Calculating Multiple Differences

[Dal Jeanis]

So, here's a query that should produce the table layout. I changed the grouping order to Computer, Disk, then reading date, because I expect that will give Jet an efficiency advantage in all the joins by Computer and Disk:

Query qryUsageDeltas:
SELECT 
   DS1.Computer_Name, 
   DS1.Disk_ID, 
   DS1.Date_Of_Report As CurrentDate, 
   DS2.Date_Of_Report As PriorDate,
  (DS1.Date_Of_Report - DS2.Date_Of_Report) As ChgInDate,
   DS1.Disk_Size_MB As CurrentSize, 
   DS2.Disk_Size_MB As PriorSize, 
   (DS1.Disk_Size_MB - DS2.Disk_Size_MB) As ChgInSize, 
   DS1.Percentage_Used AS CurrentPct, 
   DS2.Percentage_Used As PriorPct, 
   (DS1.Percentage_Used - DS2.Percentage_Used) As ChgInPct, 
   DS1.Disk_Space_Used As CurrentUsed, 
   DS2.Disk_Space_Used As PriorUsed, 
   (DS1.Disk_Space_Used - DS2.Disk_Space_Used) As ChgInUsed 
FROM 
   DiskSpaceStats AS DS1
   INNER JOIN 
   DiskSpaceStats AS DS2
   ON DS2.Computer_Name = DS1.Computer_Name 
   AND DS2.Disk_ID = DS1.Disk_ID 
WHERE DS2.Date_Of_Report = 
     (SELECT MAX(DS3.Date_Of_Report) 
     FROM DiskSpaceStats AS DS3 
     WHERE DS1.Computer_Name = DS3.Computer_Name 
     AND DS1.Disk_ID = DS3.Disk_ID 
     AND DS1.Date_Of_Report > DS3.Date_Of_Report)
ORDER BY 
   DS1.Computer_Name, 
   DS1.Disk_ID,
   DS1.Date_Of_Report DESC;

And here's the query that should use that to make your desired report:

