get number of orders from peak by day

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)

get number of orders from peak by day

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-20 : 01:09:42

Hi

I currently have this query...


SELECT DISTINCT TOP (100) PERCENT CONVERT(varchar, DateAdded, 111) AS DateAdded, COUNT(ID) AS OrdersPerDay
FROM         dbo.tbl_Cards
WHERE     (DateAdded BETWEEN CONVERT(DATETIME, '2010-10-10', 102) AND CONVERT(DATETIME, '2011-05-01', 102))
GROUP BY CONVERT(varchar, DateAdded, 111)
ORDER BY OrdersPerDay DESC

This returns the number of orders by day, but I would like to change it so that I would get a result that display when there where placed most orders for each day, like this...

Day 1: Peak Number of orders placed between 14.00 to 16.00 (44 orders)

Day 2: Peak Number of orders placed between 11.00 to 13.00 (24 orders)

Is this even possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-20 : 01:38:20

Yes.

See this

;WITH cteSource(theHours, theDay, theOrders)
AS (
	SELECT		DATEDIFF(HOUR, 0, DateAdded) AS theHours,,
			DATEDIFF(DAY, 0, DateAdded) AS theDay,
			COUNT(ID) AS theOrders
	FROM		dbo.tbl_Cards
	WHERE		DateAdded >= '20101010'
			AND DateAdded < '20110501'
	GROUP BY	DATEDIFF(HOUR, 0, DateAdded),
			DATEDIFF(DAY, 0, DateAdded)
), cteRanking(theHours, theOrders, SeqID)
AS (
	SELECT	theHours,
		theOrders,
		RANK() OVER (PARTITION BY theDay ORDER BY theOrders DESC) AS SeqID
	FROM	cteSource
)
SELECT	DATEADD(HOUR, theHours, 0) AS theTime,
	theOrders AS OrdersPerHour
FROM	cteRanking
WHERE	SeqID = 1

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-04-20 : 02:06:07

That is just awsome, thank you very much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-20 : 06:41:22

Try this too

;WITH cteSource(theHours, theOrders)
AS (
	SELECT		DATEDIFF(HOUR, 0, DateAdded) AS theHours,,
			COUNT(ID) AS theOrders
	FROM		dbo.tbl_Cards
	WHERE		DateAdded >= '20101010'
			AND DateAdded < '20110501'
	GROUP BY	DATEDIFF(HOUR, 0, DateAdded)
), cteRanking(theHours, theOrders, SeqID)
AS (
	SELECT	theHours,
		theOrders,
		RANK() OVER (PARTITION BY theHours / 24 ORDER BY theOrders DESC) AS SeqID
	FROM	cteSource
)
SELECT	DATEADD(HOUR, theHours, 0) AS theTime,
	theOrders AS OrdersPerHour
FROM	cteRanking
WHERE	SeqID = 1