Return 1 row with various sums based on date?-CodePudding

Assume a table of purchase transactions with columns CustId, Amount, DatePosted where Amount is the value of the transaction, and DatePosted is a DATETIME value. Given a specific CustId, how would I write a select such that it returns a single row with the following columns: CustId, total value of transactions in the last 3 days, last 60 days, 1 year, 2 years (5 columns total).

Example table:

CustId	Amount	DatePosted
1234	698.02	2023-01-23Z12:34:56
1234	582.69	2022-12-15Z19:57:23
1234	7775.22	2022-12-02Z02:34:32
1234	18.72	2022-01-23Z12:34:56
1234	2.27	2021-01-23Z12:34:56

Expected output given the sample data above when searching using CustId=1234:

CustId	3-day Total	60-day Total	1 year Total	2 year Total
1234	698.02	9055.93	9074.65	9076.92

CodePudding user response：

This is set up so that you can set @CustID = null and the query will return results for all customers in the set. Coalesce was also used to convert nulls to 0.

DECLARE @CustID BIGINT;

SELECT table1.custID,
    COALESCE([3Day].Total,0) AS [3DayTotal],
    COALESCE([60Day].Total,0)AS [60DayTotal],
    COALESCE([1Year].Total, 0) AS [1YearTotal],
    COALESCE([2Year].Total, 0) AS [2YearTotal]
FROM table1 LEFT OUTER JOIN
    (SELECT custID, SUM(Amount) AS Total FROM table1 WHERE DatePosted > DATEADD(DAY, -3, GETDATE()) GROUP BY custID) AS [3Day] ON table1.CustID = [3Day].CustID LEFT OUTER JOIN
    (SELECT custID, SUM(Amount) AS Total FROM table1 WHERE DatePosted > DATEADD(DAY, -60, GETDATE()) GROUP BY custID) AS [60Day] ON table1.CustID = [60Day].CustID LEFT OUTER JOIN
    (SELECT custID, SUM(Amount) AS Total FROM table1 WHERE DatePosted > DATEADD(YEAR, -1, GETDATE()) GROUP BY custID) AS [1Year] ON table1.CustID = [1Year].CustID LEFT OUTER JOIN
    (SELECT custID, SUM(Amount) AS Total FROM table1 WHERE DatePosted > DATEADD(YEAR, -2, GETDATE()) GROUP BY custID) AS [2Year] ON table1.CustID = [2Year].CustID
WHERE table1.CustID = @CustID 
    OR @CustID IS NULL
GROUP BY table1.CustID, [3DayTotal], [60DayTotal], [1YearTotal],[2YearTotal]

CodePudding user response：

You could get all purchase data for the last 2 years, then using SUM with SQL CASE expression to calculate total value for each time-range.

SELECT 
  CustId, 
  SUM(CASE WHEN DatePosted >= Last3Day THEN Amount ELSE 0 END) AS [3-day Total],
  SUM(CASE WHEN DatePosted >= Last60Day THEN Amount ELSE 0 END) AS [60-day Total],
  SUM(CASE WHEN DatePosted >= Last1Year THEN Amount ELSE 0 END) AS [1 year Total],
  SUM(CASE WHEN DatePosted >= Last2Year THEN Amount ELSE 0 END) AS [2 year Total]
FROM 
  <your data table>,
  (SELECT 
      DATEADD(DAY, -3, GETDATE()) AS Last3Day,
      DATEADD(DAY, -60, GETDATE()) AS Last60Day,
      DATEADD(YEAR, -1, GETDATE()) AS Last1Year,
      DATEADD(YEAR, -2, GETDATE()) AS Last2Year) timerange
WHERE DatePosted >= Last2Year      
GROUP BY CustId;

Demo: http://sqlfiddle.com/#!18/9eecb/179880

CodePudding user response：

This query assumes 2 year max. If you want to go further back then change the where clause as well. No need to use coalesce or a derived table. SQL server query planner may be smart enough to provide similar performance for all these solutions but this is easier to understand:

SELECT 
    CustId, 
    SUM(CASE WHEN DatePosted >= DATEADD(day, -3, GETDATE()) THEN Amount ELSE 0 END) AS [3-day Total], 
    SUM(CASE WHEN DatePosted >= DATEADD(day, -60, GETDATE()) THEN Amount ELSE 0 END) AS [60-day Total], 
    SUM(CASE WHEN DatePosted >= DATEADD(year, -1, GETDATE()) THEN Amount ELSE 0 END) AS [1 year Total], 
    SUM(CASE WHEN DatePosted >= DATEADD(year, -2, GETDATE()) THEN Amount ELSE 0 END) AS [2 year Total]
FROM PurchaseTransactions
WHERE CustId = 1234 AND DatePosted >= DATEADD(year, -2, GETDATE())
GROUP BY CustId