[thelist] Sum'd Union Tables

Luther, Ron Ron.Luther at hp.com
Thu Jan 5 07:25:22 CST 2006


Matt Warden noted:


>>However, as I understand it, there is never a r tuple with 
>>no matching s tuple, so groupbing by s.segment_id should work.


Hi Matt,

Mostly true enough.  However, (and I don't recall if it was 
specified in the original question or not), this kind of 
analysis is typically run for a specific time frame.

Within a specific time frame, say a month, this assumption is 
often false.  Many stores will experience returns this month 
for sales made last month, MTD 'net sales' can be negative, 
and you can have an r tuple with no matching s tuple.

Although far less elegant, I believe this problem is typically 
approached by pre-processing and creating an aggregate table 
containing the information of interest.  (Especially if you 
have a significant volume of transactions and a larger number 
of report users.) The heavy lifting from the raw data is only 
done once per reporting/data load period and the report itself 
is then a very simple and very fast 'select from aggr'.


HTH,

RonL.



More information about the thelist mailing list