Previous month difference and last year-end difference in oracle sql

Question

i need to write a query that will calculate difference between last month-end and month-end and difference between last year-end and month-end. I created sample database in sqlfiddle http://sqlfiddle.com/#!4/b9749 In my database the most important date is always the month-end but as you can see in the sample there there are other dates as well but i can't use values from these dates. When i run this query with condidtion that date ='2014-04-30' the result should be like this:

date        product    amount     last_month_diff     last_year_end_diff
2014-04-30       a1       350                -150                    650
2014-04-30       b1       123                -123                   1877

when i run this query with condidtion that date ='2014-05-31' the result should be like this

date        product    amount     last_month_diff     last_year_end_diff
2014-05-31       a1       400                 -50                    600
2014-05-31       b1       500                -377                   1500
2014-05-31       c1       200                   0                      0

and when i run this query with condidtion that date ='2014-06-30' the result should be like this

date        product    amount     last_month_diff     last_year_end_diff
2014-06-30       b1       780                -280                   1220
2014-06-30       c1       100                 100                      0

At first i thought i use analytical functions (lag) but i may have many dates between two month-ends and i don't know how to achieve the expected result.

mikron mikron · Accepted Answer · 2014-11-26T12:10:21

Try something like the bellow.

with input_date as (select to_date('2014-04-30', 'YYYY-MM-DD') d from dual),
sot_tot as (select product, 
                   sum(case when extract(month from date_) = extract(month from d) then amount else 0end) amount,       
                   sum(case when extract(month from date_) = extract(month from last_day(add_months(d, -1))) then amount else 0 end) previous_month_amount
            from sot, input_date
            where date_ <= d
            group by product)

select product, amount, previous_month_amount - amount as previos_month_diff
  from sot_tot

I was not able understand what you mean by difference between last month-end and month-end and difference between last year-end and month-end. However the solution will be very similar to the one above and you can play with the sum and case combination to achieve the result you want.

Previous month difference and last year-end difference in oracle sql

1 Answers