Calculate delta difference in the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate delta difference in the same table

Author

Topic

tammy2512
Starting Member

8 Posts

Posted - 2010-11-15 : 15:04:20

Hello,

The following is the table creation and records insertion script

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[students] (
[studentid] [int] NULL,
[paymentdate] [date] NULL,
[pmtamt] [decimal](20, 2) NULL
) ON [PRIMARY]

GO

INSERT INTO [students] ([studentid] ,[paymentdate] ,[pmtamt])
VALUES
('1','2009-06-03','135.00'),
('2','2008-03-02','456.78'),
( '1','2008-06-03','654.00'),
('1', '2007-04-08','7895.00'),
('1','2010-06-03','427.00'),
('2','2009-07-08','3544.00'),
('3','2010-07-08','564.00'),
('3','2009-06-08','423.54'),
('3','2007-03-08','2342.00')

I need to find out the delta difference in payments between different years for each student.

I have been working on the following code

select A.studentid
, A.[year] , b.[year],
a.avgpmtamt,
B.avgpmtamt
, (B.avgpmtamt-A.avgpmtamt)/ B.avgpmtamt as change
from
( select studentid, year(paymentdate) as [year],avg(pmtamt) as avgpmtamt
from students
group by studentid,year(paymentdate)
) as A
join
( select studentid, year(paymentdate) as [year],avg(pmtamt) as avgpmtamt
from students
group by studentid,year(paymentdate)
) as B
on A.studentid = B.studentid and a.[year]<>B.[year]
order by studentid desc

But there seems to some problem with my code as I am getting the following result:

studentid year year avgpmtamt avgpmtamt change
3 2009 2007 423.540000 2342.000000 0.819154
3 2010 2007 564.000000 2342.000000 0.759180
3 2007 2009 2342.000000 423.540000 -4.529583
3 2010 2009 564.000000 423.540000 -0.331633
3 2007 2010 2342.000000 564.000000 -3.152482
3 2009 2010 423.540000 564.000000 0.249042
2 2009 2008 3544.000000 456.780000 -6.758658
2 2008 2009 456.780000 3544.000000 0.871111
1 2008 2007 654.000000 7895.000000 0.917162
1 2009 2007 135.000000 7895.000000 0.982900
1 2010 2007 427.000000 7895.000000 0.945915
1 2007 2008 7895.000000 654.000000 -11.071865
1 2009 2008 135.000000 654.000000 0.793577
1 2010 2008 427.000000 654.000000 0.347094
1 2007 2009 7895.000000 135.000000 -57.481481
1 2008 2009 654.000000 135.000000 -3.844444
1 2010 2009 427.000000 135.000000 -2.162962
1 2007 2010 7895.000000 427.000000 -17.489461
1 2008 2010 654.000000 427.000000 -0.531615
1 2009 2010 135.000000 427.000000 0.683840

For example if you take student id 2 it is calculating twice for 2009 and 2008 and then again vice versa; and its doing the same this for the other student ids also
Could you please let me know where did i go wrong; because i need the code to calculate the difference just once from 2009 to 2008.

Thank you!!! :)

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-15 : 15:38:44

DECLARE
@t TABLE
(
StudentID int NULL
, payyear int NULL
, avgAmt decimal(20, 2) NULL
, rID int NULL
)
;

INSERT INTO @t
(
StudentID
, payYear
, AvgAmt
, rID
)
SELECT
StudentID
, year(paymentdate)
, avg(pmtamt)
, ROW_NUMBER() OVER(PARTITION BY StudentID ORDER BY StudentID) AS rowNum
FROM
students
GROUP BY
studentid
, year(paymentdate)
;

SELECT
t1.StudentID
, t1.PayYear AS Year1
, t2.PayYear AS Year2
, t1.avgAmt
, t2.AvgAmt
, (t1.AvgAmt - t2.AvgAmt) AS Variance
, ROW_NUMBER() OVER(ORDER BY t1.StudentID)
FROM
(
SELECT
s.StudentID
, s.PayYear
, s.AvgAmt
, s.rID
FROM
@t AS s
) AS t1
LEFT OUTER JOIN
(
SELECT
s.StudentID
, s.PayYear
, s.AvgAmt
, s.rID
FROM
@t AS s
) AS t2
ON t1.StudentID = t2.StudentID
AND t2.rID = t1.rID - 1
;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-15 : 19:56:28


select A.studentid
, A.[year] , b.[year],
a.avgpmtamt,
B.avgpmtamt
, (B.avgpmtamt-A.avgpmtamt)/ B.avgpmtamt as change
from
( select studentid, year(paymentdate) as [year],avg(pmtamt) as avgpmtamt
from students
group by studentid,year(paymentdate)
) as A
join
( select studentid, year(paymentdate) as [year],avg(pmtamt) as avgpmtamt
from students
group by studentid,year(paymentdate)
) as B
on A.studentid = B.studentid and a.[year]  < B.[year]
order by studentid desc

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources