Intersting query

[sonaliak]

Here is how the data is:


LogTrans_ID LogTrans_Start LogTrans_End LogTrans_DBConn
----------- --------------------------- --------------------------- ---------------
12317 2001-08-06 11:43:10.000 2001-08-06 11:43:21.000 3
12318 2001-08-06 11:43:12.000 2001-08-06 11:43:27.000 3
12319 2001-08-06 11:43:09.000 2001-08-06 11:43:35.000 3
12336 2001-08-06 11:45:04.000 2001-08-06 11:45:04.000 3
12337 2001-08-06 11:45:04.000 2001-08-06 11:45:05.000 3
12338 2001-08-06 11:45:03.000 2001-08-06 11:45:06.000 3
12382 2001-08-06 11:48:25.000 2001-08-06 11:48:25.000 3
12383 2001-08-06 11:48:23.000 2001-08-06 11:48:26.000 3
12384 2001-08-06 11:48:25.000 2001-08-06 11:48:26.000 3
12386 2001-08-06 11:48:31.000 2001-08-06 11:48:34.000 3


This is what I want the query to do:
LogTrans_Start LogTrans_End LogTrans_ID LogTrans_ID
--------------------------- --------------------------- ----------- -----------
2001-08-06 11:43:10.000 2001-08-06 11:45:05.000 12317 12337
2001-08-06 11:45:03.000 2001-08-06 11:48:34.000 12338 12386

I want to group LogTrans_ID in group of 5 rows. LogTrans_ID is sequential eg 1,3,5,7,9 then next group 10, 13, 15, 17, 19 in each group but not always serial
like 1,2,3,4,5 then the 2nd group 6,7,8,9,10
Each group has 5 rows( always in multiple of 5)

I want to find out the start time for LogTrans_ID = 1
and end time for LogTrans_ID =5 and again start time for LogTrans_ID = 6 and end time for LogTrans_ID =10 and so on..
This is a log reader query so
start time for LogTrans_ID = 1 is always less than start time for LogTrans_ID = 2 and so on.. similarly for the end time.

I wrote this query but

select a.LogTrans_Start, b.LogTrans_End, a.LogTrans_ID, b.LogTrans_ID
from mwebLogTransact a, mwebLogTransact b
Where a.LogTrans_DBConn = (Select max(LogTrans_DBConn) from mwebLogTransact)
and a.LogTrans_Start = (Select min(LogTrans_Start) from mwebLogTransact
where LogTrans_DBConn = (Select max(LogTrans_DBConn) from mwebLogTransact))
and b.LogTrans_End = (Select max(LogTrans_End) from mwebLogTransact
where LogTrans_DBConn = (Select max(LogTrans_DBConn) from mwebLogTransact))
and a.LogTrans_DBConn = b.LogTrans_DBConn

it does not group these rows by 5 rows so gives me only one row as

LogTrans_Start LogTrans_End LogTrans_ID LogTrans_ID
--------------------------- --------------------------- ----------- -----------
2001-08-06 11:43:10.000 2001-08-06 11:48:34.000 12317 12386

which is not what I want.

LogTrans_ID is a Primary key so is unique


Thanks for help
Sonali

[chrisrlong]

I don't have much time to think about it, but this should give you the basic idea of the solutions:

SELECT
   RN_GRP,
   MAX ( CASE   WHEN RN = 0 THEN LogTrans_Start END) AS LogTrans_Start,
   MAX ( CASE   WHEN RN = 4 THEN LogTrans_End   END) AS LogTrans_End,
   MAX ( CASE   WHEN RN = 0 THEN LogTrans_ID    END) AS LogTrans_ID_Start,
   MAX ( CASE   WHEN RN = 4 THEN LogTrans_ID    END) AS LogTrans_ID_End
FROM   
   (
      SELECT
         LogTrans_ID, 
         LogTrans_Start, 
         LogTrans_End, 
         LogTrans_DBConn,
         TRUNC(ROWNUM / 5)
            AS RN_GRP   ,
         ROWNUM MOD 5
            AS RN
      FROM
         (
            SELECT
               LogTrans_ID, 
               LogTrans_Start, 
               LogTrans_End, 
               LogTrans_DBConn,
            FROM
               mwebLogTransact
            ORDER BY
               LogTrans_Start 
    )
GROUP BY
   RN_GRP

I haven't tested this and I think I could probably do it a couple other ways, but the basic requirements are:

- Order the data
- Chunk it up into groups of 5
- Pivot the results (widen and flatten )

HTH,

- Chris