DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 20

Thread: Date Split - Sql help

Threaded View

  1. #11
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.
    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
    Let me know if this works for you,

    - Chris
    Last edited by chrisrlong; 11-12-2002 at 02:20 PM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width