Cant get the result sorted - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Cant get the result sorted

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-04-13 : 09:51:14

Hi guys,
The result of my query I am not able to get it sorted by months.Here is my query & the output.

declare @tbl as table(date datetime,amt int)
insert into @tbl
select '2-jan-2008',100 union all
select '15-jan -2008',200 union all
select '20-jan -2008',500 union all
select '12-jan-2008',300 union all
select '02-feb-2008',100 union all
select '09-feb-2008',250 union all
select '03-mar-2008',500 union all
select '05-mar-2008',800

select Months,SumAmt,MaxAmt,MinAmt from
(
select datename(mm,date)as Months,sum(amt)as SumAmt,min(amt)as MinAmt,max(amt)as MaxAmt,row_number()
over(partition by datename(mm,date) order by datename(mm,date))as rowid from @tbl
group by datename(mm,date)
)t

The result is

Months SumAmt MaxAmt MinAmt
------------------------------ ----------- ----------- -----------
February 350 250 100
January 1100 500 100
March 1300 800 500

I want to get it sorted month wise i.e Jan,Feb,March.But I cannot find a way

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-13 : 10:07:10

don't sort by datename because it will come out alphabetical. order by datepart instead

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 11:35:57

just add a column to get monthnumber and sort on it:-

select MonthNames,SumAmt,MaxAmt,MinAmt from
(
select datepart(mm,date)as Months,datename(mm,date)as MonthNames,sum(amt)as SumAmt,min(amt)as MinAmt,max(amt)as MaxAmt,row_number()
over(partition by datename(mm,date) order by datename(mm,date))as rowid from @tbl 
group by datename(mm,date)
)t
order by t.Months

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources