Showing number of orders at interval times

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)

Showing number of orders at interval times

Author

Topic

Chewbacca
Starting Member

5 Posts

Posted - 2010-10-27 : 04:45:57

I am trying to get some data to import to excel. What I am trying to do is to analyze at what times during the day we recieve orders, and then I want to show that in a graph in excel.

I need some help with the query. Every 30 minutes I want to count the number of orders recieved within those 30 min. The record should show the time and the count like:
08:00 32
08:30 11
09:00 27
I want to be able to run this query over longer periods as well, like for the whole last year...

All help appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 05:54:55

Please give DDL, sample data and wanted result.

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

Chewbacca
Starting Member

5 Posts

Posted - 2010-10-27 : 07:48:29

Sorry, I'm new here... what is DDL ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 07:52:07

DataDefinitionLanguage

That means it would be really helpful for us if we can see the CREATE statement of that table so we know the data types of the columns etc.

Also sample data and wanted result would be great.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-27 : 08:21:43

[code]SELECT theTime,
SUM(Orders) AS theOrderCount
FROM (
SELECT CONVERT(CHAR(5), DATEADD(MINUTE, 30 * Number, 0), 8) AS theTime,
0 AS Orders
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 0 AND 23

UNION ALL

SELECT CONVERT(CHAR(5), (DATEDIFF(MINUTE, 0, theTime) / 30) * 30, 8) AS theTime,
COUNT(*) AS Orders
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, OrderDate, 0), OrderDate) AS theTime
FROM dbo.OrderTable
) AS d
WHERE OrderDate >= '20101026'
AND OrderDate < '20101027'
GROUP BY CONVERT(CHAR(5), (DATEDIFF(MINUTE, 0, theTime) / 30) * 30, 8)
) AS d
GROUP BY theTime
ORDER BY theTime[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources