question

johne417 avatar image
johne417 asked

SQL Server Query For Average Value over a Date Period

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

sqlsql-server-2012querysql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.