[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

how to compute for the downtime hours based on business days/hours

hi experts,

can anyone suggest a logic that i can use for the following specs:

downtime: 06-15-2006 08:00:00
fixed time: 07-25-2006 15:23:00
business days: monday - friday
business hours: 08:00:00 - 17:00:00

how do i get the downtime hours for the month of july taking into consideration the business days and business hours. so weekends and after office hours will not be included in the computation.

i would really really appreciate any ideas/thoughts on this one.  i am giving all my available points. =)

regards and thanks in advance.

ann

0
wala_lang
Asked:
wala_lang
  • 5
  • 4
1 Solution
 
jrb1Commented:
0
 
Scott PletcherSenior DBACommented:
First, create a table of sequential numbers from 0 to the max number of days difference between downtime and fixedtime (1100 days = ~ 3 yrs).  [Btw, that table is *remarkably* useful for many things in SQL.]  For example:

SET NOCOUNT ON
CREATE TABLE seqNums (
    seqNum SMALLINT,
    CONSTRAINT seqNums_CI  --this is important for best performace!
        UNIQUE CLUSTERED (seqNum)
    )
DECLARE @seqNum SMALLINT
SET @seqNum = 0
WHILE @seqNum <= 1100
BEGIN
    INSERT INTO seqNums VALUES(@seqNum)
    SET @seqNum = @seqNum + 1
END --WHILE
SET NOCOUNT OFF
SELECT COUNT(*) FROM seqNums


Then, this code should produce a total for any dates specified [NOTE: sample inline data is enclosed; naturally change that to be whatever table or other source of data you need :-)] :


SELECT CAST(SUM(
    CASE seqNum
    WHEN 0 THEN
        --first day
        CASE WHEN DATENAME(WEEKDAY, downtime) IN ('Saturday', 'Sunday') OR DATEPART(HOUR, downtime) >= 17
        THEN 0 ELSE DATEDIFF(MINUTE, CONVERT(CHAR(5), downtime, 8), '17:00') END
    WHEN DATEDIFF(DAY, downtime, fixedtime) THEN
        --last day
        CASE WHEN DATEDIFF(DAY, downtime, fixedtime) = 0 OR DATEPART(HOUR, fixedtime) < 8 OR
            DATENAME(WEEKDAY, fixedtime) IN ('Saturday', 'Sunday') THEN 0 ELSE
            DATEDIFF(MINUTE, '08:00', CASE WHEN DATEPART(HOUR, fixedtime) > 17 THEN '17:00'
                ELSE CONVERT(CHAR(5), fixedtime, 8) END) END
    ELSE
        --middle day(s)
        CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, seqNum, downtime)) IN ('Saturday', 'Sunday')
            THEN 0 ELSE 540 END
    END) / 60.0 AS DECIMAL(6, 1))
FROM (
    SELECT CAST('20060615 08:00:00' AS DATETIME) AS downtime,
        CAST('20060725 15:23:00' AS DATETIME) AS fixedtime
) AS dates
INNER JOIN seqNums ON seqNum <= DATEDIFF(DAY, downtime, fixedtime)
0
 
Scott PletcherSenior DBACommented:
D'OH, not working correctly, will continue to work on it :-) .
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Scott PletcherSenior DBACommented:
CORRECTION: Needed to adjust the first day if the downtime started before 08:00.

SELECT CAST(SUM(   --comment out to see individual day totals
    CASE seqNum
    WHEN 0 THEN
        --first day
        CASE WHEN DATENAME(WEEKDAY, downtime) IN ('Saturday', 'Sunday') OR DATEPART(HOUR, downtime) >= 17
        THEN 0 ELSE DATEDIFF(MINUTE, CASE WHEN DATEPART(HOUR, downtime) < 8 THEN '08:00' ELSE CONVERT(CHAR(5), downtime, 8) END, '17:00') END
    WHEN DATEDIFF(DAY, downtime, fixedtime) THEN
        --last day
        CASE WHEN DATEDIFF(DAY, downtime, fixedtime) = 0 OR DATEPART(HOUR, fixedtime) < 8 OR
            DATENAME(WEEKDAY, fixedtime) IN ('Saturday', 'Sunday') THEN 0 ELSE
            DATEDIFF(MINUTE, '08:00', CASE WHEN DATEPART(HOUR, fixedtime) > 17 THEN '17:00'
                ELSE CONVERT(CHAR(5), fixedtime, 8) END) END
    ELSE
        --middle day(s)
        CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, seqNum, downtime)) IN ('Saturday', 'Sunday')
            THEN 0 ELSE 540 END
    END
    ) / 60.0 AS DECIMAL(6, 1))   --comment out to see individual day totals
