Aggregate payments per year per customer per type

Question

Please consider the following payment data:

customerID    paymentID    pamentType    paymentDate    paymentAmount
---------------------------------------------------------------------
     1            1            A         2015-11-28          500
     1            2            A         2015-11-29         -150
     1            3            B         2016-03-07          300
     2            4            A         2015-03-03          200
     2            5            B         2016-05-25         -100
     2            6            C         2016-06-24          700
     1            7            B         2015-09-22          110
     2            8            B         2016-01-03          400

I need to tally per year, per customer, the sum of the diverse payment types (A = invoice, B = credit note, etc), as follows:

year    customerID    paymentType    paymentSum
-----------------------------------------------
2015        1             A             350      : paymentID 1 + 2
2015        1             B             110      : paymentID 7
2015        1             C               0
2015        2             A             200      : paymentID 4
2015        2             B               0
2015        2             C               0
2016        1             A               0
2016        1             B             300      : paymentID 3
2016        1             C               0
2016        2             A               0
2016        2             B             300      : paymentID 5 + 8
2016        2             C             700      : paymentId 6

It is important that there are values for every category (so for 2015, customer 1 has 0 payment value for type C, but still it is good to see this).

In reality, there are over 10 payment types and about 30 customers. The total date range is 10 years.

Is this possible to do in only SQL, and if so could somebody show me how? If possible by using relatively easy queries so that I can learn from it, for instance by storing intermediary result into a #temptable.

Any help is greatly appreciated!

Answer 1

This is a simple query that generates the required 0s. Note that it may not be the most efficient way to generate this result set. If you already have lookup tables for customers or payment types, it would be preferable to use those rather than the CTEs¹ I use here:

declare @t table (customerID int,paymentID int,paymentType char(1),paymentDate date,
                 paymentAmount int)
insert into @t(customerID,paymentID,paymentType,paymentDate,paymentAmount) values
(1,1,'A','20151128', 500),
(1,2,'A','20151129',-150),
(1,3,'B','20160307', 300),
(2,4,'A','20150303', 200),
(2,5,'B','20160525',-100),
(2,6,'C','20160624', 700),
(1,7,'B','20150922', 110),
(2,8,'B','20160103', 400)

;With Customers as (
    select DISTINCT customerID from @t
), PaymentTypes as (
    select DISTINCT paymentType from @t
), Years as (
    select DISTINCT DATEPART(year,paymentDate) as Yr from @t
), Matrix as (
    select
        customerID,
        paymentType,
        Yr
    from
        Customers
            cross join
        PaymentTypes
            cross join
        Years
)
select
    m.customerID,
    m.paymentType,
    m.Yr,
    COALESCE(SUM(paymentAmount),0) as Total
from
    Matrix m
        left join
    @t t
        on
            m.customerID = t.customerID and
            m.paymentType = t.paymentType and
            m.Yr = DATEPART(year,t.paymentDate)
group by
    m.customerID,
    m.paymentType,
    m.Yr

Result:

customerID  paymentType Yr          Total
----------- ----------- ----------- -----------
1           A           2015        350
1           A           2016        0
1           B           2015        110
1           B           2016        300
1           C           2015        0
1           C           2016        0
2           A           2015        200
2           A           2016        0
2           B           2015        0
2           B           2016        300
2           C           2015        0
2           C           2016        700

(We may also want to play games with a numbers table and/or generate actual start and end dates for years if the date processing above needs to be able to use an index)

---

Note also how similar the top of my script is to the sample data in your question - except it's actual code that generates the sample data. You may wish to consider presenting sample code in such a way in the future since it simplifies the process of actually being able to test scripts in answers.

---

¹CTEs - Common Table Expressions. They may be thought of as conceptually similar to temp tables - except we don't actually (necessarily) materialize the results. They also are incorporated into the single query that follows them and the whole query is optimized as a whole.

Your suggestion to use temp tables means that you'd be breaking this into multiple separate queries that then necessarily force SQL to perform the task in an order that we have selected rather than letting the optimizer choose the best approach for the above single query.

Answer 2

a simple GROUP BY with SUM() on the paymentAmount will gives you what you wanted

select  year = datepart(year, paymentDate),
        customerID,
        paymentType,
        paymentSum = sum(paymentAmount)
from    payment_data
group by datepart(year, paymentDate), customerID, paymentType