Friday, April 8, 2011

Finding the T-SQL to return these values

I'm trying to write a stored procedure that will return two calculated values for each record according to the rules below, but I haven't figured out how to structure the SQL to make it happen. I'm using SQL Server 2008.

First, the relevant tables, and the fields that matter to the problem.

ProductionRuns

 RunID (key, and RunID is given to the stored proc as its parameter)
 ContainerName
 ProductName
 TemplateID

TemplateMeasurements

 MeasurementTypeID
 TemplateID

SimpleBounds

 MeasurementTypeID
 TemplateID
 UpperBound
 LowerBound

ContainerBounds

 MeasurementTypeID
 TemplateID
 UpperBound
 LowerBound
 ContainerName

ProductBounds

 MeasurementTypeID
 TemplateID
 UpperBound
 LowerBound
 ProductName

And this is what I'm trying to return. I want to return a calculated upper bound and lower bound value for each TemplateMeasurements record that has a matching TemplateID with the ProductionRuns record that has the supplied runID.

The calculated upper and lower bounds basically get the tightest bound that can be obtained as a result of the simple, container and product bounds, if they qualify.

If a SimpleBounds record exists with the correct MeasurementTypeID and TemplateID, then that becomes one of the qualifying bounds for a particular MeasurementTypeID and record of TemplateMeasurements.

For a ContainerBound record to qualify, the TemplateID and MeasurementTypeID must match, but also the ContainerName must match the value for ContainerName in the ProductionRuns record. Also for ProductBounds, the same is true, but for ProductName.

For a particular MeasurementTypeID, take all the qualifying bounds, and find the least Upper Bound, and that will be the calculated Upper Bound that is to be returned. Find the greatest Lower Bound of the qualifiers and that will be the returned Lower Bound.

I have no idea how to put together SQL to do this however.

Also, if none of the three bound tables qualify for a particular MeasurementTypeID, then null could be returned.

My thought would be some kind of left outer join, but I'm not sure how to extend that to three tables that could all have null in the results.

Thanks for the help.

