Thank you. I have done as I did before and have tried to annotate what I understand in the second SQL statement. Have I understood this all correctly?
Code:
SELECT
\* Select the following fields from the specified table and perform any calculations given /*
Q1.CurrentDate,
Q1.Computer_Name,
Q1.Disk_ID,
Q1.CurrentSize,
Q1.CurrentUsed,
(Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
Q2.DailyDelta,
\* Divide CurrentLeft/DailyDelta and turn the result into an integer (no decimal places) */
Int(CurrentLeft/Q2.DailyDelta) AS EstDaysLeft,
\* Add the number of days in EstDaysLeft field to the CurrentDate field */
DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
FROM
\* Any reference to Q1 above links to the UsageDeltas Query /*
UsageDeltas AS Q1
\* Join the same query records as a different alias Q2 and perform the calculations */
INNER JOIN
(SELECT
Computer_Name,
Disk_ID,
\* Divide the ChgInUsed and ChgInDate fields and provide an Average figure */
AVG(ChgInUsed/ChgInDate) AS DailyDelta,
\* Calculate the max (latest) date in the CurrentDate field */
MAX(CurrentDate) As MaxDate
FROM UsageDeltas
\* Group the output by these fields and prefix the new fields with Q2 */
GROUP BY Computer_Name, Disk_ID ) AS Q2
\* Provide the rows there these conditions are met, giving only the latest (up-to-date) information by using the MaxDate field */
ON (Q1.CurrentDate = Q2.MaxDate) AND (Q1.Disk_ID = Q2.Disk_ID) AND (Q1.Computer_Name = Q2.Computer_Name)
WHERE
\* Only provide the data where DailyDelta is above 0 (i.e. not a negative figure) */
DailyDelta>0;