StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The case statement in Philip Kelley's answer does not work, although the main principal of filling a temp table with dates and left joining stands true. For my version I've used the same variable to start - an input date and the number of days to report on.</p> <pre><code>DECLARE @StartDate DATETIME, @Days INT SELECT @StartDate = GETDATE(), @Days = 5 -- REMOVE ANY TIME FROM THE STARTDATE SET @StartDate = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @StartDate)) -- CREATE THE TEMP TABLE OF DATES USING THE SAME METHODOLOGY DECLARE @Dates TABLE (AlarmDate SMALLDATETIME NOT NULL PRIMARY KEY) WHILE (@Days > 0) BEGIN INSERT @Dates VALUES (DATEADD(DAY, @Days, @StartDate)) SET @Days = @Days - 1 END -- NOW SELECT THE DATA SELECT AlarmDate, CompID, CONVERT(DECIMAL(10, 2), ISNULL(SUM(DownTime), 0) / 3600.0) [DownTime] FROM @Dates LEFT JOIN ( SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, StartAlarmDate)) [StartAlarmDate], CompID, DATEDIFF(SECOND, StartAlarmDate, CASE WHEN EndAlarmDate >= DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate)) THEN DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate)) ELSE EndAlarmDate END) [DownTime] FROM [yourTable] UNION ALL SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, EndAlarmDate)) [Date], CompID, DATEDIFF(SECOND, DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate)), EndAlarmDate) [DownTime] FROM [yourTable] WHERE EndAlarmDate >= DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate)) ) data ON StartAlarmDate = AlarmDate GROUP BY AlarmDate, CompID </code></pre> <p>I have used seconds for the date diff and divided by 3600.0 after the seconds have been summed up as 60 rows each with a difference of a minute would sum to 0 when using hours for a datediff.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload