Need help with SQL query

Question

I have following data:

TransactionType        Lane      TransactionDate
------------------------------------------------
           Sale          17  2011-07-21 06:15:34
         SignOn          18  2011-07-21 07:00:00
           Sale          18  2011-07-21 07:06:43
         SignOn          20  2011-07-21 08:01:12
           Sale          18  2011-07-21 07:30:00
           Sale          18  2011-07-21 08:30:00
           Sale          18  2011-07-21 09:30:50
           Sale          20  2011-07-21 10:15:34
        SignOff          18  2011-07-21 19:00:00
        SignOff          20  2011-07-21 19:10:52

I need to get the following information:

1. the count of lane that lie between SignOn and SignOff transaction types
2. the total time each lane was at SignOn state, e.g. row with lane number 18 was 12 hours, row with lane 20 has time of 11 hours, 9 minutes and 40 seconds

Answer 1

This will get most of it... including multiple sign in/out per lane and how many transactions within each login/out time period. Once you get THIS result, you can query from THIS to get the total counts of active lanes / sessions with whatever roll-up values... such as total times lane 18 was signed in/out total sales transactions, or just overall how many lanes were open and accounted for such sale activity.

Since the group by is on lane, signon/off, I just applied MIN() for the hours, minutes, seconds which would always be a constant per each signon/off session.

SELECT 
      PreQuery.Lane,
      PreQuery.SignedOn,
      PreQuery.SignedOff,
      MIN( datediff(hour, PreQuery.SignedOn, PreQuery.SignedOff )) as hours,
      MIN( datediff(minute, PreQuery.SignedOn, PreQuery.SignedOff )) as minutes,
      MIN( datediff(second, PreQuery.SignedOn, PreQuery.SignedOff )) as seconds,
      COUNT(*) as NumOfSales 
   from 
      ( SELECT 
              T1.Lane,
              T1.TransactionDate SignedOn,
              MIN( T2.TransactionDate ) as SignedOff
           from 
              Trans1 T1
                 LEFT JOIN Trans1 T2
                    ON T1.Lane = T2.Lane
                    AND T2.TransactionType = "SignOff"
                    AND T2.TransactionDate > T1.TransactionDate 
           where 
              T1.TransactionType = "SignOn" 
           group by 
              T1.Lane,
              T1.TransactionDate ) PreQuery 
      LEFT Join Trans1 T3 
         on PreQuery.Lane = T3.Lane 
        AND T3.TransactionType = "Sale" 
        AND T3.TransactionDate BETWEEN PreQuery.SignedOn AND PreQuery.SignedOff
   GROUP BY 
      PreQuery.Lane,
      PreQuery.SignedOn,
      PreQuery.SignedOff

Answer 2

The following does not correct for multiple signon - signoff events per lane, but at least its a start. If I have info on how many signon-signoff events are allowed per day and/or other constraints I'll try to tweak it so it works in the general case.

1.the count of lane that lie between SignOn and SignOff transaction types

SELECT      sales.lane, signon.transactiondate, count(*) as salecount
FROM        table1 sales 
INNER JOIN  table1 signon 
        ON   (signon.lane = sales.lane AND signon.transactiontype = 'SignOn')
INNER JOIN  table1 signoff  
        ON   (signoff.lane = sales.lane AND signoff.transactiontype = 'SignOff')
WHERE       sales.transactiontype = 'Sale' 
            AND sales.transactiondate 
              BETWEEN signon.transactiondate AND signoff.transactiondate
GROUP BY    sales.lane, signon.transactiondate

2.the total time each lane was at SignOn state, e.g. row with lane number 18 was 12 hours, row with lane 20 has time of 11 hours, 9 minutes and 40 seconds

SELECT      signon.lane,
            datediff(hour, signon.transactiondate, signoff.transactiondate) as hours,
            datediff(minute, signon.transactiondate, signoff.transactiondate) as minutes,
            datediff(second, signon.transactiondate, signoff.transactiondate) as seconds,

FROM        table1 signon
INNER JOIN  table1 signoff 
        ON    (signoff.lane = signon.lane 
              AND signoff.transactiondate > signon.transactiondate
              AND signoff.transactiontype = 'SignOff')
WHERE       signon.transactiontype = 'SignOn'                               
GROUP BY    signon.lane, signon.transactiondate