October 3, 2016 at 5:03 am
Hi
I have the following query to count orders made at a point in time
select count(*)
FROM Database.dbo.MainTable a
where '20010101 09:00:00' between a.StartDate and a.EndDate
and a.Department like 'Catering%'
this just gives me a count for 9 am on 01/01/2001
how can I amend the above to ouput as a following to give every hour of the day for each day please
Date Time Count
01/01/2001 00:00 0
01/01/2001 01:00 2
01/01/2001 02:00 0
01/01/2001 03:00 0
01/01/2001 04:00 5
01/01/2001 05:00 0
01/01/2001 06:00 0
01/01/2001 07:00 6
01/01/2001 08:00 0
ect
please help
October 3, 2016 at 6:03 am
This should work, however, see my below point:
CREATE TABLE #Order (OrderID INT IDENTITY (1,1),
StartDate DATETIME,
EndDate DATETIME,
Department varchar(20));
INSERT INTO #Order (StartDate, EndDate, Department)
VALUES ('01-Jan-2001 03:12:17', '01-Jan-2001 04:12:17', 'Catering'),
('01-Jan-2001 05:13:12', '01-Jan-2001 05:17:01', 'Catering'),
('01-Jan-2001 05:57:00', '01-Jan-2001 06:23:17', 'Catering');
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0,O.StartDate) ,0) AS OrderStartDate,
Count(O.OrderID) AS Orders
FROM #Order O
WHERE O.StartDate between '01-Jan-2001 00:00:00' and '01-Jan-2001 23:59:59'
AND O.Department LIKE 'Catering%'
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, O.StartDate) ,0);
DROP TABLE #Order;
Your WHERE clause has your parameter on the left. If the end date and start time are in separate hours, and I followed your logic, then the row would be counmted twice. I have assumed this to be wrong, and therefore worked on StartDate only.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 3, 2016 at 7:16 am
It probably needs to be more like this to include all the possible hours.
DECLARE @StartDate datetime = '20010101',
@EndDate datetime = '20010107';
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteHours(TheHour) AS(
SELECT TOP((DATEDIFF( DD, @StartDate, @EndDate)+1)*24)
DATEADD( HH, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1, @StartDate) n
FROM E a, E b, E c
)
SELECT t.TheHour AS OrderStartDate,
Count(O.OrderID) AS Orders
FROM cteHours t
LEFT JOIN #Order O ON t.TheHour BETWEEN O.StartDate AND O.EndDate
AND O.Department LIKE 'Catering%'
GROUP BY t.TheHour;
October 4, 2016 at 1:06 am
October 4, 2016 at 2:50 am
Hi
I have got this far using your query
SELECT dateadd(hour, datediff(hour, 0, a.StartDate ), 0) as TimeStampHour, Count(*)
FROM Database.dbo.MainTable a
Where a.StartDate >='20160101'
and a.Department like 'Catering%'
GROUP BY dateadd(hour, datediff(hour, 0, a.StartDate ), 0)
ORDER BY dateadd(hour, datediff(hour, 0, a.StartDate ), 0);
however I do need to calculate between a.StartDate and a.EndDate
if I do this
SELECT dateadd(hour, datediff(hour, a.StartDate, a.EndDate ), 0) as TimeStampHour, Count(*)
FROM Database.dbo.MainTable a
Where a.StartDate >='20160101'
and a.Department like 'Catering%'
GROUP BY dateadd(hour, datediff(hour, a.StartDate, a.EndDate ), 0)
ORDER BY dateadd(hour, datediff(hour, a.StartDate, a.EndDate ), 0);
dates start at 1900!
please help
October 4, 2016 at 7:47 am
joanna.seldon (10/4/2016)
HiI have got this far using your query
SELECT dateadd(hour, datediff(hour, 0, a.StartDate ), 0) as TimeStampHour, Count(*)
FROM Database.dbo.MainTable a
Where a.StartDate >='20160101'
and a.Department like 'Catering%'
GROUP BY dateadd(hour, datediff(hour, 0, a.StartDate ), 0)
ORDER BY dateadd(hour, datediff(hour, 0, a.StartDate ), 0);
however I do need to calculate between a.StartDate and a.EndDate
if I do this
SELECT dateadd(hour, datediff(hour, a.StartDate, a.EndDate ), 0) as TimeStampHour, Count(*)
FROM Database.dbo.MainTable a
Where a.StartDate >='20160101'
and a.Department like 'Catering%'
GROUP BY dateadd(hour, datediff(hour, a.StartDate, a.EndDate ), 0)
ORDER BY dateadd(hour, datediff(hour, a.StartDate, a.EndDate ), 0);
dates start at 1900!
please help
I've already posted a solution. If it doesn't work, please post sample data and expected results for that data. Read the links on my signature to know how to post them.
October 4, 2016 at 11:24 am
DECLARE @start_date datetime;
DECLARE @end_date datetime;
SET @start_date = '20010101';
SET @end_date = '20010101 23:00';
;WITH tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) tally(number)
),
tally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM tally10 t10
CROSS JOIN tally10 t100
CROSS JOIN tally10 t1000
)
SELECT DATEADD(HOUR, t.number, StartDateHour) AS TimeStampHour, COUNT(*) AS Patient_Count
FROM #Order o
CROSS APPLY (
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, o.StartDate), 0) AS StartDateHour,
DATEADD(HOUR, DATEDIFF(HOUR, 0, o.EndDate), 0) AS EndDateHour
) AS assign_alias_names
INNER JOIN tally1000 t ON t.number BETWEEN 0 AND DATEDIFF(HOUR, StartDateHour, EndDateHour)
WHERE StartDateHour <= @end_date AND EndDateHour >= @start_date AND DATEADD(HOUR, t.number, StartDateHour) <= @end_date
GROUP BY DATEADD(HOUR, t.number, StartDateHour)
ORDER BY TimeStampHour
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply