fishmong3r fishmong3r - 5 months ago 12
SQL Question

Query is not working in SQL Server Management Studio

I have this query with 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 SSMS:


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
Comments