Date Split - Sql help - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Date Split - Sql help

  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 01:20 PM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  2. #12
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    BTW - replaced the tabs in my first post with space so it's more readable.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi Chris

    Do you know if SQL Server 2k supports case expressions and analytic functions? (I know DB2 supports analytic functions but not other rdbms)

  4. #14
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    That darn ChrisRLong... I was typing in that exact answer and he beat me to it...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #15
    Join Date
    May 2002
    Posts
    2,645
    Hi Chris,

    While you're at it, can you write a short proof disproving Einstein's Theory of Relativity? And if you have something on prime factorization or finding the next largest Mersenne prime, that would be cool too. If you have time, that is. Thanks.

  6. #16
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by pando
    Do you know if SQL Server 2k supports case expressions and analytic functions? (I know DB2 supports analytic functions but not other rdbms)
    SQLServer has supported CASE for forever (although the syntax is slightly different, if I remember), but not analytic functions. This can probably be done without them, but it would be much more difficult and expensive.

    Originally posted by marist89
    That darn ChrisRLong... I was typing in that exact answer and he beat me to it...
    Hehe

    Originally posted by stecal
    While you're at it, can you write a short proof disproving Einstein's Theory of Relativity? And if you have something on prime factorization or finding the next largest Mersenne prime, that would be cool too. If you have time, that is. Thanks.
    Uh, no problem. I'll, uh, get right on that

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #17
    Join Date
    Nov 2002
    Posts
    11
    Chris,
    I think your last solution may work for me. Tomorrow once i go to office, i will try your sql.
    I've one sql but that doesn't work for overlapping dates, can you check it out? i will post it tomorrow.

    Thanks yaa

  8. #18
    Join Date
    Nov 2002
    Posts
    11
    This is the sql i tried, but it didn't work for overlapping..

    Select
    Res.MainTable_ID,
    out.Start_Date,
    out.End_Date,
    Res.Qty

    From(
    Select
    Min(Result_id) ResID,
    Min(gas_day_dt) Start_Date,
    Max(gas_day_dt) ERes_Date
    from ResultTable
    Group by MainTable_ID
    ) out,
    ResultTable Res,
    MainTable Main
    Where Res.Result_id = out.Result_id
    and Res.MainTable_ID = Main.MainTable_ID

  9. #19
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I don't understand what you wnt me to look at. It won't work. You already found that out. What else do you want to know? I can tell you that even if it did work, it would be inefficient because you don't need the MainTable join on the outside. Actually, you wouldn't even need 2 levels at all if it had worked. But again, it doesn't, so it doesn't matter.

    And why do you say that only the 'last' solution will work for you? Did you already have SQL to properly populate the Result_T table? If so, why did you even mention the MainTable in the original question? I dis-like working up such hefty SQL for no reason. (well okay, it was a little fun) If you already have that SQL, I'd be willing to bet that mine's faster anyway (unless you're using the new MERGE command, which would likely be faster).

    Let me know how it goes,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  10. #20
    Join Date
    Nov 2002
    Posts
    11
    I got an error message saying "feature not enabled: OLAP window function". I thing there should be a setting to incorporate olap functions.. I've to talk to my DBA [oh god ] & let you know about ur sql.
    Thanks

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