DECLARE @SampleOrderTable TABLE(
pkPersonID INT,
OrderDate DATETIME,
Amount NUMERIC(18,6))
INSERT INTO @SampleOrderTable(pkPersonID,OrderDate,Amount) VALUES (1,'12/10/2019','762.84')
INSERT INTO @SampleOrderTable(pkPersonID,OrderDate,Amount) VALUES (2,'11/10/2019','886.32')
INSERT INTO @SampleOrderTable(pkPersonID,OrderDate,Amount) VALUES (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
