Ordering By Date and Month - 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 2008 Forums

Transact-SQL (2008)

Ordering By Date and Month

Author

Topic

ATG
Starting Member

35 Posts

Posted - 2011-10-24 : 02:43:20

First off, thanks for reading this.

Here is my code:

select CONVERT(varchar,Year(H.order_date)) +'-'+ CONVERT(varchar,Left(datename(mm,H.order_date),3)) as [Year-month],
COUNT(D.order_id) as [NumberOrders], SUM(D.quantity*D.order_price) as[TotalSales]

from bkorders.order_headers H
LEFT JOIN bkorders.order_details D on D.order_id=H.order_id

GROUP BY CONVERT(varchar,Year(H.order_date)) +'-'+ CONVERT(varchar,Left(datename(mm,H.order_date),3))

Here is my output.

Year-month NumberOrders TotalSales
2010-Dec 11 3157.36
2010-Nov 15 3176.27
2010-Oct 17 8008.66
2011-Apr 11 5006.88
2011-Aug 39 5138.84
2011-Feb 12 23254.20
2011-Jan 12 5164.25
2011-Jul 10 1173.35
2011-Jun 8 430.38
2011-Mar 25 3187.39
2011-May 12 3581.93
2011-Sep 16 4281.22

Whats happening is it's displaying the results in order of year and date in alpha order. I need it to sort by calender date rather that alpha.

For instance,

2010-Oct 17 8008.66
2010-Nov 15 3176.27
2010-Dec 11 3157.36
...

Any ideas? Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-10-24 : 02:55:01

Add this at the end

ORDER BY H.order_date

Madhivanan

Failing to plan is Planning to fail

ATG
Starting Member

35 Posts

Posted - 2011-10-24 : 02:59:40

I should have clarified a bit more. The number after the month is not the day. It's the quantity of orders. Currently the date is in VARCHAR format. I need it to order by date but display as '2011-Sep' for instance.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-10-24 : 03:31:10

ORDER BY [Year-month]

as column alias is used for order by cluase
but when you need to cast it for correct sorting you have to use

ORDER BY CAST(CONVERT(varchar,Year(H.order_date)) +'-'+ CONVERT(varchar,Left(datename(mm,H.order_date),3)) AS DATETIME)
--------------------------
http://connectsql.com/

ATG
Starting Member

35 Posts

Posted - 2011-10-24 : 03:39:50

I figured it out. I used a Common Table Expression. Here is my code.

WITH Sales (YrMth,NumOrd,TotSale)
as
(
select CAST(convert(varchar(4),H.order_date,100) + convert(varchar(4),year(H.order_date))as datetime) as [Year-month],
COUNT(D.order_id) as [NumberOrders], SUM(D.quantity*D.order_price) as[TotalSales]

from bkorders.order_headers H
LEFT JOIN bkorders.order_details D on D.order_id=H.order_id

GROUP BY convert(varchar(4),H.order_date,100) + convert(varchar(4),year(H.order_date))
)

select CONVERT(varchar,Year(Sales.YrMth))+'-'+Left(DateName(Month,Sales.YrMth),3), Sales.NumOrd, Sales.TotSale
from Sales

ORDER BY Sales.YrMth

Thanks for the assistance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-24 : 03:41:59

[code]
select CONVERT(varchar(4), Year(order_dte)) + '-' + Left(datename(mm, order_dte),3) as [Year-month],
[NumberOrders], [TotalSales]
FROM
(
SELECT dateadd(month, datediff(month, 0, H.order_date), 0) as [order_dte],
COUNT(D.order_id) as [NumberOrders],
SUM(D.quantity*D.order_price) as [TotalSales]
from bkorders.order_headers H
LEFT JOIN bkorders.order_details D on D.order_id=H.order_id
GROUP BY dateadd(month, datediff(month, 0, H.order_date), 0)
) d
ORDER BY [order_dte]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-10-24 : 04:06:31

I ll go with khtan, as according to your requriements DRIVED TABLE is better then CTE

--------------------------
http://connectsql.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 04:09:33

quote:
Originally posted by ATG

I should have clarified a bit more. The number after the month is not the day. It's the quantity of orders. Currently the date is in VARCHAR format. I need it to order by date but display as '2011-Sep' for instance.

thats the issue
why should you use varchar if you know that you're storing dates?
By not using appropriate datatypes, you're making manipulations difficults and causing lots of unwanted casts to be added in your code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-10-24 : 07:07:58

Also try using

ORDER BY dateadd(month,datediff(month,0,H.order_date),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources