September 29, 2010 at 10:18 pm
I have a table with columns EventID and DateofEvent
Following is the Table Creation and Insertion Script :
create table TestTable (EventID int,EventDate datetime)
insert into TestTable values(1,GETDATE()-3)
insert into TestTable values(2,GETDATE()-1)
insert into TestTable values(3,GETDATE())
insert into TestTable values(4,GETDATE()+4)
insert into TestTable values(5,GETDATE()+5)
I want a SELECT Query and A OREDER BY CLAUSE in that query which will arrange the records like suppose today is 30-Sep. Then records which are >= Todays date must be arranged in the order 1st followed by records which are less than today's date as shown in following sample output:
39/30/2010
410/4/2010
510/5/2010
19/27/2010
29/29/2010
September 29, 2010 at 10:26 pm
You didn't specify how the time portion of the EventDate field should be dealt with so I assumed that you only wanted to look at the date not the time.
Give this a try and let me know if it works for you:
SELECT
*
FROM TestTable
ORDER BY
CASE
WHEN DATEADD(Day, 0, DATEDIFF(Day, 0, EventDate)) >= DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE())) THEN 0
ELSE 1 END,
EventDate;
September 30, 2010 at 5:22 am
UMG Developer (9/29/2010)
You didn't specify how the time portion of the EventDate field should be dealt with so I assumed that you only wanted to look at the date not the time.Give this a try and let me know if it works for you:
SELECT
*
FROM TestTable
ORDER BY
CASE
WHEN DATEADD(Day, 0, DATEDIFF(Day, 0, EventDate)) >= DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE())) THEN 0
ELSE 1 END,
EventDate;
interesting logic:-)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 30, 2010 at 7:14 am
Or...
ORDER BY SIGN(DATEDIFF(day,GETDATE(),EventDate)) & 0x80000000 DESC,EventDate ASC
Far away is close at hand in the images of elsewhere.
Anon.
September 30, 2010 at 8:33 am
David, That is an interesting way to do it, but I think it is less readable for us bit-challenged people. 🙂
September 30, 2010 at 1:18 pm
Technically you don't have to manipulate the date in the table. For a million+ row table, you save a million+ calcs 🙂 :
ORDER BY
CASE
WHEN EventDate >= DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE()))
THEN CAST(0 AS tinyint) ELSE CAST(1 AS tinyint) END,
EventDate;
The CAST to tinyint insures that the sort column is only 1 byte; otherwise, SQL may use 2 (smallint) or 4 (int) instead.
Scott Pletcher, SQL Server MVP 2008-2010
September 30, 2010 at 7:07 pm
scott.pletcher (9/30/2010)
Technically you don't have to manipulate the date in the table. For a million+ row table, you save a million+ calcs 🙂 :
Duh. I was so busy thinking about the time portion of GETDATE() the I just added the code to strip it off of EventDate too...
October 1, 2010 at 2:10 am
UMG Developer (9/30/2010)
scott.pletcher (9/30/2010)
Technically you don't have to manipulate the date in the table. For a million+ row table, you save a million+ calcs 🙂 :Duh. I was so busy thinking about the time portion of GETDATE() the I just added the code to strip it off of EventDate too...
Me too :blush:
:ermm: Wonder if two selects a union and order by would be any quicker than a million+ tests (CASE)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply