Hi everyone
I'm trying to SUM the totals of the INSTALMENT, YEARLYVALUE, EST LIFETIMEVALUE, GIFTAID and TOTAL columns using the report below, but I can't seem to get it to work. I seem to require a multiplication within the SUM function, something which my version of SQL doesn't seem to allow.
Is there an easy way to sum these totals? I'm using an old version of SQL and cannot use some of the more recent totalling functions.
Many thanks
Jon
SELECT SERIALNUMBER,[FIRSTNAME],[SURNAME],[COUNTRY],
(CASE WHEN Row=100000 THEN SPACE(8) ELSE convert(varchar(8),[STARTDATE],3) END) [STARTDATE], [TYPE], [FREQUENCY], [INSTALMENTS], [INSTALMENT], [YEARLYVALUE], [EST LIFETIMEVALUE], [GIFTAID],[TOTAL],[DESTINATION],[MAILINGSEGMENT], [CLASSOF]
FROM
(
select ph.Serialnumber [SERIALNUMBER], co.firstname [FIRSTNAME],co.keyname [SURNAME],co.country [COUNTRY], ed.CLASSOF[CLASSOF], com.NOTES[MAILINGSEGMENT], convert(date,ph.STARTDATE) [STARTDATE],
ph.PAYMENTFREQUENCY [FREQUENCY], ph.INSTALMENTVALUE [INSTALMENT], ph.instalmentvalue12 [YEARLYVALUE], (ph.INSTALMENTVALUE)(count(pi.instalment)) [EST LIFETIMEVALUE],
count(pi.instalment) [INSTALMENTS],
ph.INSTALMENTVALUE [Amount],
ROUND((case when gd.GADStatus='active' AND ph.taxclaimable='Yes' then ph.INSTALMENTVALUE*count(pi.instalment)0.25 else 0 end),2) [GIFTAID],
ROUND((case when gd.GADStatus='active' AND ph.taxclaimable='Yes' then ph.INSTALMENTVALUEcount(pi.instalment)+(ph.INSTALMENTVALUE)*count(pi.instalment)*0.25 else 0 end),2) [TOTAL],
dc.DESTINATIONNOTES [DESTINATION], ph.PaymentType [TYPE],dprof.ccode,row_number() over (order by ph.STARTDATE desc) Row
from PLEDGEHEADER ph
left join contact co on ph.SERIALNUMBER=co.SERIALNUMBER
left join (SELECT serialnumber, (STUFF((SELECT CAST(', ' + parametername AS VARCHAR(MAX))
FROM contactparameter WHERE (serialnumber = contact.serialnumber and parametercategory='Constituent Code')
FOR XML PATH ('')), 1, 2, '')) AS ccode
FROM contact) dprof on ph.SERIALNUMBER=dprof.SERIALNUMBER
left join DESTINATIONCODE dc on ph.DESTINATIONCODE=dc.DESTINATIONCODE
left join GIFTAID_CURRENTDECLARATION gd on ph.SERIALNUMBER=gd.serialnumber
left join PLEDGEINSTALMENT pi on ph.PLEDGEID=pi.PLEDGEID
left join dbo.EDUCATION AS ED on ph.SERIALNUMBER = ed.SERIALNUMBER
left join COMMUNICATION com ON co.SERIALNUMBER = com.SERIALNUMBER
WHERE ed.INSTITUTIONTYPE='Location' AND com.SUBJECT = 'Income' AND dc.destinationtype LIKE 'London%'
and ph.INCOMETYPE='DONATION' and ph.STARTDATE>='01/04/19'
and ph.sourcecode IN ('APP00147', 'APP00149', 'APP00150', 'APP00151', 'APP00152', 'APP00153', 'APP00154', 'APP00155', 'APP00156', 'APP00157', 'APP00158', 'APP00159', 'APP00160', 'APP00161', 'APP00162', 'APP00163',
'APP00164', 'APP00165', 'APP00167', 'APP00168', 'APP00169', 'APP00170', 'APP00172')
GROUP BY ph.SERIALNUMBER, co.FIRSTNAME, co.KEYNAME, co.COUNTRY, ed.CLASSOF, com.NOTES, ph.STARTDATE, ph.PAYMENTFREQUENCY, ph.INSTALMENTVALUE, GADStatus, ph.TAXCLAIMABLE ,
dc.DESTINATIONNOTES , ph.PAYMENTTYPE , dprof.ccode
UNION
select 'TOTALS' serialnumber,' ' firstname,' ' surname,' ' country,' ' classof,' ' lifetime, ' ' frequency,' ' instalment,' ' instalments,' ' yearlyvalue,' ' mailingsegment,' 'startdate,SUM(ph.INSTALMENTVALUE) amount,
SUM(ROUND((case when gd.GADStatus='active' AND ph.taxclaimable='Yes' then ph.INSTALMENTVALUE*0.25 else 0 end),2)) [GIFTAID],
SUM(ph.instalmentvalue) [TOTES],'
' destinationnotes,' , 'type,' ' [Constituent Codes],100000 Row
from PLEDGEHEADER ph
left join contact co
on ph.SERIALNUMBER=co.SERIALNUMBER
left join
(SELECT serialnumber, (STUFF((SELECT CAST(', ' + parametername AS VARCHAR(MAX))
FROM contactparameter WHERE (serialnumber = contact.serialnumber and parametercategory='Constituent Code')
FOR XML PATH ('')), 1, 2, '')) AS ccode
FROM contact) dprof
on ph.SERIALNUMBER=dprof.SERIALNUMBER
left join DESTINATIONCODE dc on ph.DESTINATIONCODE=dc.DESTINATIONCODE
left join GIFTAID_CURRENTDECLARATION gd on ph.SERIALNUMBER=gd.serialnumber
left join PLEDGEINSTALMENT pi on ph.PLEDGEID=pi.PLEDGEID
left join dbo.EDUCATION AS ED on ph.SERIALNUMBER = ed.SERIALNUMBER
left join COMMUNICATION com ON co.SERIALNUMBER = com.SERIALNUMBER
WHERE ed.INSTITUTIONTYPE='Location' AND com.SUBJECT = 'Income' AND dc.destinationtype LIKE 'London%'
and ph.sourcecode IN ('APP00147', 'APP00149', 'APP00150', 'APP00151', 'APP00152', 'APP00153', 'APP00154', 'APP00155', 'APP00156', 'APP00157', 'APP00158', 'APP00159', 'APP00160', 'APP00161', 'APP00162', 'APP00163',
'APP00164', 'APP00165', 'APP00167', 'APP00168', 'APP00169', 'APP00170', 'APP00172')
) dtot
ORDER BY dtot.row