locked
Select paired records out of a single table RRS feed

  • Question

  • I have a poser I hope someone can help me with.  I have a table that is basically a log of events; it records a machine, the new state of the machine (on or off), and the time stamp of the event.  Something like this:

    MACH_NO STATE DATE
       2  OFF 2010-04-15
       1  ON 2010-03-27
       3  ON 2010-04-02
       2  ON 2010-04-17
       1  OFF 2010-03-30
       1  ON 2010-03-31
       3  OFF 2010-04-05
       2  OFF 2010-04-29
       3  ON 2010-04-17
       1  OFF 2010-04-06

    What I'm trying to find is the amount of time a machine is on.  For example, Machine 1 was turned on from 3/27 to 3/30, and again from 3/31 to 4/6, for a total of 9 days.  So, any given machine will have multiple ON and OFF records; I have to find a way to select the OFF record that matches the ON record (i.e. match the 3/27 with the 3/30 record, not the 4/6 record).

    I'm thinking this can somehow be done by joining the table with itself, but I can't come up with the T-SQL to do it.  Any takers?

    Thanks!

    Wednesday, May 26, 2010 10:09 PM

Answers

  • ;WITH cte 
       AS (SELECT *, 
            Row_number() 
             OVER(PARTITION BY mach_no,state ORDER BY [date]) AS row 
         FROM  mytable) 
    SELECT c1.mach_no, 
        c1.[date]           AS startdate, 
        c2.[date]           AS enddate, 
        Datediff(DAY,c1.DATE,c2.DATE) AS totaldays 
    FROM  cte c1 
        INNER JOIN cte c2 
         ON c1.mach_no = c2.mach_no 
          AND c1.state = 'ON' 
          AND c2.state = 'OFF' 
          AND c1.[date] <= c2.[date] 
          AND c1.row = c2.row

    The above is the untested idea. 


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Bass_Slapper Thursday, May 27, 2010 12:34 AM
    Wednesday, May 26, 2010 10:22 PM

All replies

  • ;WITH cte 
       AS (SELECT *, 
            Row_number() 
             OVER(PARTITION BY mach_no,state ORDER BY [date]) AS row 
         FROM  mytable) 
    SELECT c1.mach_no, 
        c1.[date]           AS startdate, 
        c2.[date]           AS enddate, 
        Datediff(DAY,c1.DATE,c2.DATE) AS totaldays 
    FROM  cte c1 
        INNER JOIN cte c2 
         ON c1.mach_no = c2.mach_no 
          AND c1.state = 'ON' 
          AND c2.state = 'OFF' 
          AND c1.[date] <= c2.[date] 
          AND c1.row = c2.row

    The above is the untested idea. 


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Bass_Slapper Thursday, May 27, 2010 12:34 AM
    Wednesday, May 26, 2010 10:22 PM
  • Naomi:
    Thanks very much - that got me where I needed!! The only change is the last line; when I ran the query as you submitted, I got no rows back.  When I commented out the last line (AND c1.row = c2.row), the query ran as expected, and returned the data I wanted.

    Thanks again!

    Thursday, May 27, 2010 12:36 AM
  • Just a minor point on Naomis query: change the CTE to only return the columns you require - SELECT * is uggly-buggly :)
    George
    blog | twitter
    Thursday, May 27, 2010 8:11 AM
    Answerer