SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help sorting by date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dan1980
Starting Member

United Kingdom
9 Posts

Posted - 10/15/2013 :  06:55:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 10/15/2013 :  07:09:26  Show Profile  Visit webfred's Homepage  Reply with Quote
can you please give sample data and wanted result in relation to sample data?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Dan1980
Starting Member

United Kingdom
9 Posts

Posted - 10/15/2013 :  07:32:39  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 10/15/2013 :  07:40:42  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

divya.ce
Starting Member

India
16 Posts

Posted - 10/15/2013 :  08:03:37  Show Profile  Reply with Quote
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
Go to Top of Page

Dan1980
Starting Member

United Kingdom
9 Posts

Posted - 10/15/2013 :  08:12:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2013 :  03:11:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000