February 6, 2012 at 8:51 pm
Hi All,
I am trying to find the time taken to finish a job. (hh:mm:ss)
so basically doing starttime - endtime
but want to exclude weekends and public holidays
I have got it so far but not able to exclude weekends and PH
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2012-02-03 23:00:00 pm'
SET @EndDate = '2012-02-07 12:00:00 am'
SELECT RIGHT('0' + CONVERT(varchar(6), DATEDIFF(second, @StartDate, @EndDate)/3600),2) + ':' +
RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @StartDate, @EndDate) % 3600) / 60), 2) + ':' +
RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, @StartDate, @EndDate) % 60), 2)
Result:
73:00:00
Considering 04th Feb as Saturday, 05th Feb as Sunday and 06th Feb as a public holiday
the actual result should be
01:00:00
Any help would be appreciated
Thank you in advance
February 7, 2012 at 12:07 am
This was removed by the editor as SPAM
February 7, 2012 at 3:07 pm
No i dont have a calender table but i do have public holidays table
but again that contains dates only does not contain time
February 8, 2012 at 12:44 am
This was removed by the editor as SPAM
February 8, 2012 at 2:54 am
DECLARE
@StartDate datetime = '2012-02-03 23:00:00',
@EndDate datetime = '2012-02-07 00:00:00';
DECLARE @Holidays AS TABLE
(
HolidayDate date PRIMARY KEY
);
INSERT @Holidays
(HolidayDate)
VALUES
('2012-02-06');
-- Standard in-line numbers table generator
-- Use a permanent numbers table if you have one
WITH
N1 AS (SELECT N1.n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 (n)),
N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R),
Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM N4)
SELECT
-- Format seconds as hh:mm:ss
TimeDifference =
RIGHT(100 + (SUM(Subquery.TotalSeconds) / 3600), 2) + ':' +
RIGHT(100 + (SUM(Subquery.TotalSeconds) % 3600) / 60, 2) + ':' +
RIGHT(100 + (SUM(Subquery.TotalSeconds) % 60), 2)
FROM
(
SELECT
TotalSeconds =
CASE
-- Count zero for a weekend day
WHEN DATENAME(WEEKDAY, CurrentDay.DateOnly) IN ('Saturday', 'Sunday') THEN 0
-- Count zero for a holiday
WHEN EXISTS (SELECT 1 FROM @Holidays AS h WHERE h.HolidayDate = CurrentDay.DateOnly) THEN 0
-- For the day of the start date
WHEN Numbers.n = 1 THEN
CASE
-- If start and end are the same day, count seconds
WHEN CONVERT(date, @StartDate) = CONVERT(date, @EndDate)
THEN DATEDIFF(SECOND, @StartDate, @EndDate)
-- Else count seconds to midnight next day
ELSE DATEDIFF(SECOND, @StartDate, NextDay.DateOnly)
END
-- For the day of the end date
WHEN Numbers.n = DateRange.DayCount + 1 THEN
CASE
-- If start and end are the same day, already counted
WHEN CONVERT(date, @StartDate) = CONVERT(date, @EndDate)
THEN 0
-- Else count seconds from midnight this day
ELSE DATEDIFF(SECOND, CurrentDay.DateOnly, @EndDate)
END
END
FROM Numbers
CROSS APPLY (SELECT DayCount = DATEDIFF(DAY, CONVERT(date, @StartDate), CONVERT(date, @EndDate))) AS DateRange
CROSS APPLY (SELECT DateOnly = DATEADD(DAY, Numbers.n, CONVERT(date, @StartDate))) AS NextDay
CROSS APPLY (SELECT DateOnly = DATEADD(DAY, -1, NextDay.DateOnly)) AS CurrentDay
WHERE
Numbers.n BETWEEN 1 AND 1 + DateRange.DayCount
) AS Subquery;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 3:17 am
This was removed by the editor as SPAM
February 8, 2012 at 3:24 am
Stewart "Arturius" Campbell (2/8/2012)
Thanks, PaulThis one is going into my snippets as well....
Cool 😎 ...by the way there might be more efficient solutions, that's just the one that seemed natural to me. One could certainly do something based on a function (and the logic might be reusable) but I would pretty much always code it as an in-line table-valued function rather than as a scalar function, at least in T-SQL.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 3:25 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply