sql Query help

[venu656]

Hi ,

ID Device id TimeZone Effective Date
1 123 CST 2017-01-07 00:00:00.000
1 123 PST 2017-06-24 00:00:00.000
1 123 MST 2017-08-02 00:00:00.000
2 345 CST 2017-07-01 00:00:00.000
2 345 PST 2017-08-01 00:00:00.000
4 678 CST 2017-08-02 00:00:00.000

The above table is Name Timetable .I looking for below result if i pass date parameter
as ‘2017-08-01 00:00:00.000’

ID Device id TimeZone Effective Date
1 123 CST 2017-01-07 00:00:00.000
2 345 PST 2017-08-01 00:00:00.000
4 678 CST 2017-08-02 00:00:00.000

[Lost User]

That is the wrong way to hold date and time information in a database. You should always use DateTime types, and store UTC values.

[Richard Deeming]

Assuming you're using Microsoft SQL Server, something like this should work:

WITH cte As
(
SELECT
ID,
[Device id],
TimeZone,
[Effective Date],
ROW_NUMBER() OVER
(
PARTITION BY
[Device id]
ORDER BY
CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
[Effective Date] DESC
) As RN
FROM
Timetable
)
SELECT
ID,
[Device id],
TimeZone,
[Effective Date]
FROM
cte
WHERE
RN = 1
;

ROW_NUMBER (Transact-SQL) | Microsoft Docs[^] NB: You should avoid using spaces or special characters in table and column names. It makes it harder to query the data correctly. Instead, change the display names in your application's UI.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[venu656]

Thank you .it has solved partially but still have one problem If we have rows which are less than or equal for the supplied parameter(Date) we are pulling highest date row here this is working correct . if we don't have any rows which are less than or equal for the supplied parameter(Date) and if we have rows which are greater than supplied parameter we need to pull lowest date.can you help in this regard

[Richard Deeming]

Try changing the ROW_NUMBER ordering:

ROW_NUMBER() OVER
(
PARTITION BY
[Device id]
ORDER BY
CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
CASE WHEN [Effective Date] <= @YourDateParameter THEN [Effective Date] ELSE Null END DESC,
CASE WHEN [Effective Date] <= @YourDateParameter THEN Null ELSE [Effective Date] END
) As RN

1. Dates on or before the date parameter come first;
2. Dates on or before the parameter are sorted in descending order, so the latest comes first;
3. Dates after the parameter are sorted in ascending order, so the earliest comes first;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer