sql - SQL Server Query for average value over a date period

Question

DECLARE @SampleOrderTable TABLE
                          (
                               pkPersonID INT,
                               OrderDate DATETIME,
                               Amount NUMERIC(18, 6)
                          )

INSERT INTO @SampleOrderTable (pkPersonID, OrderDate, Amount) 
VALUES (1, '12/10/2019', '762.84'),
       (2, '11/10/2019', '886.32'),
       (3, '11/9/2019', '10245.00')

How do I select the the last 4 days prior to OrderDate and the average Amount over that period?

So result data would be:

pkPersonID   Date             Amount
------------------------------------
    1        '12/7/2019'     190.71
    1        '12/8/2019'     190.71
    1        '12/9/2019'     190.71
    1        '12/10/2019'    190.71
    2        '12/7/2019'     221.58
    2        '12/8/2019'     221.58
    2        '12/9/2019'     221.58
    2        '12/10/2019'    221.58
    3        '11/6/2019'    2561.25
    3        '11/7/2019'    2561.25
    3        '11/8/2019'    2561.25
    3        '11/9/2019'    2561.25

标签： sqlsql-server

Answer 1

DECLARE @SampleOrderTable TABLE (
  pkPersonID INT,
  OrderDate DATETIME,
  Amount NUMERIC(18, 6)
);

INSERT INTO @SampleOrderTable
  (pkPersonID, OrderDate, Amount) 
VALUES
  (1, '12/20/2019', 762.84),
  (2, '12/20/2019', 886.32),
  (3, '12/20/2019', 10245.00),
  (4, '12/19/2019', 50.00),
  (5, '12/19/2019', 100.00),
  (6, '09/01/2019', 200.00),
  (7, '09/01/2019', 300.00),
  (8, '12/15/2019', 400.00),
  (9, '12/15/2019', 500.00),
  (10, '09/02/2019', 150.00),
  (11, '09/02/2019', 1100.00),
  (12, '09/02/2019', 1200.00),
  (13, '09/02/2019', 1300.00),
  (14, '09/02/2019', 1400.00),
  (15, '09/02/2019', 1500.00);

SELECT OrderDate,AVG(Amount) AS Average_Value
FROM @SampleOrderTable
WHERE DATEDIFF(DAY, CAST(OrderDate AS DATETIME), CAST(GETDATE() AS Datetime)) <= 4
GROUP BY OrderDate;