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.
| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2009-07-30 : 12:39:09
|
| Hi, I am trying to get the number of orders booked over the last 4 saturdays over a span of 10 minutes based on orderdate, trying to get the follow output so that I can compare the order counts of the last 4 saturdays. Also providing the sample table. Thanks a bunch for your help.Output:day(sat.) timeperiod orderscount2009-07-04 5:00-5:10 22009-07-04 5.10-5.20 0 2009-07-04 5.20-5.30 0 ......2009-07-04 8.00-8.10 2 2009-07-04 8.10-8.20 1 2009-07-11 7:00-7:10 2 2009-07-11 8:00-8:10 2 ..2009-07-11 14:00-14:10 1 ..2009-07-18 06:00-6:10 22009-07-25 08:00-8:10 3create table #orders(orderdate datetime,order_id uniqueidentifier)INSERT INTO #ordersSELECT '2009-07-25 07:00:44.000', NEWID()UNION ALLSELECT '2009-07-25 07:15:44.000', NEWID()UNION ALLSELECT '2009-07-25 08:00:44.000', NEWID()UNION ALLSELECT '2009-07-25 08:03:44.000', NEWID()UNION ALLSELECT '2009-07-25 08:09:44.000', NEWID()UNION ALLSELECT '2009-07-25 08:15:44.000', NEWID()UNION ALL SELECT '2009-07-18 06:00:44.000', NEWID()UNION ALLSELECT '2009-07-18 06:09:44.000', NEWID()UNION ALLSELECT '2009-07-18 08:00:44.000', NEWID()UNION ALLSELECT '2009-07-18 08:07:44.000', NEWID()UNION ALLSELECT '2009-07-18 08:16:44.000', NEWID()UNION ALLSELECT '2009-07-18 14:01:44.000', NEWID()UNION ALLSELECT '2009-07-18 14:16:44.000', NEWID()UNION ALLSELECT '2009-07-18 14:27:44.000', NEWID()UNION ALLSELECT '2009-07-18 16:16:44.000', NEWID()UNION ALLSELECT '2009-07-18 19:01:44.000', NEWID()UNION ALLSELECT '2009-07-11 07:00:44.000', NEWID()UNION ALLSELECT '2009-07-11 07:09:44.000', NEWID()UNION ALLSELECT '2009-07-11 08:02:44.000', NEWID()UNION ALLSELECT '2009-07-11 08:07:44.000', NEWID()UNION ALLSELECT '2009-07-11 08:16:44.000', NEWID()UNION ALLSELECT '2009-07-11 14:01:44.000', NEWID()UNION ALLSELECT '2009-07-11 14:16:44.000', NEWID()UNION ALLSELECT '2009-07-11 14:27:44.000', NEWID()UNION ALLSELECT '2009-07-11 18:16:44.000', NEWID()UNION ALLSELECT '2009-07-11 20:01:44.000', NEWID()UNION ALLSELECT '2009-07-04 05:00:44.000', NEWID()UNION ALLSELECT '2009-07-04 05:09:44.000', NEWID()UNION ALLSELECT '2009-07-04 08:02:44.000', NEWID()UNION ALLSELECT '2009-07-04 08:07:44.000', NEWID()UNION ALLSELECT '2009-07-04 08:16:44.000', NEWID()UNION ALLSELECT '2009-07-04 14:01:44.000', NEWID()UNION ALLSELECT '2009-07-04 14:16:44.000', NEWID()UNION ALLSELECT '2009-07-04 15:27:44.000', NEWID()UNION ALLSELECT '2009-07-04 18:16:44.000', NEWID()UNION ALLSELECT '2009-07-04 20:01:44.000', NEWID()SELECT * FROM #ordersORDER BY 1 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-07-30 : 12:51:09
|
Can we sum them by Hour instead?SELECT CONVERT(varchar(10),OrderDate,101) AS [DAY] , DATEPART(hh,OrderDate) AS [HOUR] , COUNT(*) AS [OrdersPerHour] FROM #OrdersGROUP BY CONVERT(varchar(10),OrderDate,101) , DATEPART(hh,OrderDate)ORDER BY 1,2 Every 10 minutes is ridiculousBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
jeremygiaco
Starting Member
14 Posts |
Posted - 2009-07-30 : 15:55:58
|
This should do the trick...you wont want to create the DimTime table as a temp table, create a real table that you can use over and over..populate how you like, but this is minimalistic for your example (only putting saturdays in the table for a few months)/* Table to hold values...this would be a real table... */Create Table #DimTime(TimeId int identity, FromDate DateTime, ToDate DateTime)/*Insert actual date type values for FromDate and ToDate for every date that is a saturday (for a few years back and forwards)Example:*/--insert initial saturday record for 2009Insert #DimTime(FromDate, ToDate) Values ('2009-07-04 00:00:00', '2009-07-04 00:9:59')--hold MaxFromDateDeclare @MaxFromDate DateTime--initialize variableSelect @MaxFromDate = Max(FromDate) From #DimTime--populate tableWhile @MaxFromDate < '2009-10-01' Begin If DatePart(hour,@MaxFromDate) = 23 and DatePart(minute,@MaxFromDate) = 50 Begin Set @MaxFromDate = DateAdd(day,6,@MaxFromDate) Insert #DimTime(FromDate, ToDate) Select DateAdd(minute,10,@MaxFromDate), DateAdd(second,59,DateAdd(minute,19,@MaxFromDate)) End Insert Into #DimTime(FromDate, ToDate) Select Max(DateAdd(minute,10,FromDate)), Max(DateAdd(minute,10,ToDate)) From #DimTime Select @MaxFromDate = Max(FromDate) From #DimTimeEnd----------------------------------now your query would be this (or something like it)SELECT t.FromDate, t.ToDate, Count(o.Order_Id) OrderCountFROM #DimTime t left join #Orders o on o.OrderDate between t.FromDate and t.ToDateGROUP BY t.FromDate, t.ToDateORDER BY 1--------------------------------Drop Table #DimTimeJeremy Giaco |
 |
|
|
jeremygiaco
Starting Member
14 Posts |
Posted - 2009-07-30 : 16:00:16
|
| Or (not the solution to YOUR question exactly...but probably useful to see what time of day you get the most orders generally)SELECT left(convert(varchar,o.OrderDate,114),4), Count(o.Order_Id) OrderCountFROM #Orders oGROUP BY left(convert(varchar,o.OrderDate,114),4)ORDER BY 1Jeremy Giaco |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-30 : 17:00:58
|
| Here are some other solutions at SqlServerCentral:http://www.sqlservercentral.com/Forums/Topic762575-338-1.aspx |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|