G
Gary Walter
sorry...left off closing parenthesis...
SELECT
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T4.[DATE]>=T3.[DATE]
And
T4.[DATE]<=DateAdd("yyyy",1,T3.[DATE])
GROUP BY
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day;
it might be that you also have to "phase shift"
the WHERE clause to honor grouping
like you did with last one...
SELECT
T3.SITE_NO,
T3.[DATE],
DateAdd("yyyy",1,T3.[DATE]) As EndYr,
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T3.[DATE]<=T4.[DATE]
And
DateAdd("yyyy",1,T3.[DATE])>=T4.[DATE]
GROUP BY
T3.SITE_NO,
T3.[DATE],
DateAdd("yyyy",1,T3.[DATE]),
T3.Avg7Day;
SELECT
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T4.[DATE]>=T3.[DATE]
And
T4.[DATE]<=DateAdd("yyyy",1,T3.[DATE])
GROUP BY
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day;
it might be that you also have to "phase shift"
the WHERE clause to honor grouping
like you did with last one...
SELECT
T3.SITE_NO,
T3.[DATE],
DateAdd("yyyy",1,T3.[DATE]) As EndYr,
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T3.[DATE]<=T4.[DATE]
And
DateAdd("yyyy",1,T3.[DATE])>=T4.[DATE]
GROUP BY
T3.SITE_NO,
T3.[DATE],
DateAdd("yyyy",1,T3.[DATE]),
T3.Avg7Day;
Gary Walter said:Okay...your previous query had a "symmetry"
to the group gathering, but this one I believe
you are going to have to work through explicitly...
for a T3.SITE_NO/T3.[DATE] group, you want
T4 to provide all the Avg7Day's over a year starting
with T3.[DATE]..
SELECT
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day,
MIN(T4.Avg7Day) AS AnnualMinimum
FROM
[7_Day_Avg] AS T3
INNER JOIN
[7_Day_Avg] AS T4
ON
T3.SITE_NO = T4.SITE_NO
WHERE
T4.[DATE]>=T3.[DATE]
And
T4.[DATE]<=DateAdd("yyyy",1,T3.[DATE]
GROUP BY
T3.SITE_NO,
T3.[DATE],
T3.Avg7Day;
j.boswell said:Here's where I stand. I have two queries. The first calculates a 7-
day moving average (Avg7Day) for every day. Here's how it works:
SELECT T1.SITE_NO, T1.DATE, T1.FLOW, Avg(T2.FLOW) AS Avg7Day,
Year(([T1].DATE)) AS FY
FROM [USGS RAW] AS T1 INNER JOIN [USGS RAW] AS T2 ON T1.SITE_NO =
T2.SITE_NO
WHERE (((T1.SITE_NO)="03345000") AND ((T1.DATE)>=([T2].[DATE]-3) And
(T1.DATE)<=([T2].[DATE]+3)))
GROUP BY T1.SITE_NO, T1.DATE, T1.FLOW, Year([T1].DATE);
The next query is supposed to calculate a minimum the minimum 7-day
average over the following year, for each day. For example, for
1/1/2001, it calculates the minimum 7-day average between 1/1/2001 and
12/31/2002. For 1/2/2001, it calculates the minimum 7-day average
between 1/2/2001 and 1/1/2002. The problem is that it is giving me
the 7-day average corresponding to the exact day. There is no
"minimum over a year" being calculated. Here's how it's written:
SELECT [T3].[SITE_NO], [T3].[DATE], Min(T4.Avg7Day) AS AnnualMinimum
FROM [7_Day_Avg] AS T3 INNER JOIN [7_Day_Avg] AS T4 ON ([T3].[DATE] =
[T4].[DATE]) AND (T3.SITE_NO = T4.SITE_NO)
WHERE (([T3].[DATE]>=[T4].[DATE]) And ([T3].[DATE]<=DateAdd("yyyy",1,
[T4].[DATE])))
GROUP BY [T3].[SITE_NO], [T3].[DATE];
Then I need to sort it on 10/1/XXXX, which will give me the minimum 7-
day average for 365 days beginning with 10/1 of each year. I'm all
out of ideas at this point.