Ordering of dates - 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

Ordering of dates

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-20 : 10:12:37

I selected 01/01/10 to display the six weeks prior to 01/01/10 when I did that I got this:

12/25/09
12/18/09
12/11/09
12/04/09
11/27/09
01/01/10

I changed the sorting to asc but I got this:
01/01/10
11/27/09
12/04/09
12/11/09
12/18/09
12/25/09

All I want is for 01/01/10 to appear on the top of the list if the user selects that week to display six weeks. How would I add that to this stored procedure?

ALTER procedure [dbo].[GetRegionWeek] --'12/18/2009', 'a'

@StartDate datetime,
@Region varchar (1)


As
SET NOCOUNT ON
Select
Case
when sort = 'x' then 'Area ' + right(reg,1)
else reg 
end AS reg1
, 
 Sort, Reg, Region, n.regionname, Convert(varchar, WKDate,1) as WKDate, co, cr, 
[Perc Received] = Case
when co = 0 then '0'
when cr = 0 then '0'
else convert (varchar,(cr * 100/co * 100)/100) end + '%',

Ans,  
[Perc Ans] = Case
when cr = 0 then '0'
when Ans = 0 then '0'
else convert(varchar,(ans  * 100/cr * 100)/100) end + '%', 

OverFlow,
[Perc Overflow] = Case
when cr = 0 then '0'
when overflow = 0 then '0'
else convert(varchar,(overflow * 100/cr * 100)/100) end + '%',

Aband,
[Perc Aband] = Case
when cr = 0 then '0'
when Aband = 0 then '0'
else convert(varchar,(Aband * 100/cr * 100)/100) end + '%',  

Busy,
[Perc Busy] = Case
when co = 0 then '0'
when Busy = 0 then '0'
else convert(varchar,(Busy * 100/co * 100)/100) end + '%'  

from  [dbo].[TSRPTotals] as t
inner join (select distinct region_ltr, regionname from natdocfile) n on t.region = n.region_ltr
where(sort = @Region) and (WKDate > DATEADD(wk, -6, @StartDate) AND WKDate <=@StartDate)
order by sort, reg, regionname, Region, WKDate desc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-20 : 10:21:19

If your WKDate isn't data type DATETIME in your table then your order by should look like this:
order by sort, reg, regionname, Region, convert(datetime,WKDate) desc

No, you're never too old to Yak'n'Roll if you're too young to die.