Solved

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

Posted on 2006-07-12
Medium Priority
550 Views
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

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. =)

ann

0
Question by:wala_lang
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4

LVL 25

Expert Comment

ID: 17088479
0

LVL 69

Expert Comment

ID: 17091635
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

LVL 69

Expert Comment

ID: 17091655
D'OH, not working correctly, will continue to work on it :-) .
0

LVL 69

Accepted Solution

Scott Pletcher earned 940 total points
ID: 17091761
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

LVL 69

Expert Comment

ID: 17091906
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

Author Comment

ID: 17095344
thanks for all the posts guys.  i will give them all a try today.
0

Author Comment

ID: 17095750
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

Author Comment

ID: 17095793
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

Author Comment

ID: 17097186
hi scott,

i fixed it.  =) thanks heaps again.

ann
0

LVL 69

Expert Comment

ID: 17108077
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month9 days, 9 hours left to enroll