您可以获取过去2年的所有购买数据，然后使用SUM和SQL CASE expression计算每个时间范围的总值。

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;

演示版：http：//sqlfiddle.com/#！18/9 eecb/179880

此查询假定最长为2年。如果您想继续返回，则还需更改where子句。无需使用合并或派生表。SQL Server查询规划器可能足够智能，可以为所有这些解决方案提供类似的性能，但这更易于理解：

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(Amount) AS [2 year Total]
FROM PurchaseTransactions
WHERE CustId = 1234 AND DatePosted >= DATEADD(year, -2, GETDATE())
GROUP BY CustId

这样设置后，您可以设置@CustID = null，查询将返回集合中所有客户的结果。
编辑：更新了我下面的查询，让你在你想要的范围内获得更多的灵活性，如果你想得到额外的启发式。（计数，平均值，等）也删除了合并，因为它根本不需要在这里。

DECLARE @CustID BIGINT;

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