Last Month, Two Months ago, Three Months ago....

goldfishhh · #1 (**permalink**) 09-07-10, 11:08

I'm in the process of changing a query from one that selects static dates to one that is a bit more, well, dynamic.

Code:

   SELECT                                                                
        TS.TST_SUC_ID                        -- TST_CYC_SUC_TOT_ID,     
        , CASE WHEN STRT_DT_DT >= DATE('2009-02-01')                     
                AND STRT_DT_DT <= DATE('2009-02-28')                     
               THEN 1 ELSE 0 END             -- CNT_FEB_2009_NBR,        
        , CASE WHEN STRT_DT_DT >= DATE('2009-03-01')                     
                AND STRT_DT_DT <= DATE('2009-03-31')                     
               THEN 1 ELSE 0 END             -- CNT_MAR_2009_NBR,     
.
.
.

In the Microsoft world, you can use system variables like "LASTFULLMONTH" which obviously won't work here. I was thinking of using the system date and then trying dateadd() to get the range, but that again is a MSSQL function.

Can someone push me in the right direction for how to make a dynamic date for STRT_DT_DT range for "1 month ago" "2 months ago" 3 months ago" etc...

dbzTHEdinosaur · #2 (**permalink**) 09-07-10, 11:13

Graeme Birchall in his DB2 Cookbook suggests creating UserDefinedFunctions for this sort of thing.

n_i · #3 (**permalink**) 09-07-10, 11:29

Code:

C:\Temp>db2 values current date - day(current date) + 1 days - 1 month

1
-------------------
2010-08-01-11.23.27

  1 record(s) selected.


C:\Temp>db2 values current date - day(current date)

1
-------------------
2010-08-31-11.23.39

  1 record(s) selected.

Marcus_A · #4 (**permalink**) 09-07-10, 11:32

Last full month would be something like this:

Start of month: substr((date(STRT_DT_DT) - 1 month),1,7)||'-01'

End of month: date(substr(date(STRT_DT_DT),1,7)||'-01') - 1 day

goldfishhh · #5 (**permalink**) 09-07-10, 11:57

The CREATE FUNCTION works great.

Code:

CREATE FUNCTION year_month(inval DATE)
RETURNS INTEGER
RETURN (YEAR(inval) * 12) + MONTH(inval);

Now I can select last month, 2 months ago, three etc...

Code:

SELECT * FROM TOTDB01.PROD_ABEND                                
WHERE YEAR_MONTH(DT_DT) = I266217.YEAR_MONTH(CURRENT DATE) - 1;

Although, I may opt with this method. Since the SQL queries will be executed within a program, I don't want to rely upon my ID for the function. Hmm.

Start of month: substr((date(STRT_DT_DT) - 1 month),1,7)||'-01'

End of month: date(substr(date(STRT_DT_DT),1,7)||'-01') - 1 day

ARWinner · #6 (**permalink**) 09-07-10, 13:33

Quote:

Originally Posted by goldfishhh

Code:

SELECT * FROM TOTDB01.PROD_ABEND                                
WHERE YEAR_MONTH(DT_DT) = I266217.YEAR_MONTH(CURRENT DATE) - 1;

You really do not want to do it this way. Putting the column that is checked in a UDF before the check usually causes a table scan, even if you have an index on the column.

Andy

Lenny77 · #7 (**permalink**) 09-07-10, 15:57

Did you try to do like this:

Code:

select month(current date - 77 month)
from sysibm.sysdummy1

Returns: 4

Lenny

goldfishhh · #8 (**permalink**) 09-07-10, 16:02

I guess I need to figure something else out. When I created a UDF, then modified my SQL code and then had our scheduling system execute the SQL code, I got a permission denied saying that the scheduling ID couldn't execute.

So. maybe I need to look at sysibm.sysdummy1 date field and then compare it to the DT_DT fields

D-

Quote:

Originally Posted by ARWinner

You really do not want to do it this way. Putting the column that is checked in a UDF before the check usually causes a table scan, even if you have an index on the column.

Andy

goldfishhh · #9 (**permalink**) 09-08-10, 10:58

I think this will do it....

Code:

         , CASE WHEN (INT(YEAR(STRT_DT_DT))*12+(int(MONTH(STRT_DT_DT)))) = 
         (
         SELECT (INT(YEAR(CURRENT DATE))*12+
         (INT(MONTH(CURRENT DATE)) -18)
         FROM SYSIBM.SYSDUMMY1
         )
                THEN 1 ELSE 0 END             -- CNT_JUL_2009_NBR,
.
.
.
.

Thoughts?

Quote:

Originally Posted by goldfishhh

I guess I need to figure something else out. When I created a UDF, then modified my SQL code and then had our scheduling system execute the SQL code, I got a permission denied saying that the scheduling ID couldn't execute.

So. maybe I need to look at sysibm.sysdummy1 date field and then compare it to the DT_DT fields

D-

Lenny77 · #10 (**permalink**) 09-08-10, 11:54

Quote:

Originally Posted by goldfishhh

I think this will do it....

Code:

         , CASE WHEN (INT(YEAR(STRT_DT_DT))*12+(int(MONTH(STRT_DT_DT)))) = 
         (
         SELECT (INT(YEAR(CURRENT DATE))*12+
         (INT(MONTH(CURRENT DATE)) -18)
         FROM SYSIBM.SYSDUMMY1
         )
                THEN 1 ELSE 0 END             -- CNT_JUL_2009_NBR,
.
.
.
.

Thoughts?

You don't need to use int(MONTH because month return INT by default.

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech