Finding Minimums Over Multiple Years:

[jboswell]

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.

[nico5038]

To get it per Year use:

SELECT [T3].[SITE_NO], Year([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], Year([T3].[DATE]);

Finally the 10/1 requirement (I guess a financial year) can be solved by correcting the [Date] field before using it. When 10/1 is situated in "the next" year, just add 3 months like:

select dateserial(year ([Date]),month([Date])+3,Day([Date]) as FinancialDate.. .

A last tip is to change the Date fieldname into FinancialDate, or another name, as Date is a reserved word and can cause trouble...

Nic;o)