StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As mentionned by @paulbailey, you want to use the DATEDIFF function to get the amount of downtime.</p> <p>To extract the dates and downtime period (I'm adding a bit more columns that you might need)..</p> <pre><code>SELECT compid, YEAR(startalarmdate) AS [Year], MONTH(startalarmdate) AS [Month], DAY(startalarmdate) AS [Day], DATEDIFF(ss, startalarmdate, endalarmdate) AS DowntimeInSeconds --You will need to convert thid later to the format you wish to use FROM YourTable /* WHERE CLAUSE - Most probably a date range */ </code></pre> <p>Now this gives you the downtime in seconds for each days that had a downtime.</p> <p>To get the amount of downtime per day is easy as grouping by day and SUMing up the downtimes (again adding more columns that you might need)..</p> <pre><code>SELECT compid, [Year], [Month], [Day], SUM(DowntimeInSeconds) AS TotalDowntimeInSeconds FROM (SELECT compid, YEAR(startalarmdate) AS [Year], MONTH(startalarmdate) AS [Month], DAY(startalarmdate) AS [Day], DATEDIFF(ss, startalarmdate, endalarmdate) AS DowntimeInSeconds --You will need to convert thid later to the format you wish to use FROM YourTable /* WHERE CLAUSE - Most probably a date range */) AS GetDowntimes GROUP BY compid, [Year], [Month], [Day] ORDER BY [Year], [Month], [Day], compid </code></pre> <p>And I believe this should help you get where you want to.</p> <p>Edit: To have the days that have no downtime included in this result, you need to first have a list of ALL days present in a month. You take this list and you LEFT OUTER JOIN the result from the above query (you will have to remove the ORDER BY first).</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