Aggregate query problem

Question

Aggregate query problem

SSC-Addicted

Points: 403

August 5, 2002 at 9:21 am

I am having a problem with a SQL query used to pull a summary report from a third-party production application's data. Several of the tables record the weights of material needed to process a batch and some may have several records for each batch, therefore, the weights are summed. I have narrowed the problem down to one table where, if I exclude that table, the other summary weights are correct, but if I include it, this table's weights are correct and the others are off. Here is the major portions of the code from the stored procedure:

 INSERT @BatchEndStep 

SELECT a.Batch_ID, a.tstamp, MAX(b.Step)

FROM Mixed_Material_BatchRef a INNER JOIN Mixed_Material_Step_Interval b ON a.Batch_ID = b.Batch_ID

WHERE (a.MixerID = @MixerID AND a.tstamp BETWEEN @StartTS AND @EndTS) AND b.Command <> 4 AND b.Command <> 5

GROUP BY a.Batch_ID, a.tstamp

If I check the weights for just the problem table, everything looks OK...

 PRINT 'Checking Weigh Belt data...'

SELECT a.Batch_ID, a.tstamp, SUM(b.Weight) WeighBelt

FROM @BatchEndStep a LEFT JOIN Mixed_Material_Weigh_Belt b ON a.Batch_ID = b.Batch_ID

GROUP BY a.Batch_ID, a.tstamp

But, if I include this table in the summary query, the weights of other tables are multiplied by the number of records in problem table (for each matching Batch_ID)...

 INSERT #BatchData

SELECT a.Batch_ID, a.tstamp, b.Batch_Temp StartTemp, c.Batch_Temp DropTemp, ISNULL(SUM(d.Weight),0) StainOilWeight, 

      ISNULL(SUM(e.Weight),0) NStainOilWeight, ISNULL(SUM(f.Weight),0) BlackWeight, ISNULL(SUM(g.Weight),0) Weight_Belt, 

c.Mix_time Batch_Time,(c.Mix_time - h.MixTimeOffset) Mix_Time, c.Energy DropKWH, c.KW DropKW

FROM    @BatchEndStep a LEFT JOIN Mixed_Material_Time_Interval b ON a.Batch_ID = b.Batch_ID AND b.Number = 1

LEFT JOIN Mixed_Material_Step_Interval c ON a.Batch_ID = c.Batch_ID AND a.EndStep = c.Step 

LEFT JOIN Mixed_Material_Staining_Oils d ON a.Batch_ID = d.Batch_ID

LEFT JOIN Mixed_Material_Non_Stain_Oils e ON a.Batch_ID = e.Batch_ID

LEFT JOIN Mixed_Material_Blacks f ON a.Batch_ID = f.Batch_ID

LEFT JOIN Mixed_Material_Weigh_Belt g ON a.Batch_ID = g.Batch_ID

LEFT JOIN Mixed_Material_BatchRef h ON a.Batch_ID = h.Batch_ID

GROUP BY a.Batch_ID, a.tstamp, b.Batch_Temp, c.Batch_Temp, c.Mix_Time, h.MixTimeOffset, c.Energy, c.KW



SELECT #BatchData.* FROM #BatchData ORDER BY tstamp

The weights should be aggrgating correctly on the joined Batch_ID. Any suggestions?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

clangley SSC-Addicted Points: 403 More actions · Answer 1

I found the problem. The "problem table" contained some null values. Anyone have suggestions on rewriting this query to account for possible null records in the joined tables?