Order by date range? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order by date range?

Author

Topic

dlouche
Starting Member

9 Posts

Posted - 2008-10-28 : 07:54:46

I want to get all the records from a table (no grouping) and order them first by a date range and then within that range order them by another column. For example, I have a table called Events:

ID | Date | Latitude
--------------------------------------------------------
1 | 12-01-2008 | 6
2 | 12-04-2008 | 4
3 | 12-05-2008 | 12
4 | 12-02-2008 | 4

So in this example I run the query on 12-5-2008 and would like the date range to be every two days, and the second 'order by' item to be the latitude. So the output would be:

ID | Date | Latitude
--------------------------------------------------------
2 | 12-04-2008 | 4
3 | 12-05-2008 | 12
4 | 12-02-2008 | 4
1 | 12-01-2008 | 6

ID 2 and 3 are within the last 2 days, so 2 comes before 3 (since I'm also ordering on Lat). The next date range is day 3 and 4, so ID 4 comes since it is the only item in that range, etc...

Thanks a ton for any help.

Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 08:08:38

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
ID INT,
Date SMALLDATETIME,
Latitude INT
)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 1, '12-01-2008', 6 UNION ALL
SELECT 2, '12-04-2008', 4 UNION ALL
SELECT 3, '12-05-2008', 12 UNION ALL
SELECT 4, '12-02-2008', 4

-- Initialize display behaviour
DECLARE @range INT,
@today SMALLDATETIME

SELECT @range = 2,
@today = '2008-12-05'

-- Do the magic
SELECT *
FROM @Sample
ORDER BY DATEDIFF(DAY, Date, @today) / @range,
Date,
Latitude[/code]

E 12°55'05.63"
N 56°04'39.26"

dlouche
Starting Member

9 Posts

Posted - 2008-10-28 : 08:29:01

You made that look easy!!

Thanks again

Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources