Okay, now here's the tricky part. We have to turn a table with a date for each row into a resultset of date ranges. This is not easy in SQL. Note that a PL/SQL solution would be much easier, albeit slower. The PL/SQL solution is fairly obvious, so we'll skip that, unless you specifically request one.
I made the tables and data
CREATE TABLE RESULT_T
(ID NUMBER, MAINTABLE_ID NUMBER, TARGET_DT DATE, QTY NUMBER);
INSERT INTO RESULT_T VALUES(1, 10, '10-OCT-02', 15);
INSERT INTO RESULT_T VALUES(2, 10, '11-OCT-02', 15 );
INSERT INTO RESULT_T VALUES(20, 12, '12-OCT-02', 28);
INSERT INTO RESULT_T VALUES(21, 12, '13-OCT-02', 28);
INSERT INTO RESULT_T VALUES(22, 12, '14-OCT-02', 28);
INSERT INTO RESULT_T VALUES(6, 10, '15-OCT-02', 15);
INSERT INTO RESULT_T VALUES(6, 11, '20-OCT-02', 30);
INSERT INTO RESULT_T VALUES(7, 11, '21-OCT-02', 30);
Here is one possible SQL solution.
Let me know if this works for you,Code:-- Finally, we have all the information we need in each 'start range' record.
-- So, we restrict to only thos records.
-- Since it is possible for a record to be *both* the start *and* end of
-- a range, we must use the CASE to display the appropriate End Target_DT.
SELECT
T3.Target_DT
AS BDate ,
CASE
WHEN
STOP_RANGE_FL IS NULL
THEN
END_RANGE_DATE
ELSE
Target_DT
END
AS EDate ,
QTY
FROM
(
-- Restrict to only those records
-- and get each range's end-Date
SELECT
T2.* ,
LEAD(Target_DT)
OVER
(
ORDER BY
Target_DT
)
AS END_RANGE_DATE
FROM
(
-- Determine which records are at the beginning and end of the ranges
SELECT
T1.* ,
CASE
WHEN
Target_DT <> PREV_DATE + 1 OR
QTY <> PREV_QTY OR
PREV_DATE IS NULL
THEN
1
END
AS START_RANGE_FL ,
CASE
WHEN
Target_DT <> NEXT_DATE - 1 OR
QTY <> NEXT_QTY OR
NEXT_DATE IS NULL
THEN
1
END
AS STOP_RANGE_FL
FROM
(
-- Get the prev and next dates and quantities for comparisons
SELECT
R.* ,
LAG(Target_DT)
OVER
(
ORDER BY
Target_DT
)
AS PREV_DATE ,
LAG(QTY)
OVER
(
ORDER BY
Target_DT
)
AS PREV_QTY ,
LEAD(Target_DT)
OVER
(
ORDER BY
Target_DT
)
AS NEXT_DATE ,
LEAD(QTY)
OVER
(
ORDER BY
Target_DT
)
AS NEXT_QTY
FROM
Result_T R
) T1
) T2
WHERE
T2.START_RANGE_FL = 1 OR
T2.STOP_RANGE_FL = 1
) T3
WHERE
START_RANGE_FL = 1
- Chris