Ordering by Date for last n days

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)

Ordering by Date for last n days

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2009-03-18 : 08:44:19

When I Fire a query like this:

SELECT DISTINCT TOP 10 CONVERT(VARCHAR(12), SomeDate, 106) as SomeDate 
FROM Stats 
ORDER BY SomeDate desc

on a data as given below

2009-02-28 08:42:07.420
2009-02-28 08:42:07.107
2009-02-28 08:42:04.653
2009-02-28 08:41:55.437
2009-02-28 08:41:54.357
2009-02-28 08:41:49.547
2009-02-27 08:41:47.200
2009-02-27 08:41:45.750
2009-02-26 08:41:44.137
2009-02-26 08:41:37.090

I Get results like this:

31 Jan 2009
30 Jan 2009
29 Jan 2009
28 Jan 2009
28 Feb 2009
27 Jan 2009
27 Feb 2009
26 Jan 2009
26 Feb 2009
25 Feb 2009

I want the output to be like this:


28 Feb 2009
27 Feb 2009
26 Feb 2009
25 Feb 2009
24 Feb 2009
23 Feb 2009
22 Feb 2009
21 Feb 2009
20 Feb 2009
19 Feb 2009

How to change my code?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 08:50:55

how ur getting the date jan in output given by u when no date is there in jan month in given data

2009-02-28 08:42:07.420
2009-02-28 08:42:07.107
2009-02-28 08:42:04.653
2009-02-28 08:41:55.437
2009-02-28 08:41:54.357
2009-02-28 08:41:49.547
2009-02-27 08:41:47.200
2009-02-27 08:41:45.750
2009-02-26 08:41:44.137
2009-02-26 08:41:37.090
get like this

31 Jan 2009
30 Jan 2009
29 Jan 2009
28 Jan 2009
28 Feb 2009
27 Jan 2009
27 Feb 2009
26 Jan 2009
26 Feb 2009
25 Feb 2009

lols
Posting Yak Master

174 Posts

Posted - 2009-03-18 : 08:51:53

That was just a sample..:)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-18 : 08:52:50

Hi please try this once

declare @startmonth datetime,@endmonth datetime
select @endMonth = getdate() ,@startmonth = getdate()-10

SELECT convert(varchar(32),DATEADD(DAY, number, @startmonth),106) 'Date'
FROM Master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @startmonth) <= @endMonth
order by date desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 08:53:59

Don't use same name for ALIAS as source column.

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

lols
Posting Yak Master

174 Posts

Posted - 2009-03-18 : 08:57:30

Thanks Peso but when I change that, I still do not get the desired output..My aim is to get the last 10 days i.e from Mar 18 to Mar 09 and I use this query
SELECT DISTINCT TOP 10 CONVERT(VARCHAR(12), SomeDate, 106) as SomeDate
FROM Stats
ORDER BY SomeDate desc

lols
Posting Yak Master

174 Posts

Posted - 2009-03-18 : 08:58:21

Nageshwar..is there a way to get the last 10 days listed without giving the start and end params?

lols
Posting Yak Master

174 Posts

Posted - 2009-03-18 : 09:09:48

OK here's what I did - SELECT DISTINCT TOP 30 CONVERT(VARCHAR(22), SomeDate, 107) as SomeOtherDate
FROM Stats
ORDER BY CONVERT(VARCHAR(22), SomeDate, 107) desc

I get the results

Mar 18, 2009
Mar 17, 2009
Mar 16, 2009
Mar 15, 2009
Mar 14, 2009
Mar 13, 2009
Mar 12, 2009
Mar 11, 2009
Mar 10, 2009
Mar 09, 2009
Mar 08, 2009
Mar 07, 2009
Mar 06, 2009
Mar 05, 2009
Mar 04, 2009
Mar 03, 2009
Mar 02, 2009
Mar 01, 2009
Jan 31, 2009
Jan 30, 2009
Jan 29, 2009
Jan 28, 2009
Jan 27, 2009
Jan 26, 2009
Feb 28, 2009
Feb 27, 2009
Feb 26, 2009
Feb 25, 2009
Feb 24, 2009
Feb 23, 2009

After Mar 1, I want Feb 28 and Feb 27 and so on...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 09:17:27

[code]-- Prepare sample data
DECLARE @Stats TABLE
(
SomeDate DATETIME
)

INSERT @Stats
SELECT ABS(CHECKSUM(NEWID())) % 30000 + 30000
FROM master..spt_values

-- Display the result
SELECT TOP 30 CONVERT(VARCHAR(12), SomeDate, 107) as SomeDate
FROM @Stats
GROUP BY CONVERT(VARCHAR(12), SomeDate, 107),
CONVERT(VARCHAR(8), SomeDate, 112)
ORDER BY CONVERT(VARCHAR(8), SomeDate, 112) DESC[/code]

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

lols
Posting Yak Master

174 Posts

Posted - 2009-03-18 : 09:19:34

Bingo! Thanks a ton Peso

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources