Ndtyjky

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