From stackoverflow
  • I don't have time to test this right now, but hopefully this will get you pretty close:

    SELECT
         PR.RunID,
         PR.TemplateID,
         CASE
              WHEN MAX(SB.LowerBound) > MAX(CB.LowerBound) AND
                          MAX(SB.LowerBound) > MAX(PB.LowerBound) THEN MAX(SB.LowerBound)
              WHEN MAX(CB.LowerBound) > MAX(PB.LowerBound) THEN MAX(CB.LowerBound)
              ELSE MAX(PB.LowerBound)
         END AS LowerBound,
         CASE
              WHEN MIN(SB.UpperBound) < MIN(CB. UpperBound) AND
                          MIN(SB. UpperBound) < MIN(PB. UpperBound) THEN MIN(SB. UpperBound)
              WHEN MIN(CB. UpperBound) < MIN(PB. UpperBound) THEN MIN(CB. UpperBound)
              ELSE MIN(PB. UpperBound)
         END
    FROM
         ProductionRuns PR
    INNER JOIN TemplateMeasurements TM ON
          TM.TemplateID = PR.TemplateID
    LEFT OUTER JOIN SimpleBounds SB ON
         SB.TemplateID = PR.TemplateID AND
         SB.MeasurementTypeID = TM.MeasurementTypeID
    LEFT OUTER JOIN ContainerBounds CB ON
         CB.TemplateID = PR.TemplateID AND
         CB.MeasurementTypeID = TM.MeasurementTypeID AND
         CB.ContainerName = PR.ContainerName
    LEFT OUTER JOIN ProductBounds PB ON
         PB.TemplateID = PR.TemplateID AND
         PB.MeasurementTypeID = TM.MeasurementTypeID AND
         PB.ProductName = PR.ProductName
    GROUP BY
         PR.RunID,
         PR.TemplateID
    
    Tony Peterson : Thanks for the code. I will take a look at it. ProductBounds needs to use PB.ProductName = PR.ProductName but other than that it looks great!
    Tom H. : Ahh, cut and paste error... I'll correct it now
  • Not to take away from Tom H.'s answer, but you might also consider approaching this problem with unions instead of joins to help split up the different upper/lower rules. It depends on how you think the queries will need to change (if at all) in the future.

    The query ends up looking cleaner, especially without all the CASE rules, but it might not be as useful in cases when TemplateMeasurement rows don't exist.

    SELECT RunID, TemplateID, MIN(UpperBound), MAX(LowerBound)
    FROM
    
      (SELECT PR.RunID, SB.TemplateID, SB.UpperBound, SB.LowerBound
      FROM SimpleBounds SB
      INNER JOIN TemplateMeasurements TM
          ON  SB.TemplateID = TM.TemplateID
          AND SB.MeasurementTypeID = TM.MeasurementTypeID
      INNER JOIN ProductionRuns PR
          ON  TM.TemplateID = PR.TemplateID)
    
    UNION
    
      (SELECT PR.RunID, CB.TemplateID, CB.UpperBound, CB.LowerBound
      FROM ContainerBounds CB
      INNER JOIN TemplateMeasurements TM
          ON  CB.TemplateID = TM.TemplateID
          AND CB.MeasurementTypeID = TM.MeasurementTypeID
      INNER JOIN ProductionRuns PR
          ON  TM.TemplateID = PR.TemplateID
          AND CB.ContainerName = PR.ContainerName)
    
    UNION
    
      (SELECT PR.RunID, PB.TemplateID, PB.UpperBound, PB.LowerBound
      FROM ProductBounds PB
      INNER JOIN TemplateMeasurements TM
          ON  PB.TemplateID = TM.TemplateID
          AND PB.MeasurementTypeID = TM.MeasurementTypeID
      INNER JOIN ProductionRuns PR
          ON  TM.TemplateID = PR.TemplateID
          AND PB.ProductName = PR.ProductName)
    
    GROUP BY RunID, TemplateID
    
    Tony Peterson : I've never used unions before. I will have to look up how they work. Thanks for the code.
  • You've got other answers already that should work, but in my opinion this type of UNIONed inner query can result in the cleanest-looking, most-maintainable way to collapse a horizontal hierarchy into a vertical one, which is basically your problem:

    SELECT MIN(iq.upperbound), MAX(iq.lowerbound)
    FROM TemplateMeasurements tm
        INNER JOIN ProductionRuns pr ON tm.TemplateID = pr.TemplateID
        LEFT JOIN
        (
        SELECT sb.UpperBound, sb.LowerBound, sb.MeasurementTypeID, '' as Name, 'sb' as Type, sb.TemplateID
        FROM SimpleBounds sb 
        UNION ALL
        SELECT cb.UpperBound, cb.LowerBound, cb.MeasurementTypeID, cb.ContainerName as Name, 'cb' as Type, cb.TemplateID
        FROM ContainerBounds cb 
        UNION ALL
        SELECT pb.UpperBound, pb.LowerBound, pb.MeasurementTypeID, pb.ProductName as Name, 'pb' as Type, pb.TemplateID
        FROM ProductBounds pb 
        ) iq ON iq.MeasurementTypeID = tm.MeasurementTypeID 
            AND iq.TemplateID = tm.TemplateID 
            AND iq.Name = 
                CASE iq.Type 
                 WHEN 'sb' THEN iq.Name 
                 WHEN 'cb' THEN pr.ContainerName 
                 WHEN 'pb' THEN pr.ProductName 
                END
        WHERE pr.RunID = @runid
        GROUP BY tm.TemplateID, tm.MeasurementTypeID
    
  • Thanks for leading me in the right direction. I had to fiddle with the problem for a while before I got it tweaked just right, but it works great now.

    My final code and results:

     ALTER PROCEDURE [dbo].[GetBounds]
     @runID int
     AS
     BEGIN
        SET NOCOUNT ON;
        DECLARE @templateID int
        SET @templateID = (SELECT TOP(1) TemplateID 
        FROM ProductionRuns WHERE RunID = @runID);
    
        SELECT TM.MeasurementTypeID,
    
        CASE 
        WHEN MIN(SB.UpperBound) < MIN(PB.UpperBound) 
        AND MIN(SB.UpperBound) < MIN(CB.UpperBound) THEN MIN(SB.UpperBound)
        WHEN MIN(PB.UpperBound) < MIN(SB.UpperBound) 
        AND MIN(PB.UpperBound) < MIN(CB.UpperBound) THEN MIN(PB.UpperBound)
        WHEN MIN(CB.UpperBound) < MIN(SB.UpperBound) 
        AND MIN(CB.UpperBound) < MIN(PB.UpperBound) THEN MIN(CB.UpperBound)
        ELSE MIN(SB.UpperBound) 
        END AS 'UpperBound',
    
        CASE
        WHEN MAX(SB.LowerBound) > MAX(PB.LowerBound) 
        AND MAX(SB.LowerBound) > MAX(CB.LowerBound) THEN MAX(SB.LowerBound)
        WHEN MAX(PB.LowerBound) > MAX(SB.LowerBound) 
        AND MAX(PB.LowerBound) > MAX(CB.LowerBound) THEN MAX(PB.LowerBound)
        WHEN MAX(CB.LowerBound) > MAX(SB.LowerBound) 
        AND MAX(CB.LowerBound) > MAX(PB.LowerBound) THEN MAX(CB.LowerBound)
        ELSE MAX(SB.LowerBound)
        END AS 'LowerBound'
    
        FROM
        ProductionRuns PR
        INNER JOIN TemplateMeasurements TM ON
        TM.TemplateID = PR.TemplateID
        LEFT OUTER JOIN SimpleBounds SB ON
        SB.TemplateID = PR.TemplateID AND
        SB.MeasurementTypeID = TM.MeasurementTypeID
        LEFT OUTER JOIN ContainerBounds CB ON
        CB.TemplateID = PR.TemplateID AND
        CB.MeasurementTypeID = TM.MeasurementTypeID AND
        CB.ContainerName = PR.ContainerName
        LEFT OUTER JOIN ProductBounds PB ON
        PB.TemplateID = PR.TemplateID AND
        PB.MeasurementTypeID = TM.MeasurementTypeID AND
        PB.ProductName = PR.ProductName 
    
        WHERE TM.TemplateID = @templateID
    
        GROUP BY
        TM.MeasurementTypeID
     END
    

    Partial results for a particular case, RunID = 3249 (TemplateID = 2)

    MeasurementTypeID   UpperBound LowerBound
    2   NULL NULL
    11  4 2.5
    18  30 1
    20  40 10
    33  99 0
    36  200 140
    42  120 32
    ...
    

0 comments:

Post a Comment