Difference between rows in a table

[lavadan]

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.

[ck9663]

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

[lavadan]

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)

[ck9663]

Try this sample code:

declare @tablename table (EffectiveDate smalldatetime, Amount money)
 
insert into @tablename values('08/31/2008', 400)
insert into @tablename values('09/30/2008', 350)
insert into @tablename values('10/31/2008', 200)
insert into @tablename values('11/30/2008', 500)
insert into @tablename values('12/31/2008', 100)
 
 
declare @inputdate as smalldatetime
 
set @inputdate = '11/30/2008'
 
select * from @tablename
 
select @inputdate as input, 
year_value = Amount, 
month_value = (select t1.Amount - t2.amount from @tablename t2 where DATEDIFF(MONTH, t2.EffectiveDate, @inputdate) = 1),
quarter_value = (select t1.Amount - t2.amount from @tablename t2 where DATEDIFF(MONTH, t2.EffectiveDate, @inputdate) = 2)
from @tablename t1
where  @inputdate = EffectiveDate
 

One catch, there should be only one row per month.

This will be your resultset:

EffectiveDate           Amount
----------------------- ---------------------
2008-08-31 00:00:00     400.00
2008-09-30 00:00:00     350.00
2008-10-31 00:00:00     200.00
2008-11-30 00:00:00     500.00
2008-12-31 00:00:00     100.00
 
input                   year_value            month_value           quarter_value
----------------------- --------------------- --------------------- ---------------------
2008-11-30 00:00:00     500.00                300.00                150.00
 

-- CK