Count Hourly Customer in a Day


  

 create table events (CustomerID int, ArrivedDateTime datetime, DepartDateTime datetime)
Insert into events values (1,'2016-01-01 00:32:00.000', '2016-01-01 01:15:00.000')
,(2, '2016-01-01 00:36:00.000', ' 2016-01-01 07:02:00.000')
,(3 ,'2016-01-01 00:41:00.000', ' 2016-01-01 01:49:00.000')
,(4,'2016-01-01 00:51:00.000', ' 2016-01-01 09:00:00.000')
,(5,'2016-01-01 00:58:00.000', ' 2016-01-01 09:10:00.000')
,(6,'2016-01-01 01:27:00.000', ' 2016-01-01 14:05:00.000')

 ;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

, myDateCTE as
(
select dateadd(hour,n,(select Cast(Cast(min(ArrivedDateTime) as date) as datetime)
from events)) dt
from Nums
--(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23) ) d(n)

)

--select Cast(dt as Date) [Date], datepart(hour,dt)-1 [Hour],count(*) CustomersCnt
--from  events e left join myDateCTE c
-- on c.dt between e.ArrivedDateTime and e.DepartDateTime
--Group by dt 

--select Cast(dt as Date) [Date], datepart(hour,dt)  [Hour],  CustomerID
--from  myDateCTE c  left join events e
-- on c.dt between e.ArrivedDateTime and e.DepartDateTime  

 select Cast(dt as Date) [Date], datepart(hour,dt)  [Hour]
 ,  Sum(Case when CustomerID is not null then 1 else 0 end)  CustomerCnt
from  myDateCTE c  left join events e
 on c.dt between e.ArrivedDateTime and e.DepartDateTime
Group by dt 

 Drop table events

 

https://social.msdn.microsoft.com/Forums/en-US/8a5f5de5-6fa2-420e-bc0e-68d978479ef2/repeat-rows-by-datediff-value?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s