how do produce counts for every month around a reference date

Question

I am using DB2 SQL, where I have a reference date - and would like to count distinct individuals that for each monthdiff either side (increasing up to the START_DT/END_DT) of the REF_DT - return a row, with the month counter.

My base data looks like:

ID   REF_DT       START_DT     END_DT
--   ------       --------     ------
1    2000-01-01   1998-01-01   2002-01-01
2    2001-06-01   2001-06-01   2003-06-01
3    2003-01-01   1998-01-01   2005-06-01
4    2002-05-01   2003-01-01   2005-01-01

so taking the first row, I return a row for each month around the REF_DT that exists between START_DT and END_DT and return the value of the monthdiff. The first row in my base table would generate:

ID   REF_DT       START_DT     END_DT      MONTHDIFF
--   ------       --------     ------      --------- 
1    2000-01-01   1998-01-01   2002-01-01  -24
1    2000-01-01   1998-01-01   2002-01-01  -23
.
.
.
1    2000-01-01   1998-01-01   2002-01-01  24

i.e. would produce 48 rows as the START_DT and END_DT are 24 months either side of the REF_DT

I could to this query by query i.e.

select distinct id, ref_dt,start_dt,end_dt, 
case when ref_dt - 1 month between start_dt and end_dt then -1 end as monthdiff

however I would have to manually create 1 row per query. I wish to embed this logic in something a bit more clever than that.

Thanks.

SOLVED

see Juan's suggestion for the answer. The idea to create a table for just the month reference worked. I created this table by looking at the following stack thread generate_series() equivalent in DB2

`REF_DT`, `START_DT` and `END_DT` is the same for all `MONTHDIFF` in your desire output? — Juan Carlos Oropeza, Oct 26 '15 at 12:24
@Juan - not they can be different for each person. However I don't mind specifying fixed boundaries before hand, for example 5 years either side as I know this will capture all of my data — brucezepplin, Oct 26 '15 at 12:50
I mean in your output result. 4 first column are exact the same — Juan Carlos Oropeza, Oct 26 '15 at 12:51

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

Easy way is you create a month table. 100 years will need 1200 rows

allMonths

 month_id
 date

How can I get a table of dates from the first day of the month, two months ago, to yesterday?

SELECT 
      YT.id, 
      YT.ref_dt, 
      YT.start_dt, 
      YT.end_dt, 
      MONTHS_BETWEEN(YT.ref_dt, allDates.date) as MONTHDIFF
FROM YourTable YT
INNER JOIN allDates
        ON allDates BETWEEEN YT.START_DT AND YT.END_DT

edited May 23 '17 at 12:14

Community

1
1

answered Oct 26 '15 at 12:40

Juan Carlos Oropeza

47,252
12
78
118

thanks this works - generating that table of numbers for the month reference was the key – brucezepplin Oct 26 '15 at 15:00

how do produce counts for every month around a reference date

1 Answers1