Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    8

    Unanswered: Looping through dates

    Right now I have a query that I run at the beginning of each month and it summarizes data for the previous month and appends to a table (past months data remains the same). However I have noticed that the past months data may change, so I would like to update the past data as well.
    When I run the query, I need to manually change a month value that is used in the query, is there a way I can do this and update previous months all in one shot?

    Here's what my code is like

    Code:
    INSERT INTO USER1.SUMMARY("MONTH", PRODUCTGROUP, ENTERED_RECORDS, APPROVED_RECORDS,
           DECLINED_RECORDS, ENTERED_BOOKED_RECORD)
    
    SELECT "Month",
           PRODUCTGROUP,
           SUM(CASE WHEN ENTER_MONTH =."Month"
           then 1
           else 0 
           END) AS ENTERED_RECORDS,
           SUM(CASE WHEN AJUDICATED_MONTH ="Month" and AJUD_CODE = 'A'
           then 1
           else 0
           END) as APPROVED_RECORDS,
           SUM(CASE WHEN DT_AJUDICATED is not null and AJUDICATED_MONTH ="Month" and AJUD_CODE = 'R'
           then 1
           else 0
           END) as DECLINED_RECORDS,
           SUM(CASE WHEN DT_BOOKED is not null and AJUDICATED_MONTH ="Month"
           then 1
           else 0
           END) as ENTERED_BOOKED_RECORDS,
           SUM(CASE WHEN DT_BOOKED is not null and BOOKED_MONTH ="Month" 
           then 1
           else 0
           END) as BOOKED_RECORDS
    FROM (SELECT *, DATE('2011-05-01') as "Month" FROM TABLE1)
    GROUP BY PRODUCTGROUP, "Month"
    This appends May's data into my summary table, now I would like to update data in from Jan 2010 to the current month.

    Thanks for any inputs and help.
    Much appreciated.
    I am fairly new to all this ):

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Consider the MERGE statement.

  3. #3
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    Consider the MERGE statement.
    Thanks for the suggestion, I know merge is able to update/insert new rows into a table but my concern is more on the dates. Is there a way I can update all the previous months without manually changing the current month.

    Thanks again ( :

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are examples by using CURRENT_DATE special register, built-in functions and date calculations of DB2 SQL.

    Example 1: Previous month
    Code:
    BETWEEN LAST_DAY(CURRENT_DATE - 2 MONTHs) + 1 DAY /* First day(01) of previous month */
        AND LAST_DAY(CURRENT_DATE - 1 MONTH)          /* Last day of previous month */
    Example 2: n months before
    Code:
    BETWEEN LAST_DAY(CURRENT_DATE - (n + 1) MONTHs) + 1 DAY /* First day(01) of n months before */
        AND LAST_DAY(CURRENT_DATE - n MONTHs)               /* Last day of n months before */

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are two examples.
    But, I'm not sure that those are your required queries.

    1) I assumed that you want to distinguish each month of each years.
    In other words, Jan 2010 and Jan 2011 are different group and summarize separately.

    2) It is not good idea to surround column names by double quotation marks.
    Joe Celko and others explained the reasons in here...
    comp.databases.ibm-db2 | Google Groups
    So, I will use column name year_month instead of "Month",
    and assume that the format of the column year_month is 'yyyy-mm'.


    Notes:
    1) Modify the predicates "xxx_MONTH = year_month" to meet the data type and format of columns xxx_MONTH.

    2) Used the characteristics:
    2-1) If ELSE-clause was ommited in a CASE expression, ELSE null woul be assumed.
    2-2) COUNT(expression) counts only non-null values.
    So,
    SUM(CASE WHEN <collumn> is not null AND <condition> THEN 1 ELSE 0 END)
    can be replaced by
    COUNT(CASE WHEN <condition> THEN <collumn> END)

    Example 1:
    Code:
    SELECT year_month
         , PRODUCTGROUP
         , COUNT(CASE WHEN ENTER_MONTH = year_month
           then 1
           END) AS ENTERED_RECORDS
         , COUNT(CASE WHEN AJUDICATED_MONTH = year_month and AJUD_CODE = 'A'
           then 1
           END) as APPROVED_RECORDS
         , COUNT(CASE WHEN AJUDICATED_MONTH = year_month and AJUD_CODE = 'R'
           then DT_AJUDICATED
           END) as DECLINED_RECORDS
         , COUNT(CASE WHEN AJUDICATED_MONTH = year_month
           then DT_BOOKED
           END) as ENTERED_BOOKED_RECORDS
         , COUNT(CASE WHEN BOOKED_MONTH = year_month 
           then DT_BOOKED
           END) as BOOKED_RECORDS
     FROM  TABLE1
         , (SELECT LEFT( CHAR(CURRENT_DATE - y YEARs - m MONTHs , ISO) , 7 ) AS year_month
             FROM  (VALUES 0,1,2,3,4,5,6,7,8,9)       AS y(y)
                 , (VALUES 0,1,2,3,4,5,6,7,8,9,10,11) AS m(m)
             WHERE CURRENT_DATE - y YEARs - m MONTHs >= DATE('2010-01-01')
           ) ym
     GROUP BY
           PRODUCTGROUP
         , year_month

    Example 2:
    Code:
    SELECT COALESCE(  en.year_month
                    , aj.year_month
                    , bk.year_month
                   )  AS year_month
         , COALESCE(  en.PRODUCTGROUP
                    , aj.PRODUCTGROUP
                    , bk.PRODUCTGROUP
                   )  AS PRODUCTGROUP
         , ENTERED_RECORDS
         , APPROVED_RECORDS
         , DECLINED_RECORDS
         , ENTERED_BOOKED_RECORDS
         , BOOKED_RECORDS
     FROM  (SELECT <convert ENTER_MONTH to 'yyyy-mm'> AS year_month
                 , PRODUCTGROUP
                 , COUNT(*) AS ENTERED_RECORDS
             WHERE ENTER_MONTH >= DATE('2010-01-01')
             GROUP BY
                   <convert ENTER_MONTH to 'yyyy-mm'>
                 , PRODUCTGROUP
           ) AS en
     FULL OUTER JOIN
           (SELECT <convert AJUDICATED_MONTH to 'yyyy-mm'>
                 , PRODUCTGROUP
                 , COUNT(CASE AJUD_CODE WHEN 'A' THEN 0 END
                        ) AS APPROVED_RECORDS
                 , COUNT(CASE AJUD_CODE WHEN 'R' THEN DT_AJUDICATED END
                        ) AS DECLINED_RECORDS
                 , COUNT(DT_BOOKED) AS ENTERED_BOOKED_RECORDS
             WHERE AJUDICATED_MONTH >= DATE('2010-01-01')
             GROUP BY
                   <convert AJUDICATED_MONTH to 'yyyy-mm'>
                 , PRODUCTGROUP
           ) AS aj
       ON  aj.year_month   = en.year_month
       AND aj.PRODUCTGROUP = en.PRODUCTGROUP
     FULL OUTER JOIN
           (SELECT <convert BOOKED_MONTH to 'yyyy-mm'>
                 , PRODUCTGROUP
                 , COUNT(DT_BOOKED) AS BOOKED_RECORDS
             WHERE BOOKED_MONTH >= DATE('2010-01-01')
             GROUP BY
                   <convert BOOKED_MONTH to 'yyyy-mm'>
                 , PRODUCTGROUP
           ) AS bk
       ON  bk.year_month   = COALESCE(aj.year_month   , en.year_month)
       AND bk.PRODUCTGROUP = COALESCE(aj.PRODUCTGROUP , en.PRODUCTGROUP)
    ;
    Last edited by tonkuma; 06-23-11 at 21:50. Reason: Add Notes 2)

Posting Permissions

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