Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    34

    Unanswered: Align arbitrary time periods to calendar quarter

    Hello,
    Please help me with the following problem.

    Here is my data:
    I have a table UTIL with the following columns
    drop table UTIL;
    create table UTIL
    (
    REF_LINE_ID number,
    PERIOD_START_DATE_WID number,
    PERIOD_END_DATE_WID number,
    CMS_QTY number
    );

    insert into UTIL values (83939, 20101214, 20110416, 11);

    As you can see period_start and period_end dates can cross multiple quarters or months and might represent partial month. So one line in this table might become two or more due to multiple quarters covered in period in expected output.

    Problem:
    I need to stamp respective quarters on every line, split it if period start and end covers more than one quarter and allocate goods_qty based on ratio of days in every split period over total # of days in the original period.

    Here is expected output:
    REF_LINE_ID YEAR_QTR ALLOC_PERIOD_START ALLOC_PERIOD_END CMS_QTY_Q_ALLOC
    83939 2010-Q4 12/14/2010 12/31/2010 1.52
    83939 2011-Q1 01/01/2011 03/31/2011 7.96
    83939 2011-Q2 04/01/2011 04/16/2011 1.34

    Hopefully I stated the problem better, please let me know.

    I built a query which does the trick:
    SELECT
    UF.REF_LINE_ID,
    DD.YEAR_QUARTER,
    GREATEST(TO_DATE(QUARTER_START_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_START_DATE_WID, 'YYYYMMDD')) as ALLOC_PERIOD_START,
    LEAST(TO_DATE(QUARTER_END_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_END_DATE_WID, 'YYYYMMDD')) as ALLOC_PERIOD_END,
    CMS_QTY,
    round(uf.CMS_QTY * ((LEAST(TO_DATE(QUARTER_END_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_END_DATE_WID, 'YYYYMMDD'))- GREATEST( TO_DATE(QUARTER_START_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_START_DATE_WID, 'YYYYMMDD'))) / (TO_DATE(PERIOD_END_DATE_WID, 'YYYYMMDD') - TO_DATE(PERIOD_START_DATE_WID, 'YYYYMMDD'))), 2) as CMS_QTY_Q_ALLOC
    FROM (select * from UTIL ) UF
    JOIN
    (SELECT MIN(DATE_DIM_WID) AS QUARTER_START_DATE, MAX(DATE_DIM_WID) AS QUARTER_END_DATE, YEAR_QUARTER FROM MN_DATE_DIM GROUP BY YEAR_QUARTER) DD
    ON ((UF.PERIOD_START_DATE_WID BETWEEN DD.QUARTER_START_DATE AND DD.QUARTER_END_DATE OR UF.PERIOD_END_DATE_WID BETWEEN DD.QUARTER_START_DATE AND DD.QUARTER_END_DATE)
    or (DD.QUARTER_START_DATE between UF.PERIOD_START_DATE_WID and UF.PERIOD_END_DATE_WID or DD.QUARTER_END_DATE between UF.PERIOD_START_DATE_WID and UF.PERIOD_END_DATE_WID)
    );

    It seems to be working fine however it does not perform well. I have a table with 25 million records and it takes forever to complete.

    Oracle has very useful 'connect by level' mechanism which works fine for month allocation, I don't see yet how to make it work for quarters as there is no counterpart of add_month or month_between functions for quarters. I guess the next step would be to wrap the logic into PL/SQL.

    I appreciate your help and advice.

    Peter.
    Last edited by PeterS; 03-30-13 at 20:53. Reason: Added more details and clarifications

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    only amateurs or fools store DATE datatype in NUMBER column

    It would be helpful if you provided INSERT statements for the test data rows.
    It would be helpful if you provided the actual desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2002
    Posts
    34
    anacedent, thank you for enlightening me, I learned so much about myself.
    please read your own slogans more often, particularly the second one.
    and please don't bother replying to my question.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can't push a string.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2002
    Posts
    34
    I built a query which does what I need:
    SELECT
    UF.REF_LINE_ID,
    DD.YEAR_QUARTER,
    GREATEST(TO_DATE(QUARTER_START_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_START_DATE_WID, 'YYYYMMDD')) as ALLOC_PERIOD_START,
    LEAST(TO_DATE(QUARTER_END_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_END_DATE_WID, 'YYYYMMDD')) as ALLOC_PERIOD_END,
    CMS_QTY,
    round(uf.CMS_QTY * ((LEAST(TO_DATE(QUARTER_END_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_END_DATE_WID, 'YYYYMMDD'))- GREATEST( TO_DATE(QUARTER_START_DATE, 'YYYYMMDD'), TO_DATE(PERIOD_START_DATE_WID, 'YYYYMMDD'))) / (TO_DATE(PERIOD_END_DATE_WID, 'YYYYMMDD') - TO_DATE(PERIOD_START_DATE_WID, 'YYYYMMDD'))), 2) as CMS_QTY_Q_ALLOC
    FROM (select * from UTIL ) UF
    JOIN
    (SELECT MIN(DATE_DIM_WID) AS QUARTER_START_DATE, MAX(DATE_DIM_WID) AS QUARTER_END_DATE, YEAR_QUARTER FROM MN_DATE_DIM GROUP BY YEAR_QUARTER) DD
    ON ((UF.PERIOD_START_DATE_WID BETWEEN DD.QUARTER_START_DATE AND DD.QUARTER_END_DATE OR UF.PERIOD_END_DATE_WID BETWEEN DD.QUARTER_START_DATE AND DD.QUARTER_END_DATE)
    or (DD.QUARTER_START_DATE between UF.PERIOD_START_DATE_WID and UF.PERIOD_END_DATE_WID or DD.QUARTER_END_DATE between UF.PERIOD_START_DATE_WID and UF.PERIOD_END_DATE_WID)
    );

    It seems to be working fine however it does not perform well. I have a table with 25 million records and it takes forever to complete.

    Please help me with performance optimization.

    Thank you,
    Peter.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post EXPLAIN PLAN
    Code:
    SELECT UF.ref_line_id, 
           DD.year_quarter, 
           Greatest(To_date(quarter_start_date, 'YYYYMMDD'), 
           To_date(period_start_date_wid, 'YYYYMMDD')) AS ALLOC_PERIOD_START, 
           Least(To_date(quarter_end_date, 'YYYYMMDD'), 
           To_date(period_end_date_wid, 'YYYYMMDD'))   AS ALLOC_PERIOD_END, 
           cms_qty, 
           Round(uf.cms_qty * ( ( Least(To_date(quarter_end_date, 'YYYYMMDD'), 
                                                         To_date(period_end_date_wid 
                                                         , 
                                                         'YYYYMMDD') 
                                        ) - 
                                  Greatest(To_date(quarter_start_date, 
                                           'YYYYMMDD'), 
                                                         To_date( 
                                  period_start_date_wid, 
                                                         'YYYYMMDD')) ) / 
                                      ( 
                                                     To_date(period_end_date_wid, 
                                                     'YYYYMMDD') - 
                                                                        To_date( 
                                                     period_start_date_wid, 
                                                     'YYYYMMDD') ) ) 
           , 2)                                        AS CMS_QTY_Q_ALLOC 
    FROM   (SELECT * 
            FROM   util) UF 
           join (SELECT Min(date_dim_wid) AS QUARTER_START_DATE, 
                        Max(date_dim_wid) AS QUARTER_END_DATE, 
                        year_quarter 
                 FROM   mn_date_dim 
                 GROUP  BY year_quarter) DD 
             ON ( ( UF.period_start_date_wid BETWEEN 
                    DD.quarter_start_date AND DD.quarter_end_date 
                     OR UF.period_end_date_wid BETWEEN 
                        DD.quarter_start_date AND DD.quarter_end_date ) 
                   OR ( DD.quarter_start_date BETWEEN 
                              UF.period_start_date_wid AND UF.period_end_date_wid 
                         OR DD.quarter_end_date BETWEEN UF.period_start_date_wid AND 
                                                        UF.period_end_date_wid ) );
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The ON condition
    (I removed outmost parentheses.)
    Code:
      ON
       (   UF.PERIOD_START_DATE_WID BETWEEN DD.QUARTER_START_DATE
                                        AND DD.QUARTER_END_DATE
        OR UF.PERIOD_END_DATE_WID   BETWEEN DD.QUARTER_START_DATE
                                        AND DD.QUARTER_END_DATE
       )
       OR
       (   DD.QUARTER_START_DATE BETWEEN UF.PERIOD_START_DATE_WID
                                     AND UF.PERIOD_END_DATE_WID
        OR DD.QUARTER_END_DATE   BETWEEN UF.PERIOD_START_DATE_WID
                                     AND UF.PERIOD_END_DATE_WID
       )
    may be replaced by
    Code:
      ON
           UF.PERIOD_START_DATE_WID <= DD.QUARTER_END_DATE
       AND UF.PERIOD_END_DATE_WID   >= DD.QUARTER_START_DATE

  8. #8
    Join Date
    May 2002
    Posts
    34
    Thank you tonkuma,

    25 million rows were processed in 41 minute. It is more or less acceptable, since it is one time historical load.

    Peter.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Seeing this part...
    Code:
    ...
     FROM  UTIL AS UF
     JOIN
           (SELECT MIN(DATE_DIM_WID) AS QUARTER_START_DATE
                 , MAX(DATE_DIM_WID) AS QUARTER_END_DATE
                 , YEAR_QUARTER
             FROM  MN_DATE_DIM
             GROUP BY
                   YEAR_QUARTER
           ) DD
      ON
    ...
    (Subquery for UTIL may be not neccesary.)


    Example 1: The subquery DD.
    Code:
            SELECT MIN(DATE_DIM_WID) AS QUARTER_START_DATE
                 , MAX(DATE_DIM_WID) AS QUARTER_END_DATE
                 , YEAR_QUARTER
             FROM  MN_DATE_DIM
             GROUP BY
                   YEAR_QUARTER
    Example 2: Add WHERE conditions.
    Code:
            SELECT MIN(DATE_DIM_WID) AS QUARTER_START_DATE
                 , MAX(DATE_DIM_WID) AS QUARTER_END_DATE
                 , YEAR_QUARTER
             FROM  MN_DATE_DIM
             WHERE DATE_DIM_WID
                   >= (SELECT MIN(PERIOD_START_DATE_WID)
                        FROM  UTIL
                      )
               AND DATE_DIM_WID
                   <= (SELECT MAX(PERIOD_END_DATE_WID)
                        FROM  UTIL
                      )
             GROUP BY
                   YEAR_QUARTER
    If the number of returned rows of the subquery DD(Example 1)
    was reduced by adding conditions(like Example 2),
    it might be worth to try example 2 instead of example 1.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.
    (Not tested. just an idea came in my mind.)

    Example 3:
    Code:
    SELECT
           UF.REF_LINE_ID
         , DD.YEAR_QUARTER
         , TO_DATE(QUARTER_START_DATE, 'YYYYMMDD') as ALLOC_PERIOD_START
         , TO_DATE(QUARTER_END_DATE  , 'YYYYMMDD') as ALLOC_PERIOD_END
         , CMS_QTY
         , round(  uf.CMS_QTY
                 * (  (  TO_DATE(QUARTER_END_DATE  , 'YYYYMMDD')
                       - TO_DATE(QUARTER_START_DATE, 'YYYYMMDD')
                      )
                    / (  TO_DATE(PERIOD_END_DATE_WID  , 'YYYYMMDD')
                       - TO_DATE(PERIOD_START_DATE_WID, 'YYYYMMDD')
                      )
                   )
                 , 2
                ) as CMS_QTY_Q_ALLOC
     FROM  UTIL AS UF
     JOIN
           (SELECT MIN(DATE_DIM_WID) AS QUARTER_START_DATE
                 , MAX(DATE_DIM_WID) AS QUARTER_END_DATE
                 , YEAR_QUARTER
             FROM  MN_DATE_DIM
             WHERE DATE_DIM_WID BETWEEN UF.PERIOD_START_DATE_WID
                                    AND UF.PERIOD_END_DATE_WID
             GROUP BY
                   YEAR_QUARTER
           ) DD
      ON
           UF.PERIOD_START_DATE_WID <= DD.QUARTER_END_DATE
       AND UF.PERIOD_END_DATE_WID   >= DD.QUARTER_START_DATE
     ORDER BY
           QUARTER_START_DATE    , QUARTER_END_DATE
         , PERIOD_START_DATE_WID , PERIOD_END_DATE_WID

Tags for this Thread

Posting Permissions

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