Problem with Top 20 and also adding two columns together

Transact-SQL
1

Hi everyone

I'm having real problems with the following SQL script. I'm trying to achieve the Top 20 values primarily by each Academic Year then by descending monetary value.

I'm also trying to add the bd.paymentamount and Gift Aid Total columns together to make a grand total column. However, I get an error when I try to do that because both columns are different formats.

Any help that you could provide would be much appreciated!

Many thanks
Jon

SELECT [Outer].SerialNumber [Serial Number]
,STUFF((SELECT ', ' + PARAMETERNAME FROM CONTACTPARAMETER AS [Inner] WHERE PARAMETERCATEGORY='Constituent Code' AND [Inner].SerialNumber = [Outer].SerialNumber FOR XML PATH('')),1,2,'') AS [Type],
co.contacttype [Record Type], co.firstname [Forename], co.keyname [Surname], bd.incometype [Income Type], bd.paymentamount [Amount],

	CASE
	WHEN (gd.GADstatus)='Active' THEN bd.paymentamount*0.25 
	ELSE '0'
	END AS [Gift Aid Total],

	bd.dateofpayment [Payment Date], 

	CASE
	WHEN (bd.dateofpayment) BETWEEN '2019-09-01' AND '2020-08-31' THEN '2019/20'
	WHEN (bd.dateofpayment) BETWEEN '2018-09-01' AND '2019-08-31' THEN '2018/19'
	WHEN (bd.dateofpayment) BETWEEN '2017-09-01' AND '2018-08-31' THEN '2017/18'
	WHEN (bd.dateofpayment) BETWEEN '2016-09-01' AND '2017-08-31' THEN '2016/17'
	ELSE NULL
	END AS [Academic Year],
	dc.destinationtype [School], dc.destinationgroup [Fund Type]

FROM CONTACTPARAMETER AS [Outer]

inner join contact co on co.serialnumber=[Outer].serialnumber
left join batchdetail bd on co.serialnumber=bd.serialnumber
left join destinationcode dc on bd.destinationcode=dc.destinationcode
left join GIFTAID_CURRENTDECLARATION AS gd ON bd.SERIALNUMBER=gd.SERIALNUMBER

where
bd.DATEOFPAYMENT BETWEEN '2016-09-01' AND '2017-08-31'

GROUP BY bd.paymentamount, [Outer].SerialNumber, co.contacttype, co.firstname, co.keyname, bd.incometype, gd.GADstatus, bd.dateofpayment, dc.destinationtype, dc.destinationgroup
ORDER BY bd.paymentamount DESC, [Outer].SerialNumber, co.contacttype, co.firstname, co.keyname, bd.incometype, gd.GADstatus, bd.dateofpayment, dc.destinationtype, dc.destinationgroup

2

Well, for one - don't use quotes on 0 for this:

CASE
WHEN (gd.GADstatus)='Active' THEN bd.paymentamount*0.25
ELSE 0
END AS [Gift Aid Total],

You are by default casting the column as varchar because of the quotes on the zero. You want to just return 0 so it is a number like the bd.paymentamount*0.25.

As for getting the top 20 for each year you can add this to the query
SELECT *,
Row_number() OVER(PartitionBy [Academic Year] ORDER BY [Academic Year] DESC) RN
FROM
(
your original query here between the parens
)A

And then outside of that
SELECT *
FROM (
All of the previous stuff here
)B
WHERE B.RN <20

You could probably write it so you don't have so much query within query going on but I don't have time to analyze it to do so. But I use the Partition stuff all of the time.