Help sorting by date - 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

General SQL Server Forums

New to SQL Server Programming

Help sorting by date

Author

Topic

Dan1980
Starting Member

9 Posts

Posted - 2013-10-15 : 06:55:23

Hello all

I'm really looking for some help. I have some big lists of data in a table with dates next to it. I need to sort the data in an order that is todays date -7 days, then todays date -6 and so on.

Would I do this using the datedif function?

I'm brand new to SQL so take it easy on me!!

Thanks in advance

Dan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-10-15 : 07:09:26

can you please give sample data and wanted result in relation to sample data?

Too old to Rock'n'Roll too young to die.

Dan1980
Starting Member

9 Posts

Posted - 2013-10-15 : 07:32:39

OK so my data might look like this

Customer Name Date
Customer 1 30/09/2013
Customer 2 11/10/2013
Customer 3 11/10/2013
Customer 4 23/09/2013
Customer 5 05/10/2013
Customer 6 01/10/2013
Customer 7 11/10/2013
Customer 8 08/10/2013
Customer 9 07/10/2013
Customer 10 26/09/2013

What I'd like to do is order the customers where the date field is 7 days less than todays date first. Then 6 days less than todays date and so on. Until eventually todays date and then days in the future so today +1 etc..

I currently use this (CAMPAIGN = 'CustomerCampaign) order by date DESC but that just gives me ther furthest date in the future.

The file can contain dates as far ago as 60 days in the past and future but I want to work from today -7 inwards.

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-10-15 : 07:40:42

So you want to order the result (order by) but you want to filter (where ...) too?
To make that more clear to all of us I did beg you to show us the wanted result...

Too old to Rock'n'Roll too young to die.

divya.ce
Starting Member

16 Posts

Posted - 2013-10-15 : 08:03:37

declare @custtable table (CustomerName varchar(100), Dt varchar(100),actualdate datetime)
insert into @custtable(CustomerName,Dt)
values(
'Customer 1', '30/09/2013'),
('Customer 2', '11/10/2013'),
('Customer 3', '11/10/2013'),
('Customer 4', '23/09/2013'),
('Customer 5', '05/10/2013'),
('Customer 6', '01/10/2013'),
('Customer 7', '11/10/2013'),
('Customer 8', '08/10/2013'),
('Customer 9', '07/10/2013'),
('Customer 10', '26/09/2013'),
('Customer 11', '15/10/2013'),
('Customer 19', '25/10/2013'),
('Customer 20', '30/10/2013')

-------convert varchar value to actual date (not required to be done at your end)
update @custtable set actualdate = convert(datetime,dt,103)

select * ,datediff(d,getdate(),actualdate)
from @custtable
order by case when datediff(d,getdate(),actualdate) >=-7 then actualdate else 99999 end

Dan1980
Starting Member

9 Posts

Posted - 2013-10-15 : 08:12:08

OK so is it was today this is the order I want the file in
Customer 1 08/10/2013
Customer 2 08/10/2013
Customer 3 08/10/2013
Customer 4 09/10/2013
Customer 5 09/10/2013
Customer 6 10/10/2013
Customer 7 11/10/2013
Customer 8 12/10/2013
Customer 9 12/10/2013
Customer 10 13/10/2013
Customer 11 14/10/2013
Customer 12 15/10/2013
Customer 13 16/10/2013
Customer 14 17/10/2013
Customer 15 18/10/2013
Customer 16 19/10/2013
Customer 17 20/10/2013
Customer 18 21/10/2013
Customer 19 22/10/2013
Customer 20 23/10/2013

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 03:11:41

sounds like what you need is a filter to take only data from 7 days before and then use acsending sort on date

ie


WHERE [date] >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-7)
...
ORDER BY [date]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources