Need help with SQL Server Query, Beginner

Question

I have a table with the following columns.

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

when a piece of equipment goes into alarm, I insert compid and startarmarmdate (with the current time) into the table, and when the equipment fails, I fill in zero on the same line in the endalarmdate column using the current time.

so i get something like this in a 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 managed to create a query that gives me the total idle time in hours for each event, but now I would like to create a query that gives me the total number of idle hours for each month of the month. An identifier, as if it should be fully assembled to the left, and then every day of the month to the right of compid in a column on the same row. The identifier, like days without downtime, must be zero. Can this be done the way this table is configured?

Answer 1

1: , " ", . ; (24- ) .

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

- , (), :

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

( , , . > <, >= < lt; = ( , / , ).

---

/Addenda

, ( , ):

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

, , RangeEnd , . , (, , 16 2011 00: 00.000). , , , "Dec 16, 2011 23: 59.997"

Answer 2

@paulbailey, DATEDIFF, .

( , ).

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

, .

, , ( , ).

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

, , .

: , , , , . , LEFT OUTER ( ORDER BY).

Answer 3

, temp . - .

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 used the seconds to determine the date and divided them by 3600.0 after the seconds were added up, since 60 lines with a difference of one minute would be 0 when using a clock for a dated one.