Month end data from a daily table

General

Month end data from a daily table

Hello all,

Could anyone please help me to come up with a query which extracts a record for every month end date, if there is a change in the data. Examples are given below:

Scenario 1:

Name  Balance Validity-Start date   Validity-End date

A        10          01/01/2015              02/01/2015

A        20          03/01/2015              04/01/2015

A        30          05/01/2015              02/02/2015

A        40          03/02/2015              05/02/2015

A        50          06/02/2015              27/02/2015

A        60          28/02/2015              Unknown..

Expected Output:

Name Balance Validity-Start date Validity-End date

A        30          05/01/2015           31/01/2015 (For Jan month)

A        60          28/02/2015           28/02/2015 (For Feb month)

Scenario 2:

Name Balance Validity-Start date Validity-End date

B       10          01/01/2014            03/09/2014

B       20          03/09/2014            31/12/2014

Expected Output:

Name Balance Validity

B       10           01/01/2014           31/08/2014

B       20           03/09/2014           31/12/2014

Thank you

2 REPLIES
Senior Apprentice

Re: Month end data from a daily table

This might be what you want, if there are no overlapping ranges:

SELECT ...
CASE
-- already last day of month
WHEN Validity_end_date = LAST_DAY(Validity_end_date) THEN Validity_end_date
-- last day of starting date's month
WHEN Validity_end_date IS NULL THEN LAST_DAY(Validity_start_date)
-- last day of previous month
ELSE Validity_end_date - EXTRACT(DAY FROM Validity_end_date)
END
...
WHERE Validity_end_date >= LAST_DAY(Validity_start_date)
OR Validity_end_date IS NULL

Re: Month end data from a daily table

Hi Dieter

It helped me a very lot. Thank you indeed!!