CODEWITHSUNDEEP
sqlsql-server
REW
REW

Reputation: 776

Combining consecutive SQL rows, every other one, with a caveat

I have a table with the following pieces of ordered data:

Employee    TimeX    TimeY    Type      Date
-----------------------------------------------
   1        0800     0900      'A'    1/1/2013
   1        0930     1300      'B'    1/1/2013
   1        0600     0845      'A'    1/2/2013
   1        0925     1300      'B'    1/2/2013
   1        1100     1400      'A'    1/3/2013
   1        0500     0700      'A'    1/4/2013
   1        0715     0800      'B'    1/4/2013

What I need is to get a count of minutes between TimeY of Type A and TimeX of Type B, for each matching pair. Due to design beyond my control, I have no way to link an 'A' and a 'B' together other than just sequentially by timestamp. And sadly, no, I cannot guarantee that all rows of type 'A' will be followed by rows of type 'B', so any 'A' not followed by a 'B' should be ignored. However, no 'B' will ever be followed by another 'B'. Basically, this is what I'd like to see:

Employee     Duration
---------------------
    1           30
    1           40
    1           15

Is there a way to do this easily? The closest solution I've found here involves joining on the dates, but that's not going to work in this case. The only possible solutions I'm coming up with late in the afternoon are overly complicated and not panning out.

Edit: Thanks for the responses! That's some pretty impressive SQL wrangling! I went with Marc's answer, as it was the easiest to read, but thanks to Gordon for providing the inspiration to Marc's answer and to Nenad for the effort along the lines that I was trying.

Upvotes: 1

Views: 1331

Answers (3)

Marc Shapiro
Marc Shapiro

Reputation: 571

SELECT 
        a.Employee,
        a.TimeY,
        b.TimeX
    FROM Table1 a
        CROSS APPLY
        (
            SELECT TOP(1) t.TimeX
                FROM Table1 t
                WHERE a.[Date] = t.[Date]
                    AND a.Employee = t.Employee
                    AND a.TimeY < t.TimeX
                    AND t.[Type] = 'B'
                ORDER BY t.TimeX ASC
        ) b
    WHERE a.[Type] = 'A'
    ORDER BY a.Employee ASC
;

This doesn't actually do the subtraction, since I'm not clear on the types of TimeX and TimeY.

This is similar to the correlated subquery answer, but I think the CROSS APPLY makes it easier to read.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think the easiest way to express this is with a correlated subquery:

select t.employee, t.nextBtime - t.time
from (select t.*,
             (select top 1 (case when type = 'B' then timeY end)
              from t t2
              where t2.employee = t.employee and
                    t2.date = t.date and
                    t2.timeX > t.timeX
              order by t2.timeX
             ) nextBtime
      from t
      where type = 'A'
     ) t
where nextBtime is null;

This is making the following assumptions:

  1. Next "B" is on the same date
  2. However you are representing times, you can take the difference to get duration
  3. The records are ordered by (date, timeX).

Upvotes: 1

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

It's 2AM, this is probably one of the ugliest queries I have ever written and I am pretty sure that there are ways to simplify some parts a bit. But, important - it's working :)

;WITH CTE1 AS 
(
    --first CTE is simply to get row numbering over all dates
    SELECT *, ROW_NUMBER() OVER (ORDER BY [Date],[Type]) RN
    FROM Table1
)
, RCTE1 AS 
(
    --recursive cte is going row-by-row checking if next type is same or different
    SELECT *, 1 AS L FROM CTE1 WHERE RN =1 
    UNION ALL
    --assigning same L if next is same, L+1 if different
    SELECT c.*, CASE WHEN r.Type = c.Type THEN L ELSE L+1 END AS L
    FROM RCTE1 r
    INNER JOIN CTE1 c ON r.RN +1  = c.RN
)
, CTE2 AS 
(
    --here we search for same L values
    SELECT *, ROW_NUMBER() OVER (PARTITION BY L ORDER BY RN DESC) RN2 FROM RCTE1
)
, CTE3 AS 
(
    --and eliminate the rows not needed (ie A in front of A)
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY L) RN3
    FROM CTE2 WHERE RN2 =1 
)
-- at the end join CTE3 based on same RN3 and different type
SELECT *
-- and some datetime operations to get times from strings
, DATEDIFF(MI,DATEADD(MI,CAST(RIGHT(A.TimeY,2) AS INT) , DATEADD(HH,CAST(LEFT(A.TimeY,2) AS INT),0)), DATEADD(MI,CAST(RIGHT(B.TimeX,2) AS INT) , DATEADD(HH,CAST(LEFT(B.TimeX,2) AS INT),0))) AS Goal
FROM CTE3 a
INNER JOIN CTE3 B ON a.RN3 = b.RN3 AND a.[Type] = 'A' AND b.[Type] = 'B'
-- maxrecursion off so Recursive CTE can work
OPTION (MAXRECURSION 0)

SQLFiddle DEMO

Upvotes: 1

Related Questions