T-SQL: How to return separate columns of data for different date ranges selected from same table?

Question

I am trying to get customer spending data by range from the orders table, but the script seems to always combine all the tables rows per customer id regardless of however I try to specify date ranges. Any guidance in how I can accomplish my goal here is very appreciated.

EX:

CREATE TABLE customers 
(
    idCustomer INT IDENTITY(100,1),
    name VARCHAR(100),
    PRIMARY KEY (idCustomer)
);

INSERT INTO customers 
VALUES ('Bob'), ('Barb');

CREATE TABLE orders 
(
    idOrder INT IDENTITY(1,1),
    idCustomer INT,
    orderTotal DECIMAL(19,2),
    orderDate DATETIME2
    PRIMARY KEY (idOrder)
);

INSERT INTO orders 
VALUES (100, 25.25, '2018-4-15'),
       (100, 37.00, '2018-6-15'),
       (100, 175.00, '2019-3-1'),
       (100, 232.33, '2019-8-3'),
       (101, 18.56, '2018-1-17'),
       (101, 3004.50, '2018-5-12'),
       (101, 2.98, '2019-6-1'),
       (101, 15.00, '2019-11-3')

SELECT
    c.idCustomer,
    c.name,
    COUNT(ord2018.idOrder) AS '#Orders 2018',
    SUM(ord2018.orderTotal) AS 'Total Spent 2018'
    COUNT(ord2019.idOrder) AS '#Orders 2019',
    SUM(ord2019.orderTotal) AS 'Total Spent 2019'
    COUNT(ordersAll.idOrder) AS '#Orders Lifetime',
    SUM(ordersAll.orderTotal) AS 'Total Spent Lifetime'
FROM 
    customers c
JOIN 
    orders ord2018 ON ord2018.idCustomer = c.idCustomer 
                   AND ord2018.orderDate > '2017-12-31'
                   AND ord2018.orderDate < '2019-1-1'
JOIN 
    orders ord2019 ON ord2019.idCustomer = c.idCustomer
                   AND ord2019.orderDate > '2018-12-31'
                   AND ord2019.orderDate < '2020-1-1'
JOIN
    orders ordersAll on ordersAll.idCustomer = c.idCustomer
GROUP BY 
    c.idCustomer, c.name

I want to see something like this:

idCustomer	Name	#Orders 2018	Total Spent 2018	#Orders 2019	Total Spent 2019	#Orders Lifetime	Total Spent Lifetime
100	Bob	2	62.25	2	407.33	4	469.58
101	Barb	2	3023.06	2	17.98	4	3041.04

But I am getting duplicate values across the total columns which appear to just be the sum of all records in orders table per customer.

Thanks in advance.

Answer 1

For your supplied sample data and considering your example query attempt, you can use a conditional sum in conjunction with apply.

select * 
from customers c
outer apply (
    select 
      IsNull(Sum(case when Year(orderdate) = 2018 then 1 end),0) [#Orders2018],
      IsNull(Sum(case when Year(orderdate) = 2018 then ordertotal end),0) [Total spent 2018],
      IsNull(Sum(case when Year(orderdate) = 2019 then 1 end),0) [#Orders2019],
      IsNull(Sum(case when Year(orderdate) = 2019 then ordertotal end),0) [Total spent 2019],
      Count(*) TotalOrders,
      Sum(Ordertotal) TotalSpend
    from orders o
    where o.idCustomer = c.idCustomer
)o;

Example DB<>Fiddle

• Edit, added Totals for sake of completeness :)