December 7, 2016 at 3:45 am
Thanks in advance.
I have searched tons of sources but none seem to include weekend hours.
I have a table called 'Operations' and there's 2 datetime columns, 'start_date' and 'end_date' and I want to calculate total opening hours between these 2 dates.
I have a temp table (below) which lists the opening times.
CREATE TABLE #OpeningHours
(
WorkingDay TEXT,
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');
EDIT: Sorry should have given expected results. (It was 86 hours 🙂 )
If start_date was '2016-11-29 12:00:00.000' and end_date '2016-12-05 23:15:00.000' then the result would be 86 hours (if my calculations are correct)
December 7, 2016 at 3:54 am
you can use the function datediff to calculate the difference between 2 points of time.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 7, 2016 at 3:59 am
Adi Cohn-120898 (12/7/2016)
you can use the function datediff to calculate the difference between 2 points of time.Adi
Yes but it won't include just opening hours
December 7, 2016 at 4:04 am
So can it include part days?
- Damian
December 7, 2016 at 4:05 am
Sorry but I don't understand the last respond. What do you mean only opening hours? Datediff function can get 2 points of time and calculate the difference between them in any unit that you want (days, hours, minutes, seconds and more). To me it seems to be what you need. If I'm wrong, them I just don't understand what you need. Can you use your example and show the desired results?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 7, 2016 at 4:13 am
From my create table in the OP, I have a temp table which holds the opening hours Mon-Fri 8am-22pm and Sat/Sun 8am-18pm.
I only want to calculate total hours within these periods. so if I started something on Monday 5th Dec at 9am and finished it at 4pm on Tues 6th Dec, total hours would be 21 hours
December 7, 2016 at 4:33 am
As you want part days, I can't think of a quick answer
Take a look at this http://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates
- Damian
December 7, 2016 at 4:38 am
Again, I'm not sure that I understand you. Your table has no dates column and yet from your last respond it seems that you want to get the sum of open hours according to dates. You can still get it with the combination of sum and datediff. Here is what I think that you need, but there is a very good chance that I still don't relay understand your question.
CREATE TABLE #OpeningHours
(
WorkingDay TEXT,
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');
select sum(datediff(hh,from_time, to_time))
from #OpeningHours
go
drop table #OpeningHours
go
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 7, 2016 at 4:52 am
Adi Cohn-120898 (12/7/2016)
Again, I'm not sure that I understand you. Your table has no dates column and yet from your last respond it seems that you want to get the sum of open hours according to dates. You can still get it with the combination of sum and datediff. Here is what I think that you need, but there is a very good chance that I still don't relay understand your question.Adi
I'm not sure you have read my original post correctly.
The temp table is just a list of opening hours for each day.
My main table is called 'Operations' that's the table that has the dates on it. start_date, end_date etc
December 7, 2016 at 4:55 am
DamianC (12/7/2016)
As you want part days, I can't think of a quick answerTake a look at this http://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates
Thanks, I already looked at this post but it only serves weekdays and also my access to MS Server is a remote access and we don't have the permissions to create functions 🙁
December 7, 2016 at 5:01 am
This would be an extremely easy task with a Calendar Table[/url].
This should get you started:
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 = '01-Nov-2016';
SET @EndDate = '17-Nov-2016';
WITH Dates (N, CalendarDate) as
(SELECT TOP(1000) N, DATEADD(DAY,N-1, CONVERT(DATE,'1/1/2016'))
FROM vTally)
SELECT SUM(DATEDIFF(HOUR, OH.from_time, OH.to_time)) AS WorkingHours
FROM Dates D
JOIN #OpeningHours OH ON DATENAME(WEEKDAY,D.CalendarDate) = OH.WorkingDay
WHERE CalendarDate BETWEEN @StartDate AND @EndDate;
DROP TABLE #OpeningHours;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 5:26 am
December 7, 2016 at 5:35 am
Fast.Eddie (12/7/2016)
Thom A (12/7/2016)
This would be an extremely easy task with a Calendar Table[/url].This should get you started:
Thanks, but with our remote access to MS Server, we can't create Views so can't create the vTally View. Is there a way to create the vTally View as a temp table?
The SQL on the link I gave you is easy enough to put into a Temporary table, yes... It'll ruin your performance though, as it has to write every single one of those rows out. I really don't suggest it.
Could you not get some of the basics made up for you on the server, like a Calendar table?
Edit: P.S. Unless I am mistaken, and my SQL and basic maths has gone out the window, the correct answer for your example is 86, not 82.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 5:44 am
Not possible, there's a whole change request which takes ages and a lot of the requests we stick in are rejected because it's not 'business critical'. The company that runs the database took 2 weeks just to add 1 derived column into a table.
If it greatly effects performance doing it as a temp table then I would prefer to avoid this option.
Is there another solution for my query?
Thanks
December 7, 2016 at 5:47 am
Fast.Eddie (12/7/2016)
Not possible, there's a whole change request which takes ages and a lot of the requests we stick in are rejected because it's not 'business critical'. The company that runs the database took 2 weeks just to add 1 derived column into a table.If it greatly effects performance doing it as a temp table then I would prefer to avoid this option.
Is there another solution for my query?
Thanks
I would strongly suggest a Calendar table is "Business Critical".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply