September 24, 2016 at 7:07 am
I need to modify my query to start and stop calculating time based on business hours. Business hours are 7 am til 6pm Monday – Friday.
currently I only get the total time with this.
select (extract(day from (CLOSED - OPENDATE)*1)*1440 + extract(Hour from (CLOSED - OPENDATE))*60 + extract(Minute from (CLOSED - OPENDATE)) )
,STATUS, ID, RECEIVEDDATE, DETERMINATIONDATE, TYPE
FROM MYTABLE
For example if a case was open on 8/26 at 4pm and closed on 8/29 at 10 am it would be 5 hours to close.
September 25, 2016 at 10:37 pm
SueBrown (9/24/2016)
For example if a case was open on 8/26 at 4pm and closed on 8/29 at 10 am it would be 5 hours to close.
Can you explain how do you get "5 hours"?
_____________
Code for TallyGenerator
September 26, 2016 at 3:28 am
Not sure how you calculated the 5 hours, but you dont have to do all the extracts from.
Just subtract the 2 dates if they are TIMESTAMP.
If they are not TIMESTAMP, then you can do TO_TIMESTAMP to make it easy to subtract the 2 dates.
select (CLOSED - OPENDATE)*24
,STATUS, ID, RECEIVEDDATE, DETERMINATIONDATE, TYPE
FROM MYTABLE
September 26, 2016 at 3:35 am
This is a T-SQL forum so you'd probably get better answers in an Oracle forum. I've had a bash in Postgres since I believe the date functions are similar to Oracle (and I need the practice).
Essentially the core is to create a calendar table that either includes only open days or dates with an open flag or something. This also gives you the flexibility to exclude public holidays etc. You cross join that with open hours for those days (open hours so 17:00 would mean 17:00 - 18:00).
You can then JOIN or APPLY (LEFT JOIN LATERAL = OUTER APPLY in PL/PGSQL) over that resultset and count the number of rows returned as the open hours.
The key is the calendar table and working hours, create it however you need, then the rest is just joining to it.
WITH open_days AS (
SELECT *
FROM (VALUES
('2016-08-26 00:00:00'::TIMESTAMP),
('2016-08-29 00:00:00'::TIMESTAMP)
) AS t(open)
), open_hours AS (
SELECT open + hour_of_day * '1 hour'::INTERVAL AS open_hours
FROM open_days
CROSS JOIN generate_series(7,17) AS hour_of_day
), my_table AS (
SELECT '2016-08-26 16:00:00'::TIMESTAMP AS OPENDATE,
'2016-08-29 10:00:00'::TIMESTAMP AS CLOSED
)
SELECT
COUNT(*)
--*
FROM my_table
INNER JOIN open_hours
ON open_hours >= OPENDATE AND open_hours < CLOSED
-- LEFT JOIN LATERAL
--(SELECT COUNT(*) AS hours FROM open_hours
--WHERE open_hours >= OPENDATE AND open_hours < CLOSED) AS x ON true
September 26, 2016 at 3:52 am
T-SQL answer since this is a T-SQL forum.
--Calendar table
CREATE TABLE #open_days ([open] DATETIME);
INSERT INTO #open_days VALUES ('2016-08-26 00:00:00'), ('2016-08-29 00:00:00');
--Tally table
CREATE TABLE #numbers (n INT);
INSERT INTO #numbers SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects;
WITH my_table AS (
SELECT
CONVERT(DATETIME, '2016-08-26 16:00:00') AS OPENDATE,
CONVERT(DATETIME, '2016-08-29 10:00:00') AS CLOSED
), open_hours AS (
SELECT
DATEADD(HOUR, n, [open]) AS open_hours
FROM
#open_days CROSS JOIN #numbers
WHERE
n >= 7 AND n < 18
)
SELECT
*
FROM
my_table CROSS APPLY (
SELECT COUNT(*) AS open_hours
FROM open_hours
WHERE open_hours >= OPENDATE AND open_hours < CLOSED
) AS x
September 26, 2016 at 9:15 am
Here's a modified version of the function I posted in here: http://www.sqlservercentral.com/scripts/Working+Hours/111097/
I removed breaks and lunch, and adapted the times.
The DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0) can be replaced by TRUNC() in PL/SQL, but I don't remember what are the replacements for the first functions. You'll have to figure those out.
The best part is that it doesn't need tables, unless you want to consider holidays.
/*
Programmer: Luis Cazares
Date: 2014-05-22
Purpose: This function will return working hours between given 2 dates.
This function also assumes that the working hours are between 7:00 AM and 6:00 PM.
This function was inspired by Goran Borojevic
*/
CREATE function [dbo].[CalcWorkingHours] (@StartDate datetime, @EndDate datetime)
RETURNS table
AS RETURN
SELECT ISNULL((((DATEDIFF(dd,@StartDate,@EndDate)-1) --Start with total number of days including weekends
- (DATEDIFF(wk,@StartDate,@EndDate)*2) --Subtact 2 days for each full weekend
- (1-SIGN(DATEDIFF(dd,6,@StartDate)%7)) --If StartDate is a Sunday, Subtract 1
- (1-SIGN(DATEDIFF(dd,5,@EndDate) %7))) * 660) --This will give us full days minus one that we'll complete with following operations
+ (SELECT CASE WHEN @StartDate <= DATEADD(MI, 420, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 660 --if Start Date is earlier than 7:00 then it counts as full day
WHEN @StartDate >= DATEADD(MI, 1080, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 0 --if Start Date is later than 18:00 then it won't count
ELSE DATEDIFF(MI, @StartDate, DATEADD(MI, 1080, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))) --Minutes between start date and 18:00
END
+ CASE WHEN @EndDate <= DATEADD(MI, 420, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 0 --if End Date is earlier than 7:00 then it won't count
WHEN @EndDate >= DATEADD(MI, 1080, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 660 --if End Date is later than 18:00 then it counts as full day
ELSE DATEDIFF(MI, DATEADD(MI, 420, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate) --Minutes between 7:00 and end date
END
WHERE @StartDate <= @EndDate)
/* Uncomment to use holidays table
- ((SELECT count(*)
FROM holidaystable
WHERE [Date] BETWEEN @StartDate AND @EndDate) * 7.75)
*/
,0) / 60.0 AS WorkingHours
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply