SQL string for an average over 7 days

[WhytheQ]

I have a simple table:

Date1 Value1
Date2 Value2
Date3 Value3
--
--
--

What is the SQL string I need to use to find the average of the Value
field over the 7 days preceding a particular date?

I actually need this for a macro in Excel which need to use a date in a
cell, given by the user, and then once the macro is run, a query need
to go and get the data from an Access table.

(I also need an average over 30 day prior to specified date, which
should be easy if I have the answer to the above - and also the
preceding 30days, which might be slightly trickier!)

Any help greatly appreciated, as I'm pretty new to dtatbase work.
Jason.

 

[Ken Sheridan]

Jason:

For the previous 7 days:

PARMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate BETWEEN
[Enter Date:]-7 AND [Enter Date:]-1;

For the last 30 days:

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate BETWEEN
[Enter Date:]-30 AND [Enter Date:]-1;

For the 30 before that:

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate BETWEEN
[Enter Date:]-60 AND [Enter Date:]-31;

This assumes the date values all have zero times of day. If they might not,
then a more reliable syntax is:

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate >= [Enter Date:]-7
AND YourDate < [Enter Date:];

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate >= [Enter Date:]-30
AND YourDate < [Enter Date:];

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate >= [Enter Date:]-60
AND YourDate < [Enter Date:]-30;

I'll leave you to figure out how to incorporate this into your Excel macro.

Ken Sheridan
Stafford, England

 

[WhytheQ]

Thanks Ken,
You've given me plenty to have fun with!

Regards,
Jason.

Jason:

For the previous 7 days:

PARMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate BETWEEN
[Enter Date:]-7 AND [Enter Date:]-1;

For the last 30 days:

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate BETWEEN
[Enter Date:]-30 AND [Enter Date:]-1;

For the 30 before that:

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate BETWEEN
[Enter Date:]-60 AND [Enter Date:]-31;

This assumes the date values all have zero times of day. If they might not,
then a more reliable syntax is:

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate >= [Enter Date:]-7
AND YourDate < [Enter Date:];

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate >= [Enter Date:]-30
AND YourDate < [Enter Date:];

PARAMETERS [Enter Date:] DATETIME;
SELECT AVG(YourField) As AverageValue
FROM YourTable
WHERE YourDate >= [Enter Date:]-60
AND YourDate < [Enter Date:]-30;

I'll leave you to figure out how to incorporate this into your Excel macro.

Ken Sheridan
Stafford, England

I have a simple table:

Date1 Value1
Date2 Value2
Date3 Value3
--
--
--

What is the SQL string I need to use to find the average of the Value
field over the 7 days preceding a particular date?

I actually need this for a macro in Excel which need to use a date in a
cell, given by the user, and then once the macro is run, a query need
to go and get the data from an Access table.

(I also need an average over 30 day prior to specified date, which
should be easy if I have the answer to the above - and also the
preceding 30days, which might be slightly trickier!)

Any help greatly appreciated, as I'm pretty new to dtatbase work.
Jason.