December 7, 2016 at 6:01 am
Ok, I've used the Tally Table within the query, however, I still strongly suggest you have a Calendar table made on your SQL server. There's really no reason why your business shouldn't allow it, they are extremely efficient, and are very good at doing tasks such as the above. Instead of taking ages to try and avoid your lack of permissions, using temp tables, etc, you can use the data that's already there, and means you don't have to come back to what I've got here every time you want to do date maths:
USE DevTestDB;
GO
CREATE TABLE #OpeningHours
(
WorkingDay VARCHAR(12), --Why was this a text field!? Use VARCHAR, TEXT is deprecated
from_time TIME,
to_time TIME
)
GO
INSERT INTO #OpeningHours (WorkingDay, from_time, to_time)
VALUES ('Monday', '08:00:00.000', '22:00:00.000'),
('Tuesday', '08:00:00.000', '22:00:00.000'),
('Wednesday', '08:00:00.000', '22:00:00.000'),
('Thursday', '08:00:00.000', '22:00:00.000'),
('Friday', '08:00:00.000', '22:00:00.000'),
('Saturday', '08:00:00.000', '18:00:00.000'),
('Sunday', '08:00:00.000', '18:00:00.000');
DECLARE @StartDate DATETIME, @EndDate DATETIME;
SET @StartDate = '29-Nov-2016 12:00:00.000';
SET @EndDate = '05-Dec-2016 23:15:00.000';
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT SUM(CASE WHEN CAST(@StartDate AS DATE) = D.CalendarDate THEN
CASE WHEN CAST(@StartDate AS Time) > Oh.to_time THEN 0
WHEN CAST(@StartDate AS Time) < Oh.from_time THEN DATEDIFF(HOUR, OH.from_time, OH.to_time)
ELSE DATEDIFF(HOUR, CAST(@StartDate AS Time), OH.to_time) END
WHEN CAST(@EndDate AS DATE) = D.CalendarDate THEN
CASE WHEN CAST(@EndDate AS Time) < Oh.from_time THEN 0
WHEN CAST(@EndDate AS Time) > Oh.to_time THEN DATEDIFF(HOUR, OH.from_time, OH.to_time)
ELSE DATEDIFF(HOUR, OH.from_time, CAST(@EndDate AS Time)) END
ELSE DATEDIFF(HOUR, OH.from_time, OH.to_time)
END) AS WorkingHours
FROM (SELECT TOP(1000) N AS N, DATEADD(DAY,N-1, CONVERT(DATE,'1/1/2016')) AS CalendarDate FROM Tally) D
JOIN #OpeningHours OH ON DATENAME(WEEKDAY,D.CalendarDate) = OH.WorkingDay
WHERE D.CalendarDate BETWEEN CAST(@StartDate AS DATE) AND CAST(@EndDate AS DATE);
DROP TABLE #OpeningHours;
GO
Edit: Fixing Indents. My SSMS keeps reverting back to Retain Tabs >_<...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 6:32 am
Definitely agree with using a calendar table.
Why not point whoever's in charge at this thread..
- Damian
December 7, 2016 at 7:38 am
Thom A (12/7/2016)
Ok, I've used the Tally Table within the query, however, I still strongly suggest you have a Calendar table made on your SQL server. There's really no reason why your business shouldn't allow it, they are extremely efficient, and are very good at doing tasks such as the above. Instead of taking ages to try and avoid your lack of permissions, using temp tables, etc, you can use the data that's already there, and means you don't have to come back to what I've got here every time you want to do date maths:Edit: Fixing Indents. My SSMS keeps reverting back to Retain Tabs >_<...
Thanks very much for this. One final question.
The start and end dates are hard coded in your script, if I want to use my 'start_date' and 'end_date' columns from my main Operations table, how would I implement it?
Thanks
December 7, 2016 at 7:47 am
Fast.Eddie (12/7/2016)
Thom A (12/7/2016)
Ok, I've used the Tally Table within the query, however, I still strongly suggest you have a Calendar table made on your SQL server. There's really no reason why your business shouldn't allow it, they are extremely efficient, and are very good at doing tasks such as the above. Instead of taking ages to try and avoid your lack of permissions, using temp tables, etc, you can use the data that's already there, and means you don't have to come back to what I've got here every time you want to do date maths:Edit: Fixing Indents. My SSMS keeps reverting back to Retain Tabs >_<...
Thanks very much for this. One final question.
The start and end dates are hard coded in your script, if I want to use my 'start_date' and 'end_date' columns from my main Operations table, how would I implement it?
Thanks
StartDate and EndDate are variables, so you can populate them anyway you wish (declared, function value, table value). I have used your values from your example in my query.
I've done the hard work for you here, so I am sure you can work out how to get the right values into the variables 😎
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 8:09 am
Thanks for all your help
December 7, 2016 at 12:13 pm
Fast.Eddie (12/7/2016)
Thanks for all your help
You may well have this solved by now, but I thought I'd chime in and provide the connection between a query to figure out the total opening hours and your actual Operations table. I'm taking a liberty here and just assuming that the Operations table has a primary key field, and just going with that and the start and end date fields, and then just using a generated calendar table to provide a structure for expanding out all possible dates and times of day, by the hour, and then just counting how many fall into the range. The dates CTE is good for about 3 years worth, so if you need more, you can always add an additional reference to the TALLY cte. Take a look-see and let me know if this helps:
CREATE TABLE #OpeningHours (
WorkingDay VARCHAR(12),
from_time TIME,
to_time TIME,
DayNumber tinyint
);
INSERT INTO #OpeningHours (WorkingDay, from_time, to_time, DayNumber)
VALUES ('Monday', '08:00:00.000', '22:00:00.000', 2),
('Tuesday', '08:00:00.000', '22:00:00.000', 3),
('Wednesday', '08:00:00.000', '22:00:00.000', 4),
('Thursday', '08:00:00.000', '22:00:00.000', 5),
('Friday', '08:00:00.000', '22:00:00.000', 6),
('Saturday', '08:00:00.000', '18:00:00.000', 7),
('Sunday', '08:00:00.000', '18:00:00.000', 1);
CREATE TABLE #Operations (
PRIM_KEY int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[start_date] datetime,
[end_date] datetime
);
INSERT INTO #Operations ([start_date], [end_date])
VALUES ('2016-11-01', '2016-11-17');
--DETERMINE THE MIN AND MAX FOR ALL POSSIBLE DATES AND THE NUMBER OF DAYS
DECLARE @NUM_DAYS AS bigint;
DECLARE @START_DATE AS datetime;
SELECT @NUM_DAYS =
DATEDIFF(day, MIN([start_date]), MAX([end_date])) + 1,
@START_DATE = MIN([start_date])
FROM #Operations;
WITH TALLY AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
ALL_TIMES AS (
--THIS QUERY JUST GENERATES ALL 24 HOURS IN A DAY AS TIME VALUES
SELECT TOP (24)
CAST(
DATEADD(hour,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, 0)
AS time) AS THE_TIME
FROM TALLY AS T1, TALLY AS T2
),
ALL_DATES AS (
--THIS QUERY JUST GENERATES ALL THE DATES BETWEEN THE MIN AND MAX
SELECT TOP (@NUM_DAYS)
DATEADD(day,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1,
@START_DATE) AS THE_DATE,
DATEPART(weekday,
DATEADD(day,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1,
@START_DATE)
) AS DAY_NUMBER
FROM TALLY AS T1, TALLY AS T2, TALLY AS T3
)
SELECT O.PRIM_KEY, O.[start_date], O.[end_date], COUNT(T.THE_TIME) AS TOTAL_OPEN_HOURS
FROM #Operations AS O
INNER JOIN ALL_DATES AS D
ON D.THE_DATE BETWEEN O.[start_date] AND O.[end_date]
INNER JOIN #OpeningHours AS H
ON D.DAY_NUMBER = H.DayNumber
INNER JOIN ALL_TIMES AS T
ON T.THE_TIME >= H.from_time
AND T.THE_TIME < H.to_time
GROUP BY O.PRIM_KEY, O.[start_date], O.[end_date]
ORDER BY O.PRIM_KEY;
DROP TABLE #OpeningHours;
DROP TABLE #Operations;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply