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

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.

CodePudding user response：

For the defined problem and with the years you know about from the sample data hard-coded:

;WITH agg AS
(
  SELECT idCustomer, y = YEAR(orderDate), 
    OrderCount = COUNT(*), 
    TotalSpent = COALESCE(SUM(orderTotal),0)
  FROM dbo.orders
  GROUP BY idCustomer, DATEPART(YEAR, orderDate)
)
SELECT agg.idCustomer, c.name,
  OrderCount2018 = MAX(CASE WHEN y = 2018 THEN OrderCount END),
  TotalSpent2018 = MAX(CASE WHEN y = 2018 THEN TotalSpent END),
  OrderCount2019 = MAX(CASE WHEN y = 2019 THEN OrderCount END),
  TotalSpent2019 = MAX(CASE WHEN y = 2019 THEN TotalSpent END),
  LifetimeCount  = SUM(OrderCount),
  LifetimeSpent  = SUM(TotalSpent)
FROM agg
INNER JOIN dbo.customers AS c
  ON c.idCustomer = agg.idCustomer
GROUP BY agg.idCustomer, c.name;

However you want the query to be dynamic, so you can't be hard-coding years and column names. To do this dynamically:

DECLARE @MinYear int, @MaxYear int;

SELECT @MinYear = MIN(YEAR(orderDate)), @MaxYear = MAX(YEAR(orderDate))
  FROM dbo.orders;
  
DECLARE @sql nvarchar(max) = N';WITH agg AS
(
  SELECT idCustomer, y = YEAR(orderDate), 
    OrderCount = COUNT(*), 
    TotalSpent = COALESCE(SUM(orderTotal),0)
  FROM dbo.orders
  GROUP BY idCustomer, DATEPART(YEAR, orderDate)
)
SELECT agg.idCustomer, c.name';

;WITH y(y) AS (SELECT @MinYear UNION ALL
  SELECT y 1 FROM y WHERE y < @MaxYear),
  z(y) AS (SELECT CONVERT(char(4), y) FROM y)
 SELECT @sql  = N',
  OrderCount'   y   N' = MAX(CASE WHEN y = '   y   N' THEN OrderCount END),
  TotalSpent'   y   N' = MAX(CASE WHEN y = '   y   N' THEN TotalSpent END)'
 FROM z;
  
SET @sql  = N',
  LifetimeCount  = SUM(OrderCount),
  LifetimeSpent  = SUM(TotalSpent)
FROM agg
INNER JOIN dbo.customers AS c
  ON c.idCustomer = agg.idCustomer
GROUP BY agg.idCustomer, c.name;';

SELECT @sql;
EXEC sys.sp_executesql @sql;

Output in both cases:

idCustomer	name	OrderCount2018	TotalSpent2018	OrderCount2019	TotalSpent2019	LifetimeCount	LifetimeSpent
100	Bob	2	62.25	2	407.33	4	469.58
101	Barb	2	3023.06	2	17.98	4	3041.04

Example db<>fiddle

CodePudding user response：

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 :)