CODEWITHSUNDEEP
sqldb2
brucezepplin
brucezepplin

Reputation: 9752

how do produce counts for every month around a reference date

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

Upvotes: 0

Views: 64

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Upvotes: 1

Related Questions