SQLTeam.com | Weblogs | Forums

Problem with Top 20 and also adding two columns together

#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