Trying to average last 3 records only (top 3)

[Dbc23]

So I'm trying to create a query that will produce an average (totals) column based on the TOP 3 records associated with two other key field values. Basically an average of the three most recent events. I can easily create the query to do the OVERALL averages, but whenever I try to use the built-in TOP N tool it only returns the top 3 accounts or locations, not an average of the TOP 3 results for each account and location.

Here's the code I've got:

SELECT dbo_CompletedAccountDrives.accountid, dbo_rpt_DriveMaster.LocationID,  [B]Round(Avg([dbo_DriveProjectionAndCollectedTotals]![ProductsCollected]),0) AS Products[/B]
FROM (dbo_rpt_DriveMaster INNER JOIN dbo_DriveProjectionAndCollectedTotals ON dbo_rpt_DriveMaster.DriveID = dbo_DriveProjectionAndCollectedTotals.DriveID) INNER JOIN dbo_CompletedAccountDrives ON dbo_rpt_DriveMaster.AccountID = dbo_CompletedAccountDrives.accountid
GROUP BY dbo_CompletedAccountDrives.accountid, dbo_rpt_DriveMaster.LocationID
ORDER BY dbo_CompletedAccountDrives.accountid, dbo_rpt_DriveMaster.LocationID;

The bold statement in the code is what I'm trying to only pull the 3 most recent matching records to produce the average for, this has been driving me nuts for a few days trying to solve it, and it seems like it should be simple.

There's a date field in dbo_CompletedAccountDrives and I can even limit it to only results from "X" time period, but that doesn't help because my end users want last 3, regardless of time elapsed.

Any help is greatly appreciated.

 

[welshgasman]

Try using a sub query to get the top 3 records, then avg those in the main query.?

 

[Dbc23]

I tried that a few ways but couldn't seem to get it to work. Whenever I created the sub-query they would only return the top 3 records in total, so when I try linking the key fields all but 3 records came back null. Not sure what I was doing wrong there.

 

[welshgasman]

I was thinking along these lines?

https://stackoverflow.com/questions...w-to-group-by-and-top-n-records-in-same-query

https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access

HTH

 

[xenou]

Just to put a wrench in it, Top N will not always return N values because of ties.
https://support.office.com/en-gb/ar...edicates-24f2a47d-a803-4c7c-8e81-756fe298ce57

Normally this is no big deal if values are spread out and in any case users don't usually know what they want in that case anyway. Just be prepared in case of questions. But if there are a lot of ties in your data then the results to be skewed towards the most commonly occurring values if such a tie is encountered.

 

[Dbc23]

HAH, that's actually how i have it working now. It's just averaging a recent time span and giving them a number, which is a pretty good estimate. this database drives a piece of CRM front-end software that has a built-in 3 drive average calculation, so the numbers from my data never matches what they're seeing in the system and then people get suspicious about the reliability of the data, then I either have to shrug it off or explain until they fall asleep why it's such a P.I.T.A to provide.

There's even a stored procedure on the server that's supposed to be able to return that 3DA but I've never been able to successfully invoke it. I've tried pass throughs, recreating it in VBA, all sorts of stuff.

 

[xenou]

You might want to provide some sample data that illustrates the problem (and it may be that identifying such sample data would be well on the way to a solution already).

There's even a stored procedure on the server that's supposed to be able to return that 3DA but I've never been able to successfully invoke it. I've tried pass throughs, recreating it in VBA, all sorts of stuff.

Do you have the SQL (?) for this stored procedure? Do you have a connection to that server (if so, what have you tried in order to call it?)

 

[Dbc23]

I was able to pull the code for the SP and tried re-implementing it in Access, both via an access query and running a Pass-Through but neither would work, had all sorts of execution and/or connection issues even though I can connect directly to the server fine otherwise.

The SP code is:

USE xDbase
[FONT=Verdana]GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[AccountsLast3DrivesAverageProductsCollected](@AccountID int)
RETURNS numeric(8,2)
AS
BEGIN
 declare @Ret numeric(8,2)
 select @Ret = avg(dbo.DriveProductCollection(driveid))
 from rpt_drivemaster
 where driveid in
    (select top 3 driveid from completedaccountdrives where accountid = @AccountID order by fromdatetime desc)
 
 if @Ret is null
  set @Ret = 0
 return @Ret
END
GO[/FONT]

I'll add that the code in the above procedure doesn't actually do exactly what I want/need it to and it's poor design on the part of the creator because there's a sub-template to "AccountID" that I want to also include as a layer for the average. So it should be TOP 3 records in DriveProductCollection by AccountID and LocationID (locationID being the sub template of account).

 

[xenou]

Okay, well in this case the stored procedure is useless since it doesn't do what you want anyway. I was under the mistaken impression that getting that result was the goal. So I think you can go back to post number 4 above as the next step.