Aggregate query problem

  • 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?

  • 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