I have a table where I'm storing a row for each transaction that records the type of transaction along with the amount of money being recorded for the transaction type. I then need to write a report to sum the amounts of various types.
At the moment I'm doing this with a series of CTE's, but I'm sure there's a better way:
;with cte_foodCard as (
select isnull(sum(cast(o.FinancialContribution as money)), 0) as 'FoodCardContributions'
from dbo.CriticalAssistanceInternalOutcomes as o
where upper(o.PaymentType) = 'FOOD CARD'
), cte_fuelCard as
(
select isnull(sum(cast(o.FinancialContribution as money)), 0) as 'FuelCardContributions'
from dbo.CriticalAssistanceInternalOutcomes as o
where upper(o.PaymentType) = 'FUEL CARD'
), cte_gocard as
(
select isnull(sum(cast(o.FinancialContribution as money)), 0) as 'GoCardContributions'
from dbo.CriticalAssistanceInternalOutcomes as o
where upper(o.PaymentType) in ( 'GO CARD' , 'GOCARD')
), cte_invoicePayment as
(
select isnull(sum(cast(o.FinancialContribution as money)), 0) as 'InvoicePaymentContributions'
from dbo.CriticalAssistanceInternalOutcomes as o
where upper(o.PaymentType) = 'INVOICE PAYMENT'
)
select *
from cte_foodCard , cte_fuelCard, cte_gocard, cte_invoicePayment
For more context, a sampling of the data from development (needs better rules and cleanup) looks like:
select o.Id ,
o.FinancialContribution ,
o.PaymentType
from dbo.CriticalAssistanceInternalOutcomes as o