Difference between rows in 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: If @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-200)

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.

 

[RiverGuy]

Here's a working example based on what I think you're looking for.

DECLARE @t1 TABLE (EffectiveDate DATETIME, Amount DECIMAL(18,4))

INSERT INTO @t1 SELECT '08/31/2008'              ,400
INSERT INTO @t1 SELECT '09/30/2008'              ,350
INSERT INTO @t1 SELECT '10/31/2008'              ,200  
INSERT INTO @t1 SELECT '11/30/2008'              ,500
INSERT INTO @t1 SELECT '12/31/2008'              ,100

DECLARE @Month DATETIME
SELECT @Month = '20081130'

SELECT Amount,
Amount - ISNULL(PreviousMonth, 0) AS [Month],
Amount - ISNULL(PreviousQuarter, 0) AS [Quarter]
FROM
	(SELECT a.Amount,
		(SELECT TOP 1 Amount FROM @t1 WHERE EffectiveDate < a.EffectiveDate ORDER BY EffectiveDate DESC) AS PreviousMonth,
		(SELECT TOP 1 Amount FROM @t1 WHERE EffectiveDate < DATEADD(month, -1, a.EffectiveDate) ORDER BY EffectiveDate DESC) AS PreviousQuarter
	FROM @t1 a WHERE a.EffectiveDate = @Month
) x

 

[bborissov]

Are you ALWAYS want the value from the LAST DAY of the desired month?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RiverGuy]

I took this to mean the table is aggregated by month, and the month is stored by the last day. If you've got records for other days in there, then you need another solution. In that case, a date table would come in very handy.