Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Unanswered: DB2 OLAP question

    Looking for help in getting the SQL for Unix DB2 version: DB2 v9.5.0.10.

    current table data (pipe delimited for ease of reading):
    ------------------
    CUST_ID | LEVEL | LEVEL_START_DT | LEVEL_END_DT
    123|Exective|17/05/2013|01/01/2014
    123|Exective|02/01/2014|01/01/2015
    456|Exective|02/01/2014|01/01/2015
    456|Exective|17/06/2013|01/01/2014
    456|High Value|17/05/2013| 16/06/2013
    789|Exective|02/01/2014|01/01/2015
    789|Exective|17/05/2013|01/01/2014
    1123|Exective|02/01/2014|01/01/2015
    1123|Exective|17/05/2013|01/01/2014
    1456|High Value|02/01/2014|01/01/2015
    1456|High Value|17/05/2013|01/01/2014
    1789|High value|02/01/2014|01/01/2015
    1789|High Value|17/05/2013|01/01/2014


    I want to get count of members by their customer id per level per year.
    for instance cust_id=123 is Excecutive member between Jan 2, 2013 till Jan 1, 2014, which is how our year is determined and in the next year he/she will be Executive from Jan 2, 2014 to Jan 1, 2015
    however Cust_id=456 used to be high vlaue earlier this year but now is excutive (get latest date from present year) so we will not count high value since that is no longer his/her level.

    Desired output:
    ==========
    CustCount| LEVEL | LEVEL_START_DT | LEVEL_END_DT
    4|Executive|Jan 2, 2013|Jan 1, 2014
    2|High Value|Jan 2, 2013|Jan 1, 2014
    4|Executive|Jan 2, 2014|Jan 1, 2015
    2|High Value|Jan 2, 2014|Jan 1, 2015

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    I don't think that you need OLAP for that.
    Code:
    with 
      p (start_date, y) as (values (date('2013-01-02'), 2))
    , d (y, level_start_dt, level_end_dt) as (
    select 1, start_date, start_date + 1 year - 1 day from p
      union all
    select y+1, level_start_dt + 1 year, level_end_dt + 1 year
    from d
    where d.y < (select y from p)
    )
    select count(1), t.level, d.level_start_dt, d.level_end_dt
    from d
    join table(values
      (123,  'Exective',   date('17/05/2013'), date('01/01/2014'))
    , (123,  'Exective',   date('02/01/2014'), date('01/01/2015'))
    , (456,  'Exective',   date('02/01/2014'), date('01/01/2015'))
    , (456,  'Exective',   date('17/06/2013'), date('01/01/2014'))
    , (456,  'High Value', date('17/05/2013'), date('16/06/2013'))
    , (789,  'Exective',   date('02/01/2014'), date('01/01/2015'))
    , (789,  'Exective',   date('17/05/2013'), date('01/01/2014'))
    , (1123, 'Exective',   date('02/01/2014'), date('01/01/2015'))
    , (1123, 'Exective',   date('17/05/2013'), date('01/01/2014'))
    , (1456, 'High Value', date('02/01/2014'), date('01/01/2015'))
    , (1456, 'High Value', date('17/05/2013'), date('01/01/2014'))
    , (1789, 'High Value', date('02/01/2014'), date('01/01/2015'))
    , (1789, 'High Value', date('17/05/2013'), date('01/01/2014'))
    ) t (CUST_ID, LEVEL, LEVEL_START_DT, LEVEL_END_DT)
    on d.level_end_dt between t.LEVEL_START_DT and t.LEVEL_END_DT
    group by t.level, d.level_start_dt, d.level_end_dt
    p (start_date, y) as (values (date('2013-01-02'), 2)) is a "parameter table" here for start date and a number of years desired.

    Regards,
    Mark B.

  3. #3
    Join Date
    Jul 2013
    Posts
    24

    req changed :(

    Thank you Mark for the response. Sorry I couldn't respond before as requirement was changing and I am new to SQL mostly done analysis work and looking for some help.
    We are just comparing current month and previous month. any record with date from JAN 2, 2013 to Jan 1, 2015 will not be used in comparing process (last month vs current month), just counted once like there are so and so number of customers in executive for future.

    A) Table below is a result of two table join (pipe delimited for ease of reading): Lets look at the example for the month of June, 2013
    ------------------
    CUST_ID | LEVEL | LEVEL_START_DT | LEVEL_END_DT
    123|Executive|02/01/2014|01/01/2015
    123|Executive|17/06/2013|01/01/2014
    456|Classic|02/01/2014|01/01/2015
    456|Classic|10/06/2013|01/01/2014
    456|Basic|17/05/2013|09/06/2013
    789|High Value|02/01/2014|01/01/2015
    789|High Value|17/05/2013|01/01/2014
    1123|Zero|02/01/2014|01/01/2015
    1123|Zero|15/06/2013|01/01/2014

    Now based on the above join, below is just to understand what are we keeping for further processing:

    Hint 1:
    CUST_ID|CURR_LVL|FUTR_LVL|YR_MONTH
    123|Executive| Executive |2013-06
    456|Classic|Classic|2013-06
    789|High Value|High Value|2013-06
    1123|Zero|Zero|2013-06
    1456|High Value| High Value|2013-06
    1789| High Value| High Value |2013-06



    B) Same table described in A but now after July run.

    CUST_ID | LEVEL | LEVEL_START_DT | LEVEL_END_DT
    123|Executive|02/01/2014|01/01/2015
    123|Executive|17/06/2013|01/01/2014
    456|Gold|02/01/2014|01/01/2015
    456|Gold|15/07/2013|01/01/2014
    456|Classic|10/06/2013|14/07/2014
    456|Basic|17/05/2013|09/06/2013
    789|Executive|02/01/2014|01/01/2015
    789|Executive|26/07/2013|01/01/2014
    789|High Value|17/05/2013|25/07/2014
    1123|Basic|02/01/2014|01/01/2015
    1123|Basic|25/07/2013|01/01/2014
    1123|zero|15/06/2013|24/07/2014

    Again, below is just to understand what we are keeping:

    Hint 2:
    CUST_ID|CURR_LVL|FUTR_LVL|YR_MONTH
    123|Executive|Executive|2013-07
    456|Gold|Gold|2013-07
    789|Executive| Executive |2013-07
    1123|Basic|Basic|2013-07

    C) Comparing two months Hint 1 and Hint 2 June-July, final table will aggregate results and will look like below.

    Number of Executive that remained Executive: 1 | 2013-07
    Number of High Value that are now Executive:1| 2013-07
    Number of Classic that are now Executive: 0| 2013-07
    Number of Basic that are now Executive:0| 2013-07
    Number of Zero that are now Executive:0| 2013-07

    Number of Classic that are now Gold: 1| 2013-07
    Number of Zero that are now Basic:1| 2013-07
    Last edited by zaino22; 08-08-13 at 00:40.

  4. #4
    Join Date
    Jul 2013
    Posts
    24
    Thank you for the sql i couldnt respond because i was trying to get new logic

  5. #5
    Join Date
    Dec 2012
    Location
    Ukraine
    Posts
    24
    As far as I understand you should create Facts and Dimension tables. Facts table should contain customers and Dimensions are Position, Start Date, End Date.

  6. #6
    Join Date
    Jul 2013
    Posts
    24
    Thank you for the response.

    I have now some sense how to do this, and want your opinion.

    Requirements are still not solid but in any case even if logical may be slightly changed I have some idea about the req. Business want to see the movement between levels. so If customer 456 in Example 'B' in earlier example for July-run went from Basic to Classic (to Gold) all in the month of July (Basic was awarded in May). At this point Business doesn't care about keeping track of Classic because it was short lived, they want to keep track of this customer as someone who went from Basic to Gold. in other words, if there are more than one Jump in levels they want to keep track of only the most recent and the first one when month started.

    Modified example of B (change in dates):
    ----------------------
    456|Gold|02/01/2014|01/01/2015
    456|Gold|15/07/2013|01/01/2014
    456|Classic|12/07/2013|14/07/2014
    456|Basic|17/05/2013|09/07/2013

    My solution which requires your input:
    I thought of separating future predictions in a separate sql, results of which can be either loaded separate just because it is easier to calculate for ex find anyone who has LEVEL_END_DT='01/01/2015' (pass date as parameter) boom we can proceed to aggregate these results and we can load them or merge later on with results below to have just one load for all.

    To see movement between level, I thought of Self join.
    1) self join two results on CUST_ID, in which we are interested in finding out what is the Level for customer (see below Level_2), basically pivotting

    Both sql will state that find records where LEVEL_END_DT between '01/07/2015' and '31/07/2015' (July 1 to July 30)
    and then Join on Customer ID to get two values below.
    CUST_ID | LEVEL_1 |LEVEL_2

    2) run the sql to extract customer who have LEVEL_END_DT='01/01/2014' (pass it as param) Then Join the results on Customer ID with the results of#1 to get all three LEVELS.

    CUST_ID | LEVEL | LEVEL_START_DT | LEVEL_END_DT| LEVEL_1|LEVEL_2

    Now check if the Level_1 (assuming its the one with 'Classic' LEVEL) is populated, we disregard it and focus on 'LEVEL' and 'LEVEL_2' only.

    At the end business wants to know how many ppl moved from Basic to Gold and (there is whole list but I just used one example here).
    so 456 will be another stats in aggregate eventually but for this above results in #1 and #2 will go through an ETL tool.

    End results will look something like following:
    1000 customers went from Basic to Gold in the month of July.

    Please let me know if the above logic is what you would recommend or is there a better way. Only issue I see in pivotting is that if there are more movement (logically possible) I am not taking care of that.

    I realized I asked too much so I went and did some homework thank you for silent encouragement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It would be better to define exactly your required results with enough examples(including boundary data and exceptional data),
    before you consider the logic to get the results.

    Example 1: To know level at July 01(level_at_begin) and July 31(level_at_end) for each cust_id.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Modified_example_B
    ( cust_id , level , level_start_dt , level_end_dt ) AS (
    VALUES
      (  123 , 'Exective'   , date('17.05.2013') , date('01.01.2014') )
    , (  123 , 'Exective'   , date('02.01.2014') , date('01.01.2015') )
    , (  456 , 'Gold'       , date('02.01.2014') , date('01.01.2015') )
    , (  456 , 'Gold'       , date('15.07.2013') , date('01.01.2014') )
    , (  456 , 'Classic'    , date('12.07.2013') , date('14.07.2014') )
    , (  456 , 'Basic'      , date('17.05.2013') , date('09.07.2013') )
    )
    , with_dates_at_begin_end AS (
    SELECT s.*
     FROM  (SELECT t.*
                 , MAX(CASE
                       WHEN DATE('2013-07-01')
                            BETWEEN level_start_dt
                                AND level_end_dt   THEN
                            level_start_dt
                        END
                      )
                      OVER(PARTITION BY cust_id
                      ) AS max_date_at_begin
                 , MAX(CASE
                       WHEN DATE('2013-07-31')
                            BETWEEN level_start_dt
                                AND level_end_dt   THEN
                            level_start_dt
                        END
                      )
                      OVER(PARTITION BY cust_id
                      ) AS max_date_at_end
             FROM  Modified_example_B AS t
             WHERE level_end_dt    >= DATE('2013-07-01')
               AND  level_start_dt <= DATE('2013-07-31')
           ) AS s
    )
    SELECT b.cust_id
         , b.level AS level_at_begin
         , e.level AS level_at_end
     FROM  with_dates_at_begin_end AS b
     INNER JOIN
           with_dates_at_begin_end AS e
      ON   e.cust_id = b.cust_id
       AND b.level_start_dt = b.max_date_at_begin
       AND e.level_start_dt = e.max_date_at_end
    ;
    ------------------------------------------------------------------------------
    
    CUST_ID     LEVEL_AT_BEGIN LEVEL_AT_END
    ----------- -------------- ------------
            123 Exective       Exective    
            456 Basic          Gold        
    
      2 record(s) selected.

    C)..., final table will aggregate results and will look like below.

    Number of Executive that remained Executive: 1 | 2013-07
    Number of High Value that are now Executive:1| 2013-07
    ...
    ...
    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Modified_example_B
    ( cust_id , level , level_start_dt , level_end_dt ) AS (
    VALUES
      (  123 , 'Exective'   , date('17.05.2013') , date('01.01.2014') )
    , (  123 , 'Exective'   , date('02.01.2014') , date('01.01.2015') )
    , (  456 , 'Gold'       , date('02.01.2014') , date('01.01.2015') )
    , (  456 , 'Gold'       , date('15.07.2013') , date('01.01.2014') )
    , (  456 , 'Classic'    , date('12.07.2013') , date('14.07.2014') )
    , (  456 , 'Basic'      , date('17.05.2013') , date('09.07.2013') )
    )
    , with_dates_at_begin_end AS (
    SELECT s.*
     FROM  (SELECT t.*
                 , MAX(CASE
                       WHEN DATE('2013-07-01')
                            BETWEEN level_start_dt
                                AND level_end_dt   THEN
                            level_start_dt
                        END
                      )
                      OVER(PARTITION BY cust_id
                      ) AS max_date_at_begin
                 , MAX(CASE
                       WHEN DATE('2013-07-31')
                            BETWEEN level_start_dt
                                AND level_end_dt   THEN
                            level_start_dt
                        END
                      )
                      OVER(PARTITION BY cust_id
                      ) AS max_date_at_end
             FROM  Modified_example_B AS t
             WHERE level_end_dt    >= DATE('2013-07-01')
               AND  level_start_dt <= DATE('2013-07-31')
           ) AS s
    )
    SELECT 'Number of ' || b.level
           || ' that '
           || COALESCE(
                 'are now '  || NULLIF(e.level , b.level)
               , 'remained ' || e.level
              )
           || ': ' || COUNT(*) || ' | '
           || '2013-07'
           AS summary_result
     FROM  with_dates_at_begin_end AS b
     INNER JOIN
           with_dates_at_begin_end AS e
      ON   e.cust_id = b.cust_id
       AND b.level_start_dt = b.max_date_at_begin
       AND e.level_start_dt = e.max_date_at_end
     GROUP BY
           b.level
         , e.level
    ;
    ------------------------------------------------------------------------------
    
    SUMMARY_RESULT                                                  
    ----------------------------------------------------------------
    Number of Basic that are now Gold: 1 | 2013-07                  
    Number of Exective that remained Exective: 1 | 2013-07          
    
      2 record(s) selected.

  8. #8
    Join Date
    Jul 2013
    Posts
    24
    --SQL below gives me the output below which will be the basis of my next processing.

    SELECT
    m.CUST_ID,
    m.BI_ID,
    m.LEVEL_START_DT,
    m.LEVEL_END_DT,
    p.LEVEL
    FROM <SCHEMA_NAME>.Table_A m
    JOIN <SCHEMA_NAME>.Table_B p
    ON
    m.Table_A = p.Table_B

    where
    p.PGM = 'IMP'
    and

    m.CUST_ID in
    (123,
    345,
    567,
    789,
    901,
    1122)

    and

    --Following is to only pick those whose CONTRIBUTION INDICATOR is High

    m.BI_ID in
    (
    SELECT
    c.BI_ID
    FROM
    <SCHEMA_NAME>.Table_C c
    INNER JOIN
    <SCHEMA_NAME>.Table_D a

    ON
    a.HIER_ID = c.HIER_ID
    where

    a.TYPE = 'REAL' And
    c.SLOT = 'AM' And
    c.CONTR_IND = 'H'


    )
    ORDER BY m.CUST_ID, m.LEVEL_END_DT

    --Output from above SQL Looks like below:
    CUST_ID |BI_ID | LEVEL_START_DT | LEVEL_END_DT | LEVEL
    123 | 779 | 17/05/2013 | 16/06/2013 | Gold
    123 | 779 | 17/06/2013 | 01/01/2014 | Executive
    123 | 779 | 02/01/2014 | 01/01/2015 | Executive
    345 | 869 | 17/05/2013 | 01/01/2014 | Executive
    345 | 869 | 02/01/2014 | 01/01/2015 | Executive
    567 | 103 | 17/05/2013 | 01/01/2014 | Executive
    567 | 103 | 02/01/2014 | 01/01/2015 | Executive
    789 | 568 | 17/05/2013 | 01/01/2014 | Executive
    789 | 568 | 02/01/2014 | 01/01/2015 | Executive
    901 | 459 | 17/05/2013 | 09/06/2013 | Gold
    901 | 459 | 10/06/2013 | 01/01/2014 | Executive
    901 | 459 | 02/01/2014 | 01/01/2015 | Executive
    1122 | 745 | 17/05/2013 | 16/06/2013 | Gold
    1122 | 745 | 17/06/2013 | 01/01/2014 | Executive
    1122 | 745 | 02/01/2014 | 01/01/2015 | Executive

    if you notice, there are two or three records for each Customer. I have order by on m.LEVEL_END_DT so you will see in sequence.
    First one defines the Level Customer moved from. If we look at Cust_ID=123, customer has moved to Executive from Gold on June 17, 2013. Second one defines the Current Level (usually Current and Future levels are same just dates are different) and the last one defines Customers' Future Level (which always has a start date of Jan 2 of next year to Jan 1 year after for example Jan 2, 2014 to Jan 1, 2015)
    Some records like CUST_ID=345 only have two but logically CUST_ID can have more but for sure there will only be one Future and one Current, rest is basically historical. So if we look at month of June 2013 it is possible for CUST_ID=123 to move from Gold to Executive to Elite (level not listed in the example) if that happens, we only care about the latest Level (which is Elite), and the Level customer was when he/she entered in the month in this case Gold (Sort Descending pick first one??), we don't worry about the middle Level as it was short lived in a month (see example below).

    CUST_ID |BI_ID | LEVEL_START_DT |LEVEL_END_DT |LEVEL
    123 | 779 | 17/05/2013 | 16/06/2013 |Gold
    123 | 779 | 17/06/2013 | 25/06/2013 |Executive
    123 | 779 | 26/06/2014 | 01/01/2014 |Elite
    123 | 779 | 02/01/2014 | 01/01/2015 |Elite

    I have been trying to get the output from SQL statement, and output will look like below where "LEVEL" is where Customer used to be at in the begining of the month, and "LEVEL_2" is current level. In a case where Customer (for example CUST_ID=345 did not move Levels in the month of June) has not moved the level in the month of June, "LEVEL" will be Null.


    CUST_ID | BI_ID | LEVEL_START_DT | LEVEL_END_DT | LEVEL LEVEL_2

    Then I would have to use Infomatical ETL tool to check if LEVEL is null (which will mean customer has not moved in the month of June) then take LEVEL_2 value and see if the value is Classic Gold, Executive Elite, and put it in the right bucket (customer was Executive and stayed Executive) and then it will be aggregated.

    ETL job will be a messy one. if there is a smipler solution in sql then please share!

    Final output after I perform Aggregate:
    <following are the trickly ones where I have to track movemenet between Levels>

    Customer who moved from Classic to Gold:50
    Customer who moved from Gold to Executive:20
    Customer who moved from Executive to Elite:5
    Customer who moved from Gold to Classic:5
    Customer who moved from Executive to Gold:20
    Customer who moved from Elite to Executive:15

    <I can pick these recods easily by looking at value in LEVEL_END_DT and pick the ones that have LEVEL_END_DT any date earlier than June 1, 2013)
    Customer who are Elite (who were Elite from the begining of the month and still are -unchanged): 500
    Customer who are Executive (who were Executive from the begining of the month and still are -unchanged):100 (Cust_ID=345 fit this criteria)
    Customer who are Gold (who were Gold from the begining of the month and still are -unchanged): 2500
    Customer who are Classic (who were Classic from the begining of the month and still are -unchanged): 15000


    <I can pick these recods easily by looking at value in LEVEL_END_DT and pick the ones that have LEVEL_END_DT = '01/01/2015')
    Total customer for Elite Level for Future: 200
    Total customer for Executive for Future Level: 1500
    Total customer for Gold for Future Level: 5,000
    Total customer for Classic for Future Level: 20,000

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    zaino22,

    What results did you got from our examples(i.e. Mark B's and my Example 1 and Example 2)?.

    And, what were the differences from your expected(and/or desired) results?

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your descriptions of your requirements were unclear (and seems to include some inconsistencies) for me.
    So, I want you to confirm your requirements by sample/test data and expected results from the data.

    Here are sample/test data exracted and modified from your posts.
    Didn't these data include some contradictions from your actual data?
    (In other words, are these data usable to describe your requirements?)

    Example 3: sample/test data.
    Code:
    SELECT *
     FROM  sample_test_data
     ORDER BY
           cust_id , bi_id
         , level_start_dt
    ;
    ------------------------------------------------------------------------------
    
    CUST_ID     BI_ID       LEVEL_START_DT LEVEL_END_DT LEVEL    
    ----------- ----------- -------------- ------------ ---------
            123         779 2013-05-17     2013-06-16   Gold     
            123         779 2013-06-17     2013-06-25   Executive
            123         779 2013-06-26     2014-01-01   Elite    
            123         779 2014-01-02     2015-01-01   Elite    
            345         869 2013-05-17     2014-01-01   Executive
            345         869 2014-01-02     2015-01-01   Executive
            456         234 2013-05-17     2013-06-09   Basic    
            456         234 2013-06-10     2013-07-14   Classic  
            456         234 2013-07-15     2014-01-01   Gold     
            456         234 2014-01-02     2015-01-01   Gold     
            567         103 2013-05-17     2014-01-01   Executive
            567         103 2014-01-02     2015-01-01   Executive
            789         568 2013-05-17     2014-01-01   Executive
            789         568 2014-01-02     2015-01-01   Executive
            901         459 2013-05-17     2013-06-09   Gold     
            901         459 2013-06-10     2014-01-01   Executive
            901         459 2014-01-02     2015-01-01   Executive
           1122         745 2013-05-17     2013-06-16   Gold     
           1122         745 2013-06-17     2014-01-01   Executive
           1122         745 2014-01-02     2015-01-01   Executive
    
      20 record(s) selected.

    You used sometime words current level and future level.
    But, when I read your descriptions,
    I thought that you want to compare the level at beginning of current month and the level at end of current month.

    So, I suppose that the required results might be like Example 4 and Example 5,
    if current month was June.
    Are these right?

    Example 4: expected intermediate results from Example 3,
    if current month was June.
    Code:
    CUST_ID     LEVEL_AT_BEGIN LEVEL_AT_END
    ----------- -------------- ------------
            123 Gold           Elite       
            345 Executive      Executive   
            456 Basic          Classic     
            567 Executive      Executive   
            789 Executive      Executive   
            901 Gold           Executive   
           1122 Gold           Executive
    Example 5: Summary from Example 4.
    Code:
    SUMMARY_RESULT                                                    
    ------------------------------------------------------------------
    Number of Basic that are now Classic: 1 | 2013-06                 
    Number of Executive that remained Executive: 3 | 2013-06          
    Number of Gold that are now Elite: 1 | 2013-06                    
    Number of Gold that are now Executive: 2 | 2013-06

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, no response from OP(zaino22) saying the validities of sample data provided by Example 3, nor results(Example 4 and Example 5) from the data,
    Example 1 and Example 2 would produce Example 4 and Example 5 from sample/test data of Example 3,
    if the values DATE('2013-07-01') and DATE('2013-07-31') were replaced by values DATE('2013-06-01') and DATE('2013-06-30').

    But, Example 1 and Example 2 were too roundabout ways.


    Here are simpler and more sraightforward ways.
    (If Example 4 and Example 5 from Example 3 were proper results)

    Example 6:
    Code:
    WITH
     p( p_current_date ) AS (VALUES date('2013-06-15') )
    /* using test data of Example 3, like ...
    , sample_test_data
    ( cust_id , bi_id , level_start_dt , level_end_dt , level ) AS (
    VALUES
    ...
    )
    */
    , with_levels_at_begin_end AS (
    SELECT cust_id
         , MAX(CASE
               WHEN start_of_month
                    BETWEEN level_start_dt
                        AND level_end_dt   THEN
                    level
               END
              ) AS level_at_begin
         , MAX(CASE
               WHEN end_of_month
                    BETWEEN level_start_dt
                        AND level_end_dt   THEN
                    level
               END
              ) AS level_at_end
         , MAX(start_of_month) AS start_of_month
     FROM  sample_test_data AS t
     CROSS JOIN
           (SELECT p_current_date - (DAY(p_current_date) - 1) DAYS
                 , LAST_DAY(p_current_date)
             FROM  p
           ) AS f(start_of_month , end_of_month)
     WHERE level_end_dt   >= start_of_month
       AND level_start_dt <= end_of_month
     GROUP BY
           cust_id
    )
    SELECT *
     FROM  with_levels_at_begin_end
    ;
    ------------------------------------------------------------------------------
    
    CUST_ID     LEVEL_AT_BEGIN LEVEL_AT_END START_OF_MONTH
    ----------- -------------- ------------ --------------
            123 Gold           Elite        2013-06-01    
            345 Executive      Executive    2013-06-01    
            456 Basic          Classic      2013-06-01    
            567 Executive      Executive    2013-06-01    
            789 Executive      Executive    2013-06-01    
            901 Gold           Executive    2013-06-01    
           1122 Gold           Executive    2013-06-01    
    
      7 record(s) selected.
    Example 7:
    Code:
    WITH
     p( p_current_date ) AS (VALUES date('2013-06-15') )
    /* using test data of Example 3, like ...
    , sample_test_data
    ( cust_id , bi_id , level_start_dt , level_end_dt , level ) AS (
    VALUES
    ...
    )
    */
    , with_levels_at_begin_end AS (
    SELECT cust_id
         , MAX(CASE
               WHEN start_of_month
                    BETWEEN level_start_dt
                        AND level_end_dt   THEN
                    level
               END
              ) AS level_at_begin
         , MAX(CASE
               WHEN end_of_month
                    BETWEEN level_start_dt
                        AND level_end_dt   THEN
                    level
               END
              ) AS level_at_end
         , MAX(start_of_month) AS start_of_month
     FROM  sample_test_data AS t
     CROSS JOIN
           (SELECT p_current_date - (DAY(p_current_date) - 1) DAYS
                 , LAST_DAY(p_current_date)
             FROM  p
           ) AS f(start_of_month , end_of_month)
     WHERE level_end_dt   >= start_of_month
       AND level_start_dt <= end_of_month
     GROUP BY
           cust_id
    )
    SELECT 'Number of ' || level_at_begin
           || ' that '
           || COALESCE(
                 'are now '  || NULLIF(level_at_end , level_at_begin)
               , 'remained ' || level_at_end
              )
           || ': ' || COUNT(*) || ' | '
           || SUBSTR(CHAR(start_of_month , ISO) , 1 , 7)
           AS summary_result
     FROM  with_levels_at_begin_end
     GROUP BY
           level_at_begin
         , level_at_end
         , start_of_month
    ;
    ------------------------------------------------------------------------------
    
    SUMMARY_RESULT                                                    
    ------------------------------------------------------------------
    Number of Basic that are now Classic: 1 | 2013-06                 
    Number of Executive that remained Executive: 3 | 2013-06          
    Number of Gold that are now Elite: 1 | 2013-06                    
    Number of Gold that are now Executive: 2 | 2013-06                
    
      4 record(s) selected.
    Last edited by tonkuma; 08-18-13 at 13:47.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You showed the format of output like this way...
    Number of Executive that remained Executive: 1 | 2013-07
    Number of High Value that are now Executive:1| 2013-07
    Number of Classic that are now Executive: 0| 2013-07
    Number of Basic that are now Executive:0| 2013-07
    Number of Zero that are now Executive:0| 2013-07

    Number of Classic that are now Gold: 1| 2013-07
    Number of Zero that are now Basic:1| 2013-07
    But, I prefer to separate the output into some columns like...

    Example 7:
    Code:
    CHANGE_OF_LEVELS                            COUNTS      YYYY_MM
    ------------------------------------------- ----------- -------
    Number of Basic that are now Classic                  1 2013-06
    Number of Executive that remained Executive           3 2013-06
    Number of Gold that are now Elite                     1 2013-06
    Number of Gold that are now Executive                 2 2013-06
    Example 8: a query to prodeuce Example 7 sample output from the test data(same as Example 3).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     p( p_current_date ) AS (VALUES date('2013-06-15') )
    , sample_test_data
    ( cust_id , bi_id , level_start_dt , level_end_dt , level ) AS (
    VALUES
      (   123 , 779 , date('17.05.2013') , date('16.06.2013') , 'Gold' )
    , (   123 , 779 , date('17.06.2013') , date('25.06.2013') , 'Executive' )
    , (   123 , 779 , date('26.06.2013') , date('01.01.2014') , 'Elite' )
    , (   123 , 779 , date('02.01.2014') , date('01.01.2015') , 'Elite' )
    , (   345 , 869 , date('17.05.2013') , date('01.01.2014') , 'Executive' )
    , (   345 , 869 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , (   456 , 234 , date('02.01.2014') , date('01.01.2015') , 'Gold' )
    , (   456 , 234 , date('15.07.2013') , date('01.01.2014') , 'Gold' )
    , (   456 , 234 , date('10.06.2013') , date('14.07.2013') , 'Classic' )
    , (   456 , 234 , date('17.05.2013') , date('09.06.2013') , 'Basic' )
    , (   567 , 103 , date('17.05.2013') , date('01.01.2014') , 'Executive' )
    , (   567 , 103 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , (   789 , 568 , date('17.05.2013') , date('01.01.2014') , 'Executive' )
    , (   789 , 568 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , (   901 , 459 , date('17.05.2013') , date('09.06.2013') , 'Gold' )
    , (   901 , 459 , date('10.06.2013') , date('01.01.2014') , 'Executive' )
    , (   901 , 459 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , (  1122 , 745 , date('17.05.2013') , date('16.06.2013') , 'Gold' )
    , (  1122 , 745 , date('17.06.2013') , date('01.01.2014') , 'Executive' )
    , (  1122 , 745 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    )
    , with_levels_at_begin_end AS (
    SELECT cust_id
         , MAX(CASE
               WHEN start_of_month
                    BETWEEN level_start_dt
                        AND level_end_dt   THEN
                    level
               END
              ) AS level_at_begin
         , MAX(CASE
               WHEN end_of_month
                    BETWEEN level_start_dt
                        AND level_end_dt   THEN
                    level
               END
              ) AS level_at_end
         , p_current_date
     FROM  sample_test_data AS t
     CROSS JOIN
           (SELECT p_current_date
                 , p_current_date - (DAY(p_current_date) - 1) DAYS
                 , LAST_DAY(p_current_date)
             FROM  p
           ) AS f(p_current_date , start_of_month , end_of_month)
     WHERE level_end_dt   >= start_of_month
       AND level_start_dt <= end_of_month
     GROUP BY
           cust_id
         , p_current_date
    )
    SELECT 'Number of ' || level_at_begin
           || ' that '
           || COALESCE(
                 'are now '  || NULLIF(level_at_end , level_at_begin)
               , 'remained ' || level_at_end
              )
           AS change_of_levels
         , COUNT(*) AS counts
         , SUBSTR(CHAR(p_current_date , ISO) , 1 , 7) AS yyyy_mm
     FROM  with_levels_at_begin_end
     GROUP BY
           level_at_begin
         , level_at_end
         , p_current_date
    ;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    zaino22,

    Before going to some solutions(queries to produce the expected results),
    could you confirm the sample/test data and the expected results from the data(described in the following quoted post)?

    On reflection, I might be too hurry to reach the queries.
    Quote Originally Posted by tonkuma View Post
    Your descriptions of your requirements were unclear (and seems to include some inconsistencies) for me.
    So, I want you to confirm your requirements by sample/test data and expected results from the data.

    Here are sample/test data exracted and modified from your posts.
    Didn't these data include some contradictions from your actual data?
    (In other words, are these data usable to describe your requirements?)

    Example 3: sample/test data.
    Code:
    SELECT *
     FROM  sample_test_data
     ORDER BY
           cust_id , bi_id
         , level_start_dt
    ;
    ------------------------------------------------------------------------------
    
    CUST_ID     BI_ID       LEVEL_START_DT LEVEL_END_DT LEVEL    
    ----------- ----------- -------------- ------------ ---------
            123         779 2013-05-17     2013-06-16   Gold     
            123         779 2013-06-17     2013-06-25   Executive
            123         779 2013-06-26     2014-01-01   Elite    
            123         779 2014-01-02     2015-01-01   Elite    
            345         869 2013-05-17     2014-01-01   Executive
            345         869 2014-01-02     2015-01-01   Executive
            456         234 2013-05-17     2013-06-09   Basic    
            456         234 2013-06-10     2013-07-14   Classic  
            456         234 2013-07-15     2014-01-01   Gold     
            456         234 2014-01-02     2015-01-01   Gold     
            567         103 2013-05-17     2014-01-01   Executive
            567         103 2014-01-02     2015-01-01   Executive
            789         568 2013-05-17     2014-01-01   Executive
            789         568 2014-01-02     2015-01-01   Executive
            901         459 2013-05-17     2013-06-09   Gold     
            901         459 2013-06-10     2014-01-01   Executive
            901         459 2014-01-02     2015-01-01   Executive
           1122         745 2013-05-17     2013-06-16   Gold     
           1122         745 2013-06-17     2014-01-01   Executive
           1122         745 2014-01-02     2015-01-01   Executive
    
      20 record(s) selected.

    You used sometime words current level and future level.
    But, when I read your descriptions,
    I thought that you want to compare the level at beginning of current month and the level at end of current month.

    So, I suppose that the required results might be like Example 4 and Example 5,
    if current month was June.
    Are these right?

    Example 4: expected intermediate results from Example 3,
    if current month was June.
    Code:
    CUST_ID     LEVEL_AT_BEGIN LEVEL_AT_END
    ----------- -------------- ------------
            123 Gold           Elite       
            345 Executive      Executive   
            456 Basic          Classic     
            567 Executive      Executive   
            789 Executive      Executive   
            901 Gold           Executive   
           1122 Gold           Executive
    Example 5: Summary from Example 4.
    Code:
    SUMMARY_RESULT                                                    
    ------------------------------------------------------------------
    Number of Basic that are now Classic: 1 | 2013-06                 
    Number of Executive that remained Executive: 3 | 2013-06          
    Number of Gold that are now Elite: 1 | 2013-06                    
    Number of Gold that are now Executive: 2 | 2013-06

  14. #14
    Join Date
    Jul 2013
    Posts
    24
    I feel terrible that I couldnt reply earlier, and you are extremely helpful person. First of all, please accept my sincere gratitude that you worked on my issue and provided numerous solutions, and not only that but keen to know what am I looking for. No words to describe how much respect I have for your kind actions. Thank you....

    I am happy with following... it meets one business requirement.

    CHANGE_OF_LEVELS COUNTS YYYY_MM
    ------------------------------------------- ----------- -------
    Number of Basic that are now Classic 1 2013-06
    Number of Executive that remained Executive 3 2013-06
    Number of Gold that are now Elite 1 2013-06
    Number of Gold that are now Executive 2 2013-06


    If you let me could I ask following?
    Business also wanted to know how many people were Gold/Classic/Elite etc in a month. For instance we are in August now and want to know how many customers were Gold in the month of May. If we look at '123' customer it is now Elite but was Gold till May end and would have selected this customer if were were to do a query for current Gold customers for May 1 to May 31 data .

    CUST_ID BI_ID LEVEL_START_DT LEVEL_END_DT LEVEL
    ----------- ----------- -------------- ------------ ---------
    123 779 2013-05-17 2013-06-16 Gold

    I will try tomorrow but could I use one of your example to compare semi-annual movements between tiers (Basic to Classic etc). for example if semi annual dates Jan 1 to June 30 (for Semi 1) and July 1 to Dec 31 (for Semi 2 for instance). Can I find out someone who is Gold in Semi2, what was his Level in Semi 1 (lets say he was Basic) so we could eventually say "10 Basic in Semi 1 are now Gold in Semi 2". Comparison should be able to do Any Semi annual to Any Semi annual data.

    Thank you again for your all the help....

  15. #15
    Join Date
    Jul 2013
    Posts
    24
    I tried to find out the answer to my most recent question to you and I used Example 3: sample/test data to compare customer Level at the end of May to customer level with the ones at the end of July (I believe for Semi annual comparision in the following sql, I can replace '2013-05-31' with '2013-06-30' to get the levels at the end of Semi 1 and '2013-07-31' with '2013-12-31' to get the levels end of Semi2 (This will show me A customer level in Semi 1 and Semi 2 and I can find out if that was changed or not.)

    Please advise if this is correct.

    WITH
    p( p_current_date ) AS (VALUES date('2013-06-15') )
    , sample_test_data
    ( cust_id , bi_id , level_start_dt , level_end_dt , level ) AS (
    VALUES
    ( 123 , 779 , date('17.05.2013') , date('16.06.2013') , 'Gold' )
    , ( 123 , 779 , date('17.06.2013') , date('25.06.2013') , 'Executive' )
    , ( 123 , 779 , date('26.06.2013') , date('01.01.2014') , 'Elite' )
    , ( 123 , 779 , date('02.01.2014') , date('01.01.2015') , 'Elite' )
    , ( 345 , 869 , date('17.05.2013') , date('01.01.2014') , 'Executive' )
    , ( 345 , 869 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , ( 456 , 234 , date('02.01.2014') , date('01.01.2015') , 'Gold' )
    , ( 456 , 234 , date('15.07.2013') , date('01.01.2014') , 'Gold' )
    , ( 456 , 234 , date('10.06.2013') , date('14.07.2013') , 'Classic' )
    , ( 456 , 234 , date('17.05.2013') , date('09.06.2013') , 'Basic' )
    , ( 567 , 103 , date('17.05.2013') , date('01.01.2014') , 'Executive' )
    , ( 567 , 103 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , ( 789 , 568 , date('17.05.2013') , date('01.01.2014') , 'Executive' )
    , ( 789 , 568 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , ( 901 , 459 , date('17.05.2013') , date('09.06.2013') , 'Gold' )
    , ( 901 , 459 , date('10.06.2013') , date('01.01.2014') , 'Executive' )
    , ( 901 , 459 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    , ( 1122 , 745 , date('17.05.2013') , date('16.06.2013') , 'Gold' )
    , ( 1122 , 745 , date('17.06.2013') , date('01.01.2014') , 'Executive' )
    , ( 1122 , 745 , date('02.01.2014') , date('01.01.2015') , 'Executive' )
    )
    ,
    with_dates_at_begin_end AS (
    SELECT s.*
    FROM (SELECT t.*
    , MAX(CASE
    WHEN DATE('2013-05-31')
    BETWEEN level_start_dt
    AND level_end_dt THEN
    level_start_dt
    END
    )
    OVER(PARTITION BY cust_id
    ) AS max_date_at_begin
    , MAX(CASE
    WHEN DATE('2013-07-31')
    BETWEEN level_start_dt
    AND level_end_dt THEN
    level_start_dt
    END
    )
    OVER(PARTITION BY cust_id
    ) AS max_date_at_end
    FROM Modified_example_B AS t
    WHERE level_end_dt <= DATE('2013-07-31')
    AND level_start_dt >= DATE('2013-05-31')
    ) AS s
    )

    SELECT b.cust_id
    , b.level AS level_at_begin
    , e.level AS level_at_end
    FROM with_dates_at_begin_end AS b

Posting Permissions

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