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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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

Difference between rows in a table

re: Difference between rows in a table

re: Difference between rows in a table

re: Difference between rows in a table

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about