CODEWITHSUNDEEP
sql-serverdateexists
Prince
Prince

Reputation: 165

Selecting rows with the nearest date using SQL

I have a SQL statement.

SELECT 
    ID, LOCATION, CODE,MAX(DATE),FLAG 
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

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              FLAG
-------------------------------------------------------------------
14  CAR STREET,UDUPI         234      2012-08-08 00:00:00.000   0
14  CAR STREET,UDUPI         234      2012-08-10 00:00:00.000   1
14  CAR STREET,UDUPI         234      2012-08-14 00:00:00.000   0
279 MADHUGIRI                234      2012-08-08 00:00:00.000   1
279 MADHUGIRI                234      2012-08-11 00:00:00.000   0

I want to show only the rows with dates less than or equal to the given date. The required result is 

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

Upvotes: 1

Views: 10454

Answers (3)

Jim
Jim

Reputation: 3510

Use a subquery to get the max date for each ID, and then join that to your table:

SELECT 
    ID, LOCATION, CODE, DATE, FLAG 
FROM 
    TABLE1 
JOIN (
    SELECT ID AS SubID, MAX(DATE) AS SubDATE 
    FROM TABLE1 
    WHERE DATE < '11/11/2012'
        AND EXISTS (SELECT * FROM #TEMP_CODE WHERE TABLE1.CODE = #TEMP_CODE.CODE) 
        AND ID IN (14, 279)
    GROUP BY ID
    ) AS SUB ON ID = SubID AND DATE = SubDATE

Upvotes: 2

anon
anon

Reputation: 

;WITH x AS 
(
  SELECT ID, Location, Code, Date, Flag, 
    rn = ROW_NUMBER() OVER 
    (PARTITION BY ID, Location, Code ORDER BY [Date] DESC)
  FROM dbo.TABLE1 AS t1
  WHERE [Date] <= '20121111'
  AND ID IN (14, 279) -- sorry, missed this
  AND EXISTS (SELECT 1 FROM #TEMP_CODE WHERE CODE = t1.CODE)
)
SELECT ID, Location, Code, Date, Flag
FROM x WHERE rn = 1;

This yields:

ID  LOCATION         CODE [Date]     FLAG
--- ---------------- ---- ---------- ----
14  CAR STREET,UDUPI 234  2012-08-14 0
279 MADHUGIRI        234  2012-08-11 0

This disagrees with your required results, but I think those are wrong and I think you should check them.

Upvotes: 3

Daniel Dabes
Daniel Dabes

Reputation: 5

add a Order BY DATE LIMIT 0,2

With the order by you will make the date order by the closest to your condition in where and with the limit will return only the top 2 values!

SET ROWCOUNT 2  
SELECT 
    ID, LOCATION, CODE,MAX(DATE),FLAG 
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
ORDER BY DATE

Upvotes: -2

Related Questions