Aggregate on Aggregate subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Aggregate on Aggregate subquery

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-01-21 : 14:21:31

Is there a good way to do an AVG of a MAX value? I'm trying to get the average number of days it takes to receive paperwork from a vendor after the assignment is completed. I can use:

SELECT ASGN_ID, ASGN_NAME, ORD_NUM, DEL_DATE, INV_DATE, DATEDIFF(dd,DEL_DATE, INV_DATE)
FROM INVOICE

to get the number of days between the Delivery and the Invoice dates. The problem is we have vendors that turn in paperwork at different times for one order. So INV_DATE above, is replaced with (SELECT MAX(INV_DATE) FROM INVOICE WHERE ORD_NUM = ORD_NUM AND ASGN_ID = ASGN_ID)

Which works fine and grabs the latest date of an invoice for that order done by that vendor. When I replace INV_DATE with the MAX in the DATEDIFF selection, it works fine too.

My problem is when I try to get an average of the datediff, I get the Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I want to be able to get an average delay between the two dates which I can do using temp tables to load up the MAX before and then average later, but the query takes a while to run (23 sec) and when I run it through the analyzer, the two temp tables (one date range specified by user parameters, the other is YTD) take up nearly all the processing and I get a Missing Index recommendation which is understandable since my temp tables aren't indexed.

Any thoughts?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-21 : 15:00:53

My first thought is to just make your query with the MAX in it a derived table/inline view. But, I'm not 100% that will work.

Please post sample data in a consumable format along with your expected results and we should be able to help you quicker.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 07:03:21

Sounds to me as need of derived table to get record with max invdate for vendor and then applying datediff over it to get days elapsed. Finally you can apply GROUP BY on vendor fields and do AVG(dayselapsed)
ie like


SELECT ASGN_ID, ASGN_NAME, AVG(DATEDIFF(dd,DEL_DATE, INV_DATE)*1.0) AS AvgDays
FROM
(
SELECT ASGN_ID, ASGN_NAME, ORD_NUM, DEL_DATE, INV_DATE,ROW_NUMBER() OVER (PARTITION BY ASGN_ID, ORD_NUM ORDER BY INV_DATE DESC) AS Seq
FROM INVOICE
)t
WHERE Seq=1
GROUP BY ASGN_ID, ASGN_NAME

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-01-22 : 10:55:58

That derived table works, I hadn't thought about the ROW_NUMBER PARTITION and then joining where the result was 1 to get the top result instead of MAX.

I ended up using the original query to allow the user to pick a date range, and then I used the derived table as the last column to give a static YTD value (this is going into a SSRS report so they won't know that the YTD Average is being returned on every row) so right after the Where Seq=1 I added a AND T.ASGN_ID = P.ASGN_ID to put the YTD value on the row that matches based on the ASGN_ID and that took forever to run (2 Mins)

SELECT ASGN_ID, ASGN_NAME, ORD_NUM, DEL_DATE, (SELECT MAX(INV_DATE) FROM INVOICE WHERE ORD_NUM = ORD_NUM AND ASGN_ID = ASGN_ID), DATEDIFF(dd,DEL_DATE, (SELECT MAX(INV_DATE) FROM INVOICE WHERE ORD_NUM = ORD_NUM AND ASGN_ID = ASGN_ID)) (SELECT ASGN_ID, ASGN_NAME, AVG(DATEDIFF(dd,DEL_DATE, INV_DATE)*1.0) AS AvgDays
FROM
(
SELECT ASGN_ID, ASGN_NAME, ORD_NUM, DEL_DATE, INV_DATE,ROW_NUMBER() OVER (PARTITION BY ASGN_ID, ORD_NUM ORDER BY INV_DATE DESC) AS Seq
FROM INVOICE
)t
WHERE Seq=1 AND T.ASGN_ID = P.ASGN_ID
GROUP BY ASGN_ID, ASGN_NAME)
FROM INVOICE
WHERE DEL_DATE >= @FROMDATE AND DEL_DATE < @TODATE

So I moved the derived table from the column, and set it up as a LEFT OUTER JOIN in the FROM section and now it runs great. Which makes sense (I think). When I had the table in the top as a select, it had to select all the values in the range and then compare the ASGN ID's and restrict the results on every single row. By moving it down into the FROM section, it calculated the YTD Table once and was able to just grab matching values out in under 10 sec.

SELECT ASGN_ID, ASGN_NAME, ORD_NUM, DEL_DATE, (SELECT MAX(INV_DATE) FROM INVOICE WHERE ORD_NUM = ORD_NUM AND ASGN_ID = ASGN_ID), DATEDIFF(dd,DEL_DATE, (SELECT MAX(INV_DATE) FROM INVOICE WHERE ORD_NUM = ORD_NUM AND ASGN_ID = ASGN_ID)) , YTD.AVGDAYS
FROM INVOICE LEFT OUTER JOIN
((SELECT ASGN_ID, ASGN_NAME, AVG(DATEDIFF(dd,DEL_DATE, INV_DATE)*1.0) AS AvgDays
FROM
(
SELECT ASGN_ID, ASGN_NAME, ORD_NUM, DEL_DATE, INV_DATE,ROW_NUMBER() OVER (PARTITION BY ASGN_ID, ORD_NUM ORDER BY INV_DATE DESC) AS Seq
FROM INVOICE
)t
WHERE Seq=1 AND T.ASGN_ID = P.ASGN_ID
GROUP BY ASGN_ID, ASGN_NAME)) YTD
WHERE DEL_DATE >= @FROMDATE AND DEL_DATE < @TODATE

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 05:41:19

you still have some redundant logic.
i think what you want is just this


SELECT ASGN_ID, ASGN_NAME,ORD_NUM, DEL_DATE,INV_DATE,DATEDIFF(dd,DEL_DATE, INV_DATE),
AVG(DATEDIFF(dd,DEL_DATE, INV_DATE)*1.0) OVER (PARTITION BY ASGN_ID, ASGN_NAME) AS AvgDays
FROM
(
SELECT ASGN_ID, ASGN_NAME, ORD_NUM, DEL_DATE, INV_DATE,ROW_NUMBER() OVER (PARTITION BY ASGN_ID, ORD_NUM ORDER BY INV_DATE DESC) AS Seq
FROM INVOICE
)t
WHERE Seq=1 
AND DEL_DATE >= @FROMDATE AND DEL_DATE < @TODATE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources