All Forums General
Revathich 3 posts Joined 09/14
16 Mar 2015
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

dnoeth 4628 posts Joined 11/04
17 Mar 2015

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

 

Dieter

Revathich 3 posts Joined 09/14
19 Mar 2015

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

You must sign in to leave a comment.