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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy