August 4, 2006 at 8:58 am
Ok, does anyone know how to right a order/sort so I get a recordset covering 12 months that interleaves records? Heck, I don't even know how to ask what I want to acheive. Supposing I have 16 records, one for each month between 12/03 and 3/06, how could I make the records come out like this:
2003-12
2004-01
2005-01
2004-02
2005-02
2004-03
2005-03
2004-04
2004-05
2004-06
2004-07
(...)
August 4, 2006 at 11:09 am
declare @table table (mydate datetime)
insert into @table
select '01/01/2005' union all
select '02/20/2005' union all
select '03/20/2005' union all
select '04/01/2005' union all
select '05/04/2005' union all
select '01/01/2006' union all
select '02/01/2006' union all
select '03/04/2006' union all
select '04/08/2006' union all
select '05/06/2006'
select cast(datepart(yyyy,mydate) as varchar) + '-' + cast(datepart(mm,mydate) as varchar)
from @table
order by datepart(mm,mydate),datepart(yyyy,mydate)
August 7, 2006 at 6:01 am
Somehow I can't make out the requirement from the example in original post. Could you please explain why 2003-12 is ordered as first? If it is ordered by month first, 2003-12 should come last, so it has to be something different than simple order by month, year as in John's solution.
August 7, 2006 at 9:06 am
Yes, Vladan is correct. If you use your example data in my example, you will not get the results you show in your example.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply