how to calculate previous years sum and compare it to the current
Hi,
I need to create a report which compares a time interval sum against the same time interval at previous year.
For example , if user chooses 01.feb.2012 and 15.feb.2012 as the begin and end date as parameters, the report will first calculates the sum between 01.feb.2012 and 15.feb.2012 , then it calculates the sum between 01.feb.2011 and 15.feb.2012 and then calculates the difference between the two sums. How can i do this by using sql? ( and the users who run this report , can enter whatever date she wants. she can enter form example 01.feb.2012 and 05.june 2013, then the report will compare 01.feb.2012-05.june.2013 to 01.feb2011 to 05.june.2012)
My sample table is :
date_of_transaction , transaction_id , company_id , amount_of_sales
01.feb.2011 1 A 100
02.feb.2011 2 A 200
04.FEB.2012 3 A 300
05.FEB.2012 4 A 400
for the parameters 01.feb.2012- 15.feb.2012 the output will be as shown below:
company_id , total_sales , total_sales_of_previous_year , diiference
A , 700 , 300 , 400
I tried lag or window functions but i didn't solve the problem.
Thanks for any help.