February 26, 2012 at 10:30 am
Is there any way to take an aggregate of an aggregate? I get the following error:
Msg 130, Level 15, State 1, Line 25
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
This is my query:
SELECT DISTINCT avg(dbo.fn_Business_Days_Between_Dates(dbo.GetLocalDateTimeFunc((SELECT MAX(dbo.GetLocalDateTimeFunc(FA1.ReceivedDate))
FROM FileActions FA1
WHERE FA1.ActionDefID IN (61,315, 393, 400) --disburse
AND FA1.ReceivedDate IS NOT NULL
AND FA1.FileID = FM.FileID)),
dbo.GetLocalDateTimeFunc((SELECT MAX(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate))
FROM FileActions FA2
WHERE FA2.ActionDefID IN (65,70) --recording confirmed
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID))))
FROM FileMain FM
JOIN FilePartnerRel FPR ON FPR.FileID = FM.FileID
AND FPR.PartnerCompanyID = 12905
JOIN FileActions FA ON FA.FileID = FM.FileID
AND FA.ActionDefID IN (61,315, 393, 400)--disburse
AND FA.ReceivedDate IS NOT NULL
JOIN FileActions FA1 ON FA1.FileID = FM.FileID
AND FA1.ActionDefID IN (65,70) --recording confirmed
AND FA1.ReceivedDate IS NOT NULL
WHERE FA.ReceivedDate >= @PPMonth
AND FM.StatusID != 8
Essentially, I am getting the difference between 2 Dates on a list of files, than taking the average. However, each date can be on each file more than once, hence the MAX .
How do I use MAX and AVG at the same time?
Thanks for your help.
February 26, 2012 at 11:22 am
Figured it out. Had to use a derived table.
SELECT AVG(turntime) FROM
(SELECT DISTINCT Fm.filenumber, dbo.fn_Business_Days_Between_Dates(dbo.GetLocalDateTimeFunc((SELECT MAX(dbo.GetLocalDateTimeFunc(FA1.ReceivedDate))
FROM FileActions FA1
WHERE FA1.ActionDefID IN (61,315, 393, 400) --disburse
AND FA1.ReceivedDate IS NOT NULL
AND FA1.FileID = FM.FileID)),
dbo.GetLocalDateTimeFunc((SELECT MAX(dbo.GetLocalDateTimeFunc(FA2.ReceivedDate))
FROM FileActions FA2
WHERE FA2.ActionDefID IN (65,70) --recording confirmed
AND FA2.ReceivedDate IS NOT NULL
AND FA2.FileID = FM.FileID))) * 1.0 as turntime
FROM FileMain FM
JOIN FilePartnerRel FPR ON FPR.FileID = FM.FileID
AND FPR.PartnerCompanyID = 12905
JOIN FileActions FA ON FA.FileID = FM.FileID
AND FA.ActionDefID IN (61,315, 393, 400)--disburse
AND FA.ReceivedDate IS NOT NULL
JOIN FileActions FA1 ON FA1.FileID = FM.FileID
AND FA1.ActionDefID IN (65,70) --recording confirmed
AND FA1.ReceivedDate IS NOT NULL
WHERE FA.ReceivedDate >= @PPMonth
AND FM.StatusID != 8)X
February 26, 2012 at 12:28 pm
Glad you sorted it 😉
But.. I have to say your over reliance on UDFs here could be causing a large performance issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply