Looping through dates

kurtangle13 · #1 (**permalink**) 06-14-11, 10:30

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 ):

n_i · #2 (**permalink**) 06-14-11, 10:33

Consider the MERGE statement.

kurtangle13 · #3 (**permalink**) 06-14-11, 10:41

Quote:

Originally Posted by n_i

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 ( :

tonkuma · #4 (**permalink**) 06-14-11, 18:22

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 */

tonkuma · #5 (**permalink**) 06-23-11, 08:09

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)
;

Internet Services

Web Development

Digital Marketing

Consumer Tech