November 15, 2010 at 12:56 pm
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:
studentidyearyearavgpmtamtavgpmtamtchange
320092007423.5400002342.0000000.819154
320102007564.0000002342.0000000.759180
3200720092342.000000423.540000-4.529583
320102009564.000000423.540000-0.331633
3200720102342.000000564.000000-3.152482
320092010423.540000564.0000000.249042
2200920083544.000000456.780000-6.758658
220082009456.7800003544.0000000.871111
120082007654.0000007895.0000000.917162
120092007135.0000007895.0000000.982900
120102007427.0000007895.0000000.945915
1200720087895.000000654.000000-11.071865
120092008135.000000654.0000000.793577
120102008427.000000654.0000000.347094
1200720097895.000000135.000000-57.481481
120082009654.000000135.000000-3.844444
120102009427.000000135.000000-2.162962
1200720107895.000000427.000000-17.489461
120082010654.000000427.000000-0.531615
120092010135.000000427.0000000.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!!! 🙂
November 15, 2010 at 1:55 pm
Well, since you showed your work this will be easy to help get you back on track.
T2512 (11/15/2010)
selectA.studentid
, A.[year] ,
b.[year],
a.avgpmtamt,
B.avgpmtamt
, (B.avgpmtamt-A.avgpmtamt)/ B.avgpmtamt as change
This is a percentage of the delta. Are you looking for a percentage of the change? Just confirming that part. If so, you want A.avgPmtAmt, not B.AvgPmtAmt, most likely.
Here's where things have started to go horribly wrong:
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
In particular, right here. So close but not quite. You're getting a nearly complete Cross Join.
on A.studentid = B.studentid and a.[year]<>B.[year]
Now, at a guess you were trying to do this to get groupings. This makes sense, but there's an easier way. Use a compound join instead of the grouping. While group by-AVG() with only a single record will give you the avg / 1, it's overkill. (Please note, optimizationally this is horrendous. I have a feeling you're not going to care about optimization here, just success)
Picture this:
students AS s1
JOIN
students AS s2
ON s1.studentID = s2.studentID --This was correct, right idea...
YEAR( s1.paymentDate) = YEAR( s2.PaymentDate) + 1 -- Note, you CAN join on a calculation. It's not pretty though.
Now, you'll have to adjust that for students who didn't attend in the prior year, but this should help get you back in the right direction.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 2:04 pm
This looks like homework, but that's okay, you've put some real effort in here. Couple of pointers:
If a student only pays once in one year, then you don't need all those derived tables with their GROUP BYs.
Do you need an output which covers all students / all years (potentially - you specified a pair of years)? The upside of this kind of crossjoined matrix is that you can easily see where a student is missing a year. The downside is it's more expensive. Here's a starter:
SELECT SAll.studentid, s9.pmtamt - s8.pmtamt
FROM (SELECT studentid FROM [dbo].[students] GROUP BY studentid) SAll
LEFT JOIN [dbo].[students] S9 ON S9.studentid = SAll.studentid AND YEAR(s9.paymentdate) = 2009
LEFT JOIN [dbo].[students] S8 ON S8.studentid = SAll.studentid AND YEAR(s8.paymentdate) = 2008
You can put all sorts of stuff in the WHERE clause to filter rows, and you'd also benefit from some NULL processing in the calculated result in the output.
TBH there are loads of ways of writing this. Here's another:
SELECT studentid, pmtamt2009 - pmtamt2008
FROM (
SELECT studentid,
pmtamt2009 = SUM(CASE WHEN YEAR(paymentdate) = 2009 THEN pmtamt ELSE 0 END),
pmtamt2008 = SUM(CASE WHEN YEAR(paymentdate) = 2008 THEN pmtamt ELSE 0 END)
FROM [dbo].[students]
GROUP BY studentid
) d
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply