By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Difference between rows in a table

P: 5
Hello All,
Please help me with the SQL Query.
The table structure is:

EffectiveDate Amount
08/31/2008 400
09/30/2008 350
10/31/2008 200
11/30/2008 500
12/31/2008 100

I want to find the difference between any two rows provided the effective date.
For example: @Month=11/30/3008 then i want the output of the stored procedure to be like
Year Month Quarter
500 300 150


i.e for year it has to display the Amount for the @Month(11/30/2008)
for month it has to display the difference of Amount for 11/30/2008 and 10/31/2008 (500-300)
for Quarter it has to display the difference of Amount for 11/30/2008 and 09/30/2008. (500-350)
Can somebody help me with the logic for this.
Nov 18 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Looks like you're getting the difference between specific time periods. Did you just display 500 for 11/30/2008 because there are no other records? What if you run your application on 11/30/2009?

-- CK
Nov 18 '08 #2

P: 5
The month is a parameter. It can be any date. The corresponding amount will be stored in the table.

I need the amount for the month (selected by user for example 11/30/2008),
diffrence between the amount for 11/30/2008 and 10/31/2008(amount month-amount previous month)
difference between the amount for 11/30/2008 and 09/30/2008 (amount month-amount of the quarter)
Nov 18 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Try this sample code:

Expand|Select|Wrap|Line Numbers
  1. declare @tablename table (EffectiveDate smalldatetime, Amount money)
  2.  
  3. insert into @tablename values('08/31/2008', 400)
  4. insert into @tablename values('09/30/2008', 350)
  5. insert into @tablename values('10/31/2008', 200)
  6. insert into @tablename values('11/30/2008', 500)
  7. insert into @tablename values('12/31/2008', 100)
  8.  
  9.  
  10. declare @inputdate as smalldatetime
  11.  
  12. set @inputdate = '11/30/2008'
  13.  
  14. select * from @tablename
  15.  
  16. select @inputdate as input, 
  17. year_value = Amount, 
  18. month_value = (select t1.Amount - t2.amount from @tablename t2 where DATEDIFF(MONTH, t2.EffectiveDate, @inputdate) = 1),
  19. quarter_value = (select t1.Amount - t2.amount from @tablename t2 where DATEDIFF(MONTH, t2.EffectiveDate, @inputdate) = 2)
  20. from @tablename t1
  21. where  @inputdate = EffectiveDate
  22.  
One catch, there should be only one row per month.

This will be your resultset:

Expand|Select|Wrap|Line Numbers
  1. EffectiveDate           Amount
  2. ----------------------- ---------------------
  3. 2008-08-31 00:00:00     400.00
  4. 2008-09-30 00:00:00     350.00
  5. 2008-10-31 00:00:00     200.00
  6. 2008-11-30 00:00:00     500.00
  7. 2008-12-31 00:00:00     100.00
  8.  
  9. input                   year_value            month_value           quarter_value
  10. ----------------------- --------------------- --------------------- ---------------------
  11. 2008-11-30 00:00:00     500.00                300.00                150.00
  12.  

-- CK
Nov 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.