Myujth

I am trying to rewrite a query that has a product join to expand records across a date period with the EXPAND ON function. Here is how it works currently.

SELECT 
 t1.key
 ,MIN(t1.beg_dt)
 ,MAX(t1.end_dt)
 ,dates.end_month_dt
FROM t1
INNER JOIN dates
 ON t1.beg_dt < dates.end_month_dt
 AND t1.end_dt >= dates.end_month_dt
GROUP BY 1,4;

The dates table is just a list of a bunch of date information. end_month_dt is a column that has every end of month date going back several years. (e.g. 1/31/13, 2/28/31, 3/31/13, etc...)

If a record in t1 has beg_dt = 1/1/13 and end_dt = 8/31/13, then this join will create a record for each month in this interval. Like this:

key beg_dt end_dt end_month_dt
1 1/1/13 8/31/13 1/31/13
1 1/1/13 8/31/13 2/28/13
...
1 1/1/13 8/31/13 8/31/13

This query runs poorly and must be split up into a few parts as it contains much more information from t1 and spans several years. I have found that EXPAND ON can yield a very similar result, but I am having a minor issue getting the last month to be included (or the first month). Here is what I have:

SELECT
 t1.key
 ,t1.beg_dt
 ,t1.end_dt
 ,BEGIN(prd) as end_month_dt
FROM t1
EXPAND ON PERIOD(t1.beg_dt, t1.end_dt) AS prd BY ANCHOR MONTH_END;

This yields identical results except that it doesn't include the last row. If I were to use END(prd), it would exclude the first row. The issue seems to be that prd = (1/1/13, 8/31/13) and when expanded returns

prd
(1/31/13, 2/28/13)
(2/28/13, 3/31/13)
...
(7/31/13, 8/31/13)

What I really need is the t1 info for all of the dates on the left hand side of each period (1/31 - 7/31) and then the last date on the right hand side (8/31). Is there another way that I can write the EXPAND ON such that I can get that result?

Edit:
I've found that adjusting the beg_dt and end_dt allows me to get the same results although with more functions than I'd usually like to include. It still seems to run and is still much faster than the original.

SELECT
 t1.key
 ,t1.beg_dt
 ,t1.end_dt
 ,BEGIN(prd) as end_month_dt
FROM t1
EXPAND ON PERIOD(
 CASE 
 WHEN beg_dt = Last_Day(beg_dt) THEN beg_dt + 1 
 ELSE beg_dt 
 END
 ,CASE 
 WHEN end_dt = '9999-12-31' THEN end_dt 
 ELSE end_dt + 1 
 END) AS prd BY ANCHOR Month_End

Use the begin not the end of the month as anchor and then extract the last included date:

SELECT
 t1.key
 ,t1.beg_dt
 ,t1.end_dt
 ,Last(prd) as end_month_dt -- last included day = last day of the month
FROM t1
EXPAND ON PERIOD(t1.beg_dt, t1.end_dt) AS prd BY ANCHOR PERIOD MONTH_BEGIN;

ANCHOR PERIOD is used to include the first month if beg_dt is not the first of the month.

key beg_dt end_dt end_month_dt -- returned period 
1 2013-01-31 2013-08-31 2013-01-31 -- (2013-01-01, 2013-02-01)
1 2013-01-31 2013-08-31 2013-02-28 -- (2013-02-01, 2013-03-01)
1 2013-01-31 2013-08-31 2013-03-31 -- (2013-03-01, 2013-04-01)
1 2013-01-31 2013-08-31 2013-04-30 -- (2013-04-01, 2013-05-01)
1 2013-01-31 2013-08-31 2013-05-31 -- (2013-05-01, 2013-06-01)
1 2013-01-31 2013-08-31 2013-06-30 -- (2013-06-01, 2013-07-01)
1 2013-01-31 2013-08-31 2013-07-31 -- (2013-07-01, 2013-08-01)
1 2013-01-31 2013-08-31 2013-08-31 -- (2013-08-01, 2013-09-01)

Edit:

This should return the same result as your edited query (beg_date is excluded, but end_dt included while periods include the start and exclude the end):

SELECT
 t1.KY
 ,t1.beg_dt
 ,t1.end_dt
 ,Begin(prd) AS end_month_dt
FROM t1
EXPAND ON PERIOD(beg_dt, end_dt) + INTERVAL '1' DAY AS prd BY ANCHOR Month_End