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)
 t-sql question, urgent!!

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 orderscount
2009-07-04 5:00-5:10 2
2009-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 2
2009-07-25 08:00-8:10 3


create table #orders
(
orderdate datetime,
order_id uniqueidentifier)

INSERT INTO #orders
SELECT '2009-07-25 07:00:44.000', NEWID()
UNION ALL
SELECT '2009-07-25 07:15:44.000', NEWID()
UNION ALL
SELECT '2009-07-25 08:00:44.000', NEWID()
UNION ALL
SELECT '2009-07-25 08:03:44.000', NEWID()
UNION ALL
SELECT '2009-07-25 08:09:44.000', NEWID()
UNION ALL
SELECT '2009-07-25 08:15:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 06:00:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 06:09:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 08:00:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 08:07:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 08:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 14:01:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 14:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 14:27:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 16:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-18 19:01:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 07:00:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 07:09:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 08:02:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 08:07:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 08:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 14:01:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 14:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 14:27:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 18:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-11 20:01:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 05:00:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 05:09:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 08:02:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 08:07:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 08:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 14:01:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 14:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 15:27:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 18:16:44.000', NEWID()
UNION ALL
SELECT '2009-07-04 20:01:44.000', NEWID()

SELECT * FROM #orders
ORDER 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 #Orders
GROUP BY CONVERT(varchar(10),OrderDate,101)
, DATEPART(hh,OrderDate)
ORDER BY 1,2




Every 10 minutes is ridiculous




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

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 2009
Insert #DimTime(FromDate, ToDate) Values ('2009-07-04 00:00:00', '2009-07-04 00:9:59')

--hold MaxFromDate
Declare @MaxFromDate DateTime

--initialize variable
Select @MaxFromDate = Max(FromDate)
From #DimTime

--populate table
While @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 #DimTime
End
--------------------------------
--now your query would be this (or something like it)
SELECT t.FromDate, t.ToDate, Count(o.Order_Id) OrderCount
FROM #DimTime t
left join #Orders o on o.OrderDate between t.FromDate and t.ToDate
GROUP BY t.FromDate, t.ToDate
ORDER BY 1
--------------------------------
Drop Table #DimTime


Jeremy Giaco
Go to Top of Page

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) OrderCount
FROM #Orders o
GROUP BY left(convert(varchar,o.OrderDate,114),4)
ORDER BY 1

Jeremy Giaco
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 16:05:06
Why not just go down to the ms while you're at it


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-30 : 17:05:25
Also see http://weblogs.sqlteam.com/peterl/archive/2009/01/30/Alternative-approach-to-calculate-most-used-time-interval.aspx


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

- Advertisement -