fishmong3r fishmong3r - 5 months ago 8
SQL Question

Query is not working in SQL Srvr Mgmt Studio

So, I have the below query. It works for e.g. in

FlySpeed SQL Query
app but not in
Microsoft SQL Server Management Studio
:

SELECT *
FROM table1 t
JOIN (SELECT col1, col2, CONVERT(date, col3) as dt
FROM table1
GROUP BY col1, col2, dt
HAVING COUNT(*) > 1) temp
ON t.col1 = temp.col1 AND t.col2 = temp.col2 and CONVERT(date, t.col3) = dt
ORDER BY t.col1 desc


I get this in studio:


Msg 207, Level 16, State 1, Line 5 Invalid column name 'dt'.

Answer

Use the below query,

SELECT *
FROM table1 t
JOIN (SELECT col1, col2, CONVERT(date, col3) as dt
      FROM table1
      GROUP BY col1, col2, CONVERT(date, col3)
      HAVING COUNT(*) > 1) temp
  ON t.col1 = temp.col1 AND t.col2 = temp.col2 and CONVERT(date, t.col3) = dt
ORDER BY t.col1 desc