| Author |
 Topic  |
|
|
Dan1980
Starting Member
United Kingdom
9 Posts |
 Posted - 10/15/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 10/15/2013 : 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
United Kingdom
9 Posts |
Posted - 10/15/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 10/15/2013 : 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
India
16 Posts |
Posted - 10/15/2013 : 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
United Kingdom
9 Posts |
Posted - 10/15/2013 : 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
India
52326 Posts |
Posted - 10/16/2013 : 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
|
|
|
| |
Topic |
|
Help sorting by date