Alternative to my query approach

Last Post 07 Nov 2008 03:03 AM by SwePeso. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Mihai1
New Member
New Member

--
05 Nov 2008 03:00 AM
Imagine that I have 8 tables with two colums - a customer id and the other a numeric value for customer performance, let's call it csi.
There may be customers in tabel 1 which are not to be found in table 2 and there may be customers with a recorded csi in tables 5 and 7 for instance, and so on

Data from all this tables has to be 'summarized' by csi for each customer with an id in one or more tables .

I use the following code:

I aggregate the results from to tables at a time in a temporary table with a query like:

(select a.id, sum (a.CSI + b.CSI) as 'CSI' into
##temp1
from ##tb1 a join ##tb2 b
on ( a.id = b.id )
group by a.id)
UNION
(select a.id, a.CSI
from ##tb1 a left outer join ##tb2 b
on ( a.id = b.id )
where b.id is null )
UNION
(select b.id, b.CSI as 'CSI'
from ##tb1 a right outer join ##tb2 b
on ( a.id = b.id )
where a.id is null )

And I repeat the process iterativelly for all the source and temporary tables - again two a time - until I reach the final 'aggregated' table ( result).

Is it any smarter way to reach the result ?
Thank you for any constructive suggestions.

Best regards,
SwePeso
New Member
New Member

--
07 Nov 2008 03:03 AM
Perhaps an UNION ALL is to prefer?

:-)


Acceptable Use Policy
---