CODEWITHSUNDEEP
oracle-database
Koles
Koles

Reputation: 47

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

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.

Upvotes: 0

Views: 322

Answers (1)

mikron
mikron

Reputation: 683

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.

Upvotes: 0

Related Questions