FROM (
    --SELECT CAST('20060707' AS DATETIME) AS downtime,
        --GETDATE() AS fixedtime
    SELECT CAST('20060615 08:00:00' AS DATETIME) AS downtime,
        CAST('20060725 15:23:00' AS DATETIME) AS fixedtime
) AS dates
INNER JOIN seqNums ON seqNum <= DATEDIFF(DAY, downtime, fixedtime)
--ORDER BY seqNum  --uncomment to see individual day totals


The (seemingly) odd formatting is to allow easy commenting/uncommenting so that you can easily see, and verify, the minutes computed for each day.
Ditto with the input date values -- just adjust the comments to use different test data.
0
 
Scott PletcherSenior DBACommented:
Finally (I promise, no more unprompted posts!), if you want to process multiple date ranges at the same time, while computing each separately, you can do this:


SELECT CONVERT(VARCHAR(16), downtime, 120) AS [Downtime],
    CONVERT(VARCHAR(16), fixedtime, 120) AS [Fixed Time],
    CAST(SUM(
    CASE seqNum
    WHEN 0 THEN
        --first day
        CASE WHEN DATENAME(WEEKDAY, downtime) IN ('Saturday', 'Sunday') OR DATEPART(HOUR, downtime) >= 17
        THEN 0 ELSE DATEDIFF(MINUTE, CASE WHEN DATEPART(HOUR, downtime) < 8 THEN '08:00' ELSE CONVERT(CHAR(5), downtime, 8) END, '17:00') END
    WHEN DATEDIFF(DAY, downtime, fixedtime) THEN
        --last day
        CASE WHEN DATEDIFF(DAY, downtime, fixedtime) = 0 OR DATEPART(HOUR, fixedtime) < 8 OR
            DATENAME(WEEKDAY, fixedtime) IN ('Saturday', 'Sunday') THEN 0 ELSE
            DATEDIFF(MINUTE, '08:00', CASE WHEN DATEPART(HOUR, fixedtime) > 17 THEN '17:00'
                ELSE CONVERT(CHAR(5), fixedtime, 8) END) END
    ELSE
        --middle day(s)
        CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, seqNum, downtime)) IN ('Saturday', 'Sunday')
            THEN 0 ELSE 540 END
    END
    ) / 60.0 AS DECIMAL(6, 1))
    AS [Total Down Hours]
FROM (
    SELECT CAST('20060707' AS DATETIME) AS downtime,
        GETDATE() AS fixedtime
    UNION ALL
    SELECT CAST('20060615 08:00:00' AS DATETIME) AS downtime,
        CAST('20060725 15:23:00' AS DATETIME) AS fixedtime
) AS dates
INNER JOIN seqNums ON seqNum <= DATEDIFF(DAY, downtime, fixedtime)
GROUP BY downtime, fixedtime
ORDER BY 1
0
 
wala_langAuthor Commented:
thanks for all the posts guys.  i will give them all a try today.
0
 
wala_langAuthor Commented:
hi experts,

i will award the points to scott.  it make much more sense to me.  thanks a lot for your time guys.  i really really appreciate it.

thanks scott. ur the man!

ann
0
 
wala_langAuthor Commented:
hi scott,

oopppss....i forgot.  noticed that my sample data are:

downtime: 06-15-2006 08:00:00
fixed time: 07-25-2006 15:23:00

and i only want to get the downtime for the month of july.  so using the data above, it should only give me the downtime from jul 1 up to jul 25 15:23:00.

would you mind including this piece of logic on your code.  in the meantime, i'll try to see if i can revise your code here at my end.  =)

thanks heaps.

ann
0
 
wala_langAuthor Commented:
hi scott,

i fixed it.  =) thanks heaps again.

ann
0
 
Scott PletcherSenior DBACommented:
Glad you got what you needed Hi wala_lang!

Sorry, I was not feeling well yesterday and so did not read and/or post at all.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now