simplify report generation by inserting bogus records with zero as value?

In a table I call 'Generation,' there are columns for Plant ID, Fuel, Consumption (the number of fuel units consumed to generate the number of MWh in the 'Generation' field), and MWh(Megawatt Hours). The various columns in the Generation table establish relationships with other tables that allow me to (through joins) display generation and consumption summed by Plant, Plant Operator, State, SubRegion, and Region.

The challenge is that, in many of the summations, it is desirable to display the totals by fuel and, for comparison purposes, return the same number of records for each entity by which the data is summed. So, for example, I want a query that returns summed data by state, but I want to make sure there is a state/fuel record for all fuels. If there is no use of a particular fuel in a particualr state (some states have no nuclear plants, most have no wind or solar powered plants), I still want a record for that fuel that corresponds to that state--but of course I want it have a zero value.

I can think of a few different way to do this. One that I have tried involves rather elaborate UNION queries that return zero value records where no records for a particular summing entity/fuel combination exist. This works but is cumbersome (hard to maintain the queries, less efficient execution).

An idea I had was to insert bogus records into the 'Generation' table for each potential summing entity (Plant Operator, State, SubRegion, Region)/fuel combination that would have a zero value in the 'MWh' and 'Consumption' columns. This would automatically generate a zero value for any summing entity/fuel combinations in aggregate queries where no other non-zero record exists, and wouldn't adversly effect queries where real records exist.

The only downside to this approach I can think of are that I will have to write a fairly complex INSERT query to make sure I cover all possible combinations of entity/fuel if I want to make sure this will always work--but I only have to do this once. But, it just seems like it's not a good idea to have bogus records in the table (even though they would be named such that their purpose would be obvious).

I'd appreciate opinions on the workability of this solution or suggested alternatives.

Thanks in advance,

ebradley4_at_yahoo.com Received on Tue Apr 23 2002 - 03:12:07 CEST