query qryEstFullDate:
SELECT 
   Q1.CurrentDate,
   Q1.Computer_Name, 
   Q1.Disk_ID, 
   Q1.CurrentSize,
   Q1.CurrentUsed,
   (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
   Q2.DailyDelta,
   (CurrentLeft/Q1.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
FROM 
   qryUsageDeltas AS Q1
   INNER JOIN
      (SELECT 
          Q2.Computer_Name,
          Q2.Disk_ID,
          AVG(Q2.ChgInUsed/Q2.ChangeInDate) AS DailyDelta
       FROM qryUsageDeltas AS Q2
       GROUP BY Q2.Computer_Name, Q2.Disk_ID
       )
   ON Q1.Computer_Name = Q2.Computer_Name
   AND Q1.Disk_ID = Q2.Disk_ID
WHERE Q1.CurrentDate = 
   (SELECT Max(Q3.CurrentDate)
    From qryUsageDeltas AS Q3
    WHERE Q1.Computer_Name = Q3.Computer_Name
    AND Q1.Disk_ID = Q3.Disk_ID);

FIX NOTES:

I made a fix to the joins in the query here, and put the aliases in order Q1, Q2, Q3. The previous references to TD2 and TD3 were inconsistent - no wonder you were confused.

I also recently discovered that we can re-use calculated fields in other calculations in a single query, so this version should read much easier.

EFFICIENCY NOTES:

Now, honestly, both of those are still aircode and I'm not sure how efficiently those queries will operate together.



Since the first query has two correlated subqueries to DiskSpaceStats, and then the second refers to the first query three times, the Jet database engine may be joining that DiskSpaceStats table to itself eight or more times. I'd expect Jet to be more efficient than that, creating the query and then using the intermediate result three times, but that may depend on the indexing of the underlying DiskSpaceStats table.

Hmmm. Q3 should probably be switched to DiskSpaceStats in either case, since only the date of the latest DiskSpaceStats record for each Computer/Disk combination is needed. That would look like this:

query qryEstFullDate:
SELECT 
   Q1.CurrentDate,
   Q1.Computer_Name, 
   Q1.Disk_ID, 
   Q1.CurrentSize,
   Q1.CurrentUsed,
   (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
   Q2.DailyDelta,
   (CurrentLeft/Q1.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
FROM 
   qryUsageDeltas AS Q1
   INNER JOIN
      (SELECT 
          Q2.Computer_Name,
          Q2.Disk_ID,
          AVG(Q2.ChgInUsed/Q2.ChangeInDate) AS DailyDelta
       FROM qryUsageDeltas AS Q2
       GROUP BY Q2.Computer_Name, Q2.Disk_ID
       )
   ON Q1.Computer_Name = Q2.Computer_Name
   AND Q1.Disk_ID = Q2.Disk_ID
WHERE Q1.CurrentDate = 
   (SELECT Max(T3.Date_Of_Report)
    From DiskSpaceStats AS T3
    WHERE Q1.Computer_Name = T3.Computer_Name
    AND Q1.Disk_ID = T3.Disk_ID);

The first version would be more efficient if there were an actual table of tblUsageDeltas, but the last version would perhaps be more efficient if it's a query. If performance on these queries turns out to be an issue, then check back and we can see about optimizing more.

[dr4ke1990]

Thank you. I'm a bit confused what the third query is doing, that the second one isn't? Because the second one groups it by Computer_Name/Disk_ID it only returns one value, and as it averages all the lines in the first query is that not the same the third one is doing?

[Dal Jeanis]

The only change in the third query from the second should be where the MAX() in the final WHERE clause is coming from. It's taking it straight from DiskSpaceStats rather than from the query.

[dr4ke1990]

Sorry - I don't understand :-S

[Dal Jeanis]

I 've gone back and marked the changes in green bold and blue bold. Green is changing to use the DiskSpaceStats table rather than the qryUsageDeltas query. Blue is just changing the alias from Q3 to T3 (for clarity, since it's now a table).

[dr4ke1990]

Hi Dal,

apologies that I haven't posted in a while; I've not completed the work or forgotten :-). I'm just still working through this all trying to make some sense of it. There are also a few error messages popping up which I'm trying to resolve (that's the true test that I understand!!)

[Dal Jeanis]

Yep, understanding is the second most important thing - right behind getting enough productive work done to satisfy the organization and keep the paycheck rolling in.

[dr4ke1990]

Ok - I've tried (and tried and tried and tried!!) to get it to work but It's not ! The first query works fine (I think) and I understand it (I think) .

The second query doesn't like the Q2.DailyDelta section and when I try to run the query it comes back with an error 'Syntax error in JOIN operation' and points to that. So far I've tried changing '(CurrentLeft/Q1.DailyDelta) AS EstDaysLeft' to ' (CurrentLeft/Q2.DailyDelta) AS EstDaysLeft' and removing the GROUP BY clause in the INNER JOIN subquery; both to no avail...

Am I missing something extremely simple?!

 SELECT 
   Q1.CurrentDate,
   Q1.Computer_Name, 
   Q1.Disk_ID, 
   Q1.CurrentSize,
   Q1.CurrentUsed,
   (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
   Q2.DailyDelta,
   (CurrentLeft/Q1.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
FROM 
    UsageDeltas AS Q1
   INNER JOIN
      (SELECT 
          Q2.Computer_Name,
          Q2.Disk_ID,
          AVG(Q2.ChgInUsed/Q2.ChangeInDate) AS DailyDelta
       FROM UsageDeltas AS Q2
       GROUP BY Q2.Computer_Name, Q2.Disk_ID
       )
   ON Q1.Computer_Name = Q2.Computer_Name
   AND Q1.Disk_ID = Q2.Disk_ID
WHERE Q1.CurrentDate = 
   (SELECT Max(T3.Date_Of_Report)
    From DiskSpaceStats AS T3
    WHERE Q1.Computer_Name = T3.Computer_Name
    AND Q1.Disk_ID = T3.Disk_ID);

[Dal Jeanis]

Sorry. The Jet database engine is complaining because DailyDelta is not a field on qryUsageDeltas, so neither Q1.DailyDelta nor Q2.DailyDelta can be resolved. Those should both have been Q2, and I should have put the Q2 alias on the entire subquery rather than the inner query. Try this:

query qryEstFullDate:
SELECT 
   Q1.CurrentDate,
   Q1.Computer_Name, 
   Q1.Disk_ID, 
   Q1.CurrentSize,
   Q1.CurrentUsed,
   (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
   Q2.DailyDelta,
   (CurrentLeft/Q2.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
FROM 
   qryUsageDeltas AS Q1
   INNER JOIN
     (SELECT 
          Computer_Name,
          Disk_ID,
          AVG(ChgInUsed/ChangeInDate) AS DailyDelta
       FROM qryUsageDeltas
       GROUP BY Computer_Name, Disk_ID
      ) AS Q2
  ON Q1.Computer_Name = Q2.Computer_Name
   AND Q1.Disk_ID = Q2.Disk_ID
WHERE Q1.CurrentDate = 
   (SELECT Max(Q3.CurrentDate)
    From qryUsageDeltas AS Q3
    WHERE Q1.Computer_Name = Q3.Computer_Name
    AND Q1.Disk_ID = Q3.Disk_ID);

Remember to remove the Q2 alias and all Q2 qualifiers inside the red parenthesis text.

[dr4ke1990]

Thank you for that. It appears the code works, but the database crashes (I assume because of the JET enginer you were talking about before?). I converted the UsageDeltas query from a Select to a Make Table but now when I run the query it displays a different error 'An action query cannot be used as a row source'. The below is the working code . Also... On a slightly related topic, I'm really interested to know where you learnt SQL? I'm not a technie by trade, I just enjoy making databases and using the SQL language as a hobby but there is still SO much to learn... Has it just been experience that has tought you, or have you got some sources (I often use W3Schools).

 SELECT 
   Q1.CurrentDate,
   Q1.Computer_Name, 
   Q1.Disk_ID, 
   Q1.CurrentSize,
   Q1.CurrentUsed,
   (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
   Q2.DailyDelta,
   (CurrentLeft/Q2.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate

FROM 
   UsageDeltas AS Q1
   INNER JOIN
     (SELECT 
          Computer_Name,
          Disk_ID,
          AVG(ChgInUsed/ChgInDate) AS DailyDelta
       FROM UsageDeltas
       GROUP BY Computer_Name, Disk_ID
      ) AS Q2
  ON Q1.Computer_Name = Q2.Computer_Name
   AND Q1.Disk_ID = Q2.Disk_ID
 
WHERE Q1.CurrentDate = 
   (SELECT Max(Q3.CurrentDate)
    From UsageDeltas AS Q3
    WHERE Q1.Computer_Name = Q3.Computer_Name
    AND Q1.Disk_ID = Q3.Disk_ID);

[Dal Jeanis]

DATABASE CRASH AND QUERY EFFICIENCY

I'm guessing the database is just too big to do that complex correlated subquery in a reasonable amount of time. So we'll break it down into discrete steps, if the next thing doesn't fix it first.

In reviewing the query, I noticed that the two subqueries (Q2 and Q3) are both aggregate queries across the exact same data with the same GROUP BY, so even though they are meeting different structural needs, there's no reason to have them be separate. I'll combine them.

I'm also adding an INT() function around the calculation of EstDaysLeft, just in case the floating point result is slowing down the Dateadd function.

So, first copy your UsageDeltas maketable back into a SELECT query named qryUsageDeltas and then try this simpler query as qryEstFullDate2:

query qryEstFullDate2:
SELECT 
   Q1.CurrentDate,
   Q1.Computer_Name, 
   Q1.Disk_ID, 
   Q1.CurrentSize,
   Q1.CurrentUsed,
   (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
   Q2.DailyDelta,
   Int(CurrentLeft/Q2.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
FROM 
   QryUsageDeltas AS Q1
   INNER JOIN
     (SELECT 
          Computer_Name,
          Disk_ID,
          AVG(ChgInUsed/ChgInDate) AS DailyDelta,
          MAX(CurrentDate) As MaxDate
       FROM QryUsageDeltas
       GROUP BY Computer_Name, Disk_ID
      ) AS Q2
   ON  Q1.Computer_Name = Q2.Computer_Name
   AND Q1.Disk_ID = Q2.Disk_ID
   AND Q1.CurrentDate = Q2.MaxDate;

Execute that query, and see if that query comes back in a reasonable amount of time.

If not, then we'll break it into multiple discrete steps.


DECONSTRUCTING THE COMPLEX QUERY INTO STEPS

First, copy your UsageDelta query to a maketable query MakeusageDeltas to create a table called tblUsageDeltas.

Second, run MakeusageDeltas to make the table.

Third, we'll use the table rather than the query as the source to run the following query qryEstFullDate3:

query qryEstFullDate3:
SELECT 
   T1.CurrentDate,
   T1.Computer_Name, 
   T1.Disk_ID, 
   T1.CurrentSize,
   T1.CurrentUsed,
   (T1.CurrentSize - T1.CurrentUsed) AS CurrentLeft,
   T2.DailyDelta,
   Int(CurrentLeft/T2.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,T1.CurrentDate) AS EstFullDate
FROM 
   TblUsageDeltas AS T1
   INNER JOIN
     (SELECT 
          Computer_Name,
          Disk_ID,
          AVG(ChgInUsed/ChgInDate) AS DailyDelta,
          MAX(CurrentDate) As MaxDate
       FROM TblUsageDeltas
       GROUP BY Computer_Name, Disk_ID
      ) AS T2
   ON  T1.Computer_Name = T2.Computer_Name
   AND T1.Disk_ID = T2.Disk_ID
   AND T1.CurrentDate = T2.MaxDate;

LEARNING STRATEGIES

I learned SQL by successive approximation, (A) in theory at college three decades back (B) on the job supporting mainframe DB2 two decades back (C) On the job writing mainframe DB2 and Access SQL one decade back (D) with lots of practice doing Access SQL over the last three years at my current job and here on the forum.

Once you have basic familiarity, it's pure repetition (with variation) that builds mastery. So, read each "query" and "report" post on this forum or another, posters asking for SQL or posing problems with SQL, and try to understand each solution.

Or, better, take each post on the forum asking for SQL, that you ALMOST know how to do, and do it for them. Make your best attempt, either write aircode or test your real code in a junk database. Just make some mistakes and learn from them.

Focus on what you can ALMOST do without research. Nail that down, then move on to items that you can do with QUICK research.

Wash, Rinse, repeat.

[dr4ke1990]

Thanks Dal,

This seems to work perfectly - I used the first method and it's certainly made it more efficient as it calculates it in a number of seconds. I have, however, amended the SQL very slightly by adding in a WHERE clause to only show values where the DailyDelta number is bigger than 0 to remove entries where the usage amount has reduced (and thus the date is in the past) - I just wanted to check that this isn't going to cause any issues which I haven't forseen?

 
SELECT 
   Q1.CurrentDate,
   Q1.Computer_Name, 
   Q1.Disk_ID, 
   Q1.CurrentSize,
   Q1.CurrentUsed,
   (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft,
   Q2.DailyDelta,
   Int(CurrentLeft/Q2.DailyDelta) AS EstDaysLeft,
   DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
FROM 
   UsageDeltas AS Q1
   INNER JOIN
     (SELECT 
          Computer_Name,
          Disk_ID,
          AVG(ChgInUsed/ChgInDate) AS DailyDelta,
          MAX(CurrentDate) As MaxDate
       FROM UsageDeltas
       GROUP BY Computer_Name, Disk_ID
      ) AS Q2
   ON  Q1.Computer_Name = Q2.Computer_Name
   AND Q1.Disk_ID = Q2.Disk_ID
   AND Q1.CurrentDate = Q2.MaxDate
WHERE
DailyDelta>0

The next thing I will do is go through the SQL and right comments to ensure I understand it properly (like I did before); would it be ok if I send this to you and check that I've understood properly?

Regarding the below; thank you! I think I will do the reading of this forum and see what solutions people are asking for. hopefully I'll be able to help someone one day, like you've helped me! Unfortunately I'm not in a technical role so I don't get the chance to do this everyday. it's just a hobby!

LEARNING STRATEGIES

I learned SQL by successive approximation, (A) in theory at college three decades back (B) on the job supporting mainframe DB2 two decades back (C) On the job writing mainframe DB2 and Access SQL one decade back (D) with lots of practice doing Access SQL over the last three years at my current job and here on the forum.

Once you have basic familiarity, it's pure repetition (with variation) that builds mastery. So, read each "query" and "report" post on this forum or another, posters asking for SQL or posing problems with SQL, and try to understand each solution.

Or, better, take each post on the forum asking for SQL, that you ALMOST know how to do, and do it for them. Make your best attempt, either write aircode or test your real code in a junk database. Just make some mistakes and learn from them.

Focus on what you can ALMOST do without research. Nail that down, then move on to items that you can do with QUICK research.

P.S. I've never been able to find a way.... but is there a way to annotate SQL in Access?

[dr4ke1990]

Also, Ive just noticed that one of the entries in the new query has

#Func!

in the EstFullDate column. I assume this is because the EstDaysLeft figure is too big to compute (3178559)?

[Dal Jeanis]

Yeah, that would do it. You could substitute something like this, which would give at most one day per meg available, even if less than one meg was being used on average.

Int(CurrentLeft/IIF(Q2.DailyDelta>1,Q2.DailyDelta,1) AS EstDaysLeft,

[dr4ke1990]

And that is basically saying to divide CurrentLeft/DailyDelta, if DailyDelta is bigger than 1, else divide it by 1?

any ideas why one of the values has given the #Func! error?