CODEWITHSUNDEEP
sqldateexists
Prince
Prince

Reputation: 165

Select rows with nearest date

I have a SQL statement.

SELECT ID, LOCATION, CODE,MAX(DATE) FROM TABLE1 WHERE 
DATE <= CONVERT(DATETIME,'11-11-2012') AND 
EXISTS(SELECT * FROM #TEMP_CODE WHERE TABLE1.CODE =#TEMP_CODE.CODE) 
AND ID IN (14,279)
GROUP BY ID, LOCATION, CODE,(DATE)

I need rows with the nearest date to the 11-11-2012, but the table returns all the values. What am I doing wrong. Thanks 

ID     LOCATION                 CODE            DATE
-------------------------------------------------------------------
14  CAR STREET,UDUPI            234   2012-08-08 00:00:00.000
14  CAR STREET,UDUPI            234   2012-08-10 00:00:00.000
14  CAR STREET,UDUPI            234   2012-08-14 00:00:00.000
279 MADHUGIRI                   234   2012-08-08 00:00:00.000
279 MADHUGIRI                   234   2012-08-11 00:00:00.000

I need to select the row with the max date. The required result is 

ID     LOCATION                 CODE            DATE
-------------------------------------------------------------------
14  CAR STREET,UDUPI            234   2012-08-10 00:00:00.000
279 MADHUGIRI                   234   2012-08-11 00:00:00.000

Upvotes: 0

Views: 318

Answers (3)

Himanshu
Himanshu

Reputation: 32602

No need to use Group by (Date). Try this:

SELECT ID, LOCATION, CODE,MAX(DATE) FROM TABLE1 
WHERE DATE <= CONVERT(DATETIME,'11-11-2012') AND 
EXISTS(SELECT * FROM #TEMP_CODE WHERE TABLE1.CODE =#TEMP_CODE.CODE) 
AND ID IN (14,279)
GROUP BY ID, LOCATION, CODE

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try using unambigious date format

SELECT ID, LOCATION, CODE,MAX(DATE) FROM TABLE1 
WHERE DATE <= '20121111' AND   
EXISTS(SELECT * FROM #TEMP_CODE WHERETABLE1.CODE =#TEMP_CODE.CODE)   
AND ID IN ('KBL01005','KBL05020')  
GROUP BY ID, LOCATION, CODE

Also see why you need to use unambigious date formats http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

Remove (DATE) from the GROUP BY Clause.

Change

SELECT ID, LOCATION, CODE,MAX(DATE) FROM TABLE1 WHERE  
DATE <= CONVERT(DATETIME,'11-11-2012') AND  
EXISTS(SELECT * FROM #TEMP_CODE WHERETABLE1.CODE =#TEMP_CODE.CODE)  
AND ID IN ('KBL01005','KBL05020') 
GROUP BY ID, LOCATION, CODE,(DATE)

to

SELECT ID, LOCATION, CODE,MAX(DATE) FROM TABLE1 WHERE  
DATE <= CONVERT(DATETIME,'11-11-2012') AND  
EXISTS(SELECT * FROM #TEMP_CODE WHERETABLE1.CODE =#TEMP_CODE.CODE)  
AND ID IN ('KBL01005','KBL05020') 
GROUP BY ID, LOCATION, CODE

Upvotes: 3

Related Questions