SQL Output Y/N comparing date column from different tables

user_14372775 0
Viewed 42

I'd like to output Y or N. If cc.date1 = T.date then 'Y'. Or if cc.date1 < T.date then 'N'. I'm not quite sure how I can express this in the query below and welcome any suggestions please. The cte is part of a block of a bigger query for a stored procedure. I am using SQL Server 2008 R2 (V10.50) at the moment. Thanks

WITH cte_new AS
(
SELECT CC.ID,
CC.col1,
CC.col2,
CC.col3,
ROW_NUMBER() OVER(PARTITION BY CC.ID ORDER BY CC.date1 DESC) AS row_num
FROM table1 AS CC
INNER JOIN #temp AS T 
ON CC.ID = T.ID
WHERE CC.col1 = 'CNT'
AND CC.col2 = 'NEW'
AND CC.date1 = T.date
OR CC.date1 < T.date
)
SELECT *
INTO #NewOld
FROM cte_new
WHERE row_num = 1
sql-server-2008-r2 tsql sql
1 Answers

I'm not sure if the intention of your query is correct. However, the WHERE logic doesn't look correct. You have:

WHERE CC.col1 = 'CNT' AND
      CC.col2 = 'NEW' AND
      CC.date1 = T.date OR
      CC.date1 < T.date

I'm pretty sure you intend:

WHERE CC.col1 = 'CNT' AND
      CC.col2 = 'NEW' AND
      CC.date1 <= T.date

Your version is parsed as:

WHERE (CC.col1 = 'CNT' AND CC.col2 = 'NEW' AND CC.date1 = T.date) OR
      CC.date1 < T.date
user_11440350
Related