如何编写SQL查询来计算聚合服务器的正常运行时间？

I have a requirement in SQL where I have data of a server start stop time on daily basis for one month. I need the result where it should calculate the first start time of the day, last stop time of the day, total time server was started in a day on daily basis and for servers.

我在SQL中有一个要求,我每天都有一个服务器启动停止时间的数据,为期一个月。我需要的结果是它应该计算当天的第一个开始时间,当天的最后一个停止时间,每天开始服务器的总时间以及服务器。

Table is like below and expected output is also given.

表格如下所示,并给出了预期的输出。

Table:

Date & Time           Reader ServerID
3/14/2016 6:36:20 AM    ON  123
3/14/2016 6:58:45 AM    OFF 123
3/14/2016 8:06:19 AM    ON  123
3/14/2016 9:32:48 AM    OFF 123
3/15/16 6:00:00 AM      ON  123
3/15/16 6:01:00 AM      OFF 123
3/14/2016 9:46 AM       ON  124
3/14/2016 10:01 AM      OFF 124
3/14/16 11:01 AM        ON  124
3/14/16 12:01 PM        OFF 124

Expected output

UserID   FirstIN   Last Out  TotalInTime (min)    Date
123       6:00      09:32      86         3/14
123       06:00     06:01      1         3/15
124       9:46      12:01      75         3/14

1 个解决方案

#1

So, for each day & server, you want the minimum and maximum time and the sum of minutes "ON".

因此,对于每天和服务器,您希望最小和最大时间以及分钟总和为“ON”。

First you need rows of ON/OFF pairs (pairs on a row, not pairs of rows) whose minutes you can calculate. Then you sum the minutes and take the minimum and maximum times.

首先,您需要可以计算其分钟数的ON / OFF对(行上的对,而不是行对)。然后你总结分钟并采取最小和最大时间。

SQL Server has datepart. You can use that to compute days. To make On/Off pairs, join the table to itself along these lines:

SQL Server有datepart。您可以使用它来计算天数。要制作On / Off对,请按以下方式将表连接到自身:

select   A.ServerID, datepart(day, A.time) as day, 
         A.time as ON, min(B.time) as OFF
from     T as A join T as B on datepart(day, A.time) = datepart(day, B.time)
and      A.Reader = 'ON' and B.Reader = 'OFF'
and      A.time < B.time
group by A.ServerID, datepart(day, A.time), A.time

You can make a view like that, or a CTE, or insert the results in a temporary table. Let's call the result V.

您可以创建这样的视图或CTE,或将结果插入临时表中。我们称之为结果V.

select   ServerID, day, min(ON), max(OFF)
       , sum(datediff(minute, OFF, ON)) as minutes
from     V
group by ServerID, day

(You can also nest the first query inside the second one.)

(您也可以将第一个查询嵌套在第二个查询中。)

The trick is knowing how to find the "next" time for any pair (a question I've answered often), and how to use the server's date functions.

诀窍是知道如何找到任何一对的“下一个”时间(我经常回答的问题),以及如何使用服务器的日期函数。

#1