需要SQL Server查询帮助，新手

I have a table that has the following columns.

我有一个包含以下列的表。

id, 
compid(used to identify a piece of equipment), 
startalarmdate, 
endalarmdate

when a piece of equipment goes into alarm, I insert the compid and startalarmdate(with the current time) into the table and when the equipment comes out of alarm I fill in the null in that same row in the endalarmdate column with the current time.

当一台设备进入报警状态时,我将compid和startalarmdate(带有当前时间)插入到表中,当设备报警时,我在endalarmdate列的同一行填写当前时间的空值。

so I end up with something like this in the table

所以我最终在表格中得到了类似的东西

417,6,Sun Oct 30 18:48:17 CDT 2011,Mon Oct 31 09:49:21 CDT 2011
422,6,Mon Oct 31 10:19:19 CDT 2011,Mon Oct 31 12:19:22 CDT 2011
427,6,Mon Oct 31 20:19:56 CDT 2011,Tue Nov 01 09:50:59 CDT 2011
429,6,Tue Nov 01 21:51:41 CDT 2011,Wed Nov 02 09:52:37 CDT 2011
432,6,Wed Nov 02 21:23:23 CDT 2011,Fri Nov 04 16:26:29 CDT 2011

I was able to build a query that gives me a total downtime in hours for each event, but what I would like to do now is build a query that gives me a total hours in downtime for each day of a month. Id like it to have the compid all the way to the left, then have each day of the month to the right of the compid in a column on the same row. Id like the days with no downtime to be null. Is it possible to do that with the way this table is setup?

我能够构建一个查询,为每个事件提供一个完整的停机时间(以小时为单位),但我现在要做的是构建一个查询,该查询为我提供了一个月中每天停机时间的总时间。我喜欢它一直到左边的compid,然后在同一行的列中的compid右边的月的每一天。我喜欢没有停机的日子是空的。是否可以按照此表的设置方式执行此操作?

3 个解决方案

#1

Step 1: set up a temp table containing the desired "time blocks" that you want to total for. These blocks could be for any range of time; in your example, it would be one entry for ever day (24-hour period) in the month.

步骤1:设置一个临时表,其中包含您想要总计的所需“时间块”。这些块可以用于任何时间范围;在您的示例中,它将是该月中一天(24小时)的一个条目。

CREATE TABLE #TimeRanges
 (
   RangeStart  datetime  not null
  ,RangeEnd    datetime  not null
 )

Left-outer-joining this table on your data ensures you get at least one row per time block (day), even if there were no alarms occuring that day:

在数据上左外连接可确保每个时间块(日)至少获得一行,即使当天没有发生警报:

SELECT
   tr.RangeStart  --  Use start of each time block to identify the block
  ,md.CompId      --  With left outer join, will be 0 or more rows for each block
  ,sum(datediff(hh
                ,case
                   when tr.RangeStart > md.StartAlarmDate then tr.RangeStart
                   else md.StartAlarmDate
                 end
                ,case
                   when tr.RangeEnd > md.EndAlarmDate then tr.RangeEnd
                   else md.EndAlarmDate
                 end))  HoursInRange
 from #TimeRanges tr
  left outer join MyData md
   on md.StartAlarmDate < tr.RangeEnd
    and md.EndAlarmDate > tr.From
 group by
   tr.RangeStart
 ,md.CompId

(I can't test this code, some debugging may be required--but the concept is solid. I'll let you worry about rounding partial hours, and whether you want > and <, or >= and <= (things may get tricky if an alarm starts and/or ends at the exact same point in time as a block boundary).

(我无法测试此代码,可能需要进行一些调试 - 但概念是可靠的。我会让您担心舍入部分小时数,以及是否需要>和<,或> =和<=(事情可能如果警报开始和/或在与块边界完全相同的时间点结束,则会变得棘手。

Edit/Addenda

Here's a fairly basic way to set up the temp table used in the routine (this code, I tested):

这是设置例程中使用的临时表的一种相当基本的方法(这段代码,我测试过):

--  Set up and initialize some variables
DECLARE
  @FirstDay      datetime
 ,@NumberOfDays  int

SET @FirstDay = 'Oct 1, 2011'  --  Without time, this makes it "midnight the morning of" that day
SET @NumberOfDays = 91  --  Through Dec 31


--  Creates a temporary table that will persist until it is dropped or the connection is closed
CREATE TABLE #TimeRanges
  (
    RangeStart  datetime  not null
   ,RangeEnd    datetime  not null
  ) 

--  The order in which you add rows to the table is irrelevant. By adding from last to first, I
--  only have to fuss around with one variable, instead of two (the counter and the end-point)

WHILE @NumberOfDays >= 0
 BEGIN
    INSERT #TimeRanges (RangeStart, RangeEnd)
     values ( dateadd(dd, @NumberOfDays, @FirstDay)       --  Start of day
             ,dateadd(dd, @NumberOfDays + 1, @FirstDay))  --  Start of the next day


    SET @NumberOfDays = @NumberOfDays - 1
 END


--  Review results
SELECT *
 from #TimeRanges
 order by RangeStart


--  Not necessary, but doesn't hurt, especially when testing code
DROP TABLE #TimeRanges

Note that by making RangeEnd the start of the next day, you have to be careful with your greaterthans and lessthans. The details can get very finicky and fussy there, and you'll want to do a lot of edge-case testing (what if alarm starts, or ends, exactly at Dec 16 2011 00:00.000). I'd go with that, because overall it's simpler to code for than for junk like 'Dec 16, 2011 23:59.997'

请注意,通过使RangeEnd成为第二天的开始,你必须小心你的greaterthans和lessthans。细节在那里会变得非常挑剔和挑剔,你会想做很多边缘情况测试(如果警报开始或结束,恰好在2011年12月16日00:00.000)。我会继续这样做,因为整体而言,编码比使用“Dec 16,2011 23:59.997”这样的垃圾更简单

#2

As mentionned by @paulbailey, you want to use the DATEDIFF function to get the amount of downtime.

正如@paulbailey所提到的,您希望使用DATEDIFF函数来获取停机时间。

To extract the dates and downtime period (I'm adding a bit more columns that you might need)..

提取日期和停机时间(我正在添加您可能需要的更多列)..

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 */

Now this gives you the downtime in seconds for each days that had a downtime.

现在,这可以为您提供停机时间的每秒停机时间。

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

通过按天分组并减少停机时间(再次添加您可能需要的更多列),可以轻松获得每天的停机时间。

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

And I believe this should help you get where you want to.

我相信这应该可以帮助你达到你想要的目标。

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

编辑:要使此结果中包含没有停机时间的日期,您需要首先列出一个月内存在的所有日期。您可以使用此列表,并从左上角加入上述查询的结果(您必须先删除ORDER BY)。

#3

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.

Philip Kelley的答案中的案例陈述不起作用,尽管填充临时表的日期和左加入的主要原则是正确的。对于我的版本,我使用相同的变量开始 - 输入日期和报告的天数。

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

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.

我已经使用秒作为日期差异除以3600.0之后,这些秒被总计为60行,每行差异为1分钟,当使用小时数时,总和为0。

#1