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?
August 5, 2002 at 10:04 am
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?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply