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.
| Author |
Topic |
|
PeteFromOz
Starting Member
8 Posts |
 Posted - 2012-03-19 : 23:59:49
|
Hi Folks,I know therte is a lot of posts on various forums regarding "Cannot perform an aggregate function on an expression containing an aggregate or a subquery".. but I think mine is a little different (or I have missed the point entirely)@intScanToAg = sum (dbo.fncGetWorkingDays (d.scan_date,(SELECT min(al.stamp_date) FROM viewAPPerfReporting al WHERE al.log_index IN (1008, 1018, 10503)AND al.doc_id = d.doc_id))), The udf is just a function that takes into account weekends, public holidays and returns the numner of days beween the two dates given.I originally had another scalar udf that returned the date produced by the subquery, and this worked, but with performance hit. Since I have many calls like this and huge amounts of data, the performance added up to the query taking 4+ hours to run!I cant get my head around somehow making this not a subquery since it is a parameterin the function call.I was thinking something likeselect sum(days)from (select getworkingdays(date1,date2)) but I just cant see the real solution. Am I on the right track?Any help much appreciatedCheers,Pete. |
|
|
PeteFromOz
Starting Member
8 Posts |
Posted - 2012-03-20 : 18:58:07
|
| Hi Folks,I have solved my problem!I basically took a totally different tack and created a view on the table that I need to aggigate. I then did the subqueries in the view and just added the results as extra fields. I know subqueries in views arent ideal as they proclude indexes, but the entire report runs in under 10 mins now (was hours before), so I am happy to go unindexed. Also the table is a static snapshot, so there are no updates etc.Cheers,Pete. |
 |
|
|
|
|
|
|
|
Good old Aggregate functions and subqueries