Query to select max value from multiple entries per month

[kevnaff]

Hi All.

I have a table called PPMTarget that stores our planned preventative maintenance performance targets, when a command button is clicked. A click of the command button stores the below information:

TargetID	Date	LifeSupportTarget	LifeSupportSpread	AcuteSupportTarget	AcuteSupportSpread	LifeSupportAchieved	AcuteSupportAchieved	% Life Support	% Acute Support	% Low Risk	% Overall
1216​	27/11/2023​	75.00%​	5.00%​	50.00%​	5.00%​	85.51%​	61.16%​	​	​	​	​

This command button could be clicked once per month, sometimes it could be clicked ten times. Every month I need to send a target report to upper management. In this report we take the highest target figure for each month in the last 12 months. At the moment I am exporting the data manually in to excel and copying the highest figure in to a chart.

I was hoping to create a query to do this, however I am struggling on how to group together the target figures by month, and then take the highest figure.

I have created my query below called PPMTargetReport:

The Date criteria is set to limit to entries from the last 365 days. I have set the LifeSupportAchieved and AcuteSupportAchieved to Max. However I am still getting all of the results.

Is there a way to only select one data entry per month and take the highest LifeSupportAchieved and AcuteSupportAchieved values?

Thanks

 

[cheekybuddha]

Really you probably ought to normalise this table further with a table for targets that has a field for target type (LifeSupport, AcuteSupport etc).

However, you may be able to get the result you want with:

SELECT
  MAX(LifeSupportTarget),
  MAX(AcuteSupportTarget),
  MAX(LifeSupportAchieved),
  MAX(AcuteSupportAchieved)
FROM PPMTarget
WHERE DateStamp < Date + 1
  AND DateStamp >= Date - 365

 

[CJ_London]

would need to see some example data to illustrate what you mean by 'the highest LifeSupportAchieved and AcuteSupportAchieved values'

for example, if you have

id...LSA....ASA
1.....7........10
2....10.......7
3....10.......9
4.....9........10

which record would you choose?

 

[ebs17]

the highest target figure for each month in the last 12 months

SELECT
   Year(DateStamp) * 100 + Month(DateStamp) AS AnyMonth,
   MAX(LifeSupportTarget) AS X1,
   MAX(AcuteSupportTarget) AS X2,
   MAX(LifeSupportAchieved) AS X3,
   MAX(AcuteSupportAchieved) AS X4
FROM
   PPMTarget
WHERE
   DateStamp BETWEEN Date() - 365
      AND
   Date()
GROUP BY
   Year(DateStamp) * 100 + Month(DateStamp)

 

[cheekybuddha]

take the highest target figure for each month in the last 12 months.

Oops, missed the bit about "for each month"

Could probably also do:

SELECT
  Format(DateStamp, "yyyymm") AS YearMonth
  MAX(LifeSupportTarget),
  MAX(AcuteSupportTarget),
  MAX(LifeSupportAchieved),
  MAX(AcuteSupportAchieved)
FROM PPMTarget
WHERE DateStamp < Date + 1
  AND DateStamp >= Date - 365
GROUP BY
  Format(DateStamp, "yyyymm")
ORDER BY
  Format(DateStamp, "yyyymm")
;

 

[ebs17]

A little thought:
Format(DateStamp, "yyyymm") versus Year(DateStamp) * 100 + Month(DateStamp)
The result looks the same for both.
Format produces a string as output, the calculated expression an integer. Grouping by number instead of string will take about 20 percent less time.

 

[cheekybuddha]

Grouping by number instead of string will take about 20 percent less time.

I wonder at how many records there would be a noticeable difference in speed; also you must factor in 3 operations on the date field vs one (2 functions plus multiplication versus one function)

Good to have many ways to skin the cat.

 

[kevnaff]

would need to see some example data to illustrate what you mean by 'the highest LifeSupportAchieved and AcuteSupportAchieved values'

for example, if you have

id...LSA....ASA
1.....7........10
2....10.......7
3....10.......9
4.....9........10

which record would you choose?

Hi CJ.

I would not be looking for a record but rather the 2 highest values from each field. So the query would look at the data in the LSA field and would select 10 and then 10 from ASA field too.

Hopefully this makes senses.

Maybe it would be best for 2 queries instead of one for this as I currently use the data to create 2 different charts.

Thanks all for your help.

 

[ebs17]

Three operations against one?

1) Operations do not have the same weight. One important operation can require more effort than 10 slim operations in total.

2) How many operations are involved in grouping and extra in sorting? Every field content is compared against every field content; index usage is not possible in all cases because calculated fields are used.
Performance is simply the result here
Number of operations * byte width

I took my aforementioned experience with numbers from a measurement. Try this.

 

[kevnaff]

Thanks all for your help. I have now got the data that I need with the max for each field as seen below in a continuous form. It shows the last 12 months data in order. This is what was used for the query in the end:

SELECT
Format([DateStamp],"yyyy") AS [Year]
Format([DateStamp],"mmm") AS [Month]
Max(PPMTarget.LifeSupportAchieved) AS MaxOfLifeSupportAchieved
PPMTarget.LifeSupportTarget AS Expr1
Max(PPMTarget.AcuteSupportAchieved) AS MaxOfAcuteSupportAchieved
PPMTarget.AcuteSupportTarget AS Expr2
Format([DateStamp],"yyyymm") AS Expr3

FROM PPMTarget

WHERE (((PPMTarget.DateStamp) Between Date()-Format(Date(),"d")-365 And Date()-Format(Date(),"d")))

GROUP BY Format([DateStamp],"yyyy"), Format([DateStamp],"mmm"), PPMTarget.LifeSupportTarget, PPMTarget.AcuteSupportTarget, Format([DateStamp],"yyyymm"), Format([DateStamp],"mm")

ORDER BY Format([DateStamp],"yyyymm");

My issue is now that when I enter this data in to the chart, the chart does not show the data by the order in the query, instead it sorts it alphabetically by month. I am going to have to try and figure out why this is.