SQL 3D Volumetric Banding Calulations

Question

I Come across below problem and not sure how to get it sorted: We have a dimensions of the boxes in database: L,W,H

As the box may be measured in random position , for example the values for Lenght may be allocated in to Height column etc. Now , we have a band for boxes which contains two conditions regarding the dimensions:

Band: Every Box with Maximum of 24x34x6 OR Every Box with Maximum of 21x21x21

To calculate this by AND , OR statements will be possible but we will end up with 100's of lines of code only for first condition as the number of combinations will be 27 and then this will need to by applied in sql statement for each of the columns H,L,W. I managed to get the total number of combinations by cross join but how to apply that to CASE statement and get reasonable performance and amount of code ?

CREATE  TABLE #XMS1
( XMS1 int)  
INSERT #XMS1 select 6
INSERT #XMS1 select 24
INSERT #XMS1 select 34

select 
a.XMS1 as H,
b.XMS1 as L,
c.XMS1 as W
 from #XMS1 as a 
cross join #XMS1 b
cross join #XMS1 c

The values of L,W,H can be everyting from 0 to 100 but I need to select only the lines which fits in to the band...

At least for the second box it would be quite simple `WHERE MAX(XMS1) < 21`. — Wernfried Domscheit, Mar 15 '17 at 10:41
This is not a second box - this is second condition which also qualify the box to fit in to the pot . — Daniel Hanczyc, Mar 15 '17 at 10:46

score 1 · Accepted Answer · edited May 23 '17 at 12:09

Oracle:

You can use this to order the dimensions and then just filter on smallest, middle and largest:

SELECT *
FROM   (
  SELECT LEAST( l, w, h ) AS small,
         l + w + h - LEAST( l, w, h ) - GREATEST( l, w, h ) AS middle,
         GREATEST( l, w, h ) AS large
  FROM   your_box_dimensions_table
)
WHERE  ( small <=  6 AND middle <= 24 AND large <= 36 )
OR     ( small <= 21 AND middle <= 21 AND large <= 21 ) -- or more simply: large <= 21

;

SQL Server:

Since SQL Server does not appear to have a LEAST or GREATEST function, you could just write a user-defined function for LEAST and GREATEST with three inputs and then just call that to mimic the Oracle solution.

Or:

SELECT *
FROM   (
  SELECT CASE
           WHEN l <= w AND l <= h THEN l
           WHEN w <= h            THEN w
                                  ELSE h
         END AS small,
         CASE
           WHEN w <= l AND l <= h THEN l
           WHEN h <= l AND l <= w THEN l
           WHEN l <= w AND w <= h THEN w
           WHEN h <= w AND w <= l THEN w
                                  ELSE h
         END AS middle,
         CASE
           WHEN l >= w AND l >= h THEN l
           WHEN w >= h            THEN w
                                  ELSE h
         END AS large
  FROM   your_box_dimensions_table
)
WHERE ( small <=  6 AND middle <= 24 AND large <= 36 )
OR    ( small <= 21 AND middle <= 21 AND large <= 21 ); -- or more simply: large <= 21

#XMS1 is the only example to create the table with all possible variances for first condition. The values of L,W,H can be everything from 1 to 100 — Daniel Hanczyc, Mar 15 '17 at 10:50
@DanielHanczyc If you want to filter to only get values in those bands then just move the `CASE` statement into the `WHERE` clause and use that to filter the values. The query above will order values of any magnitude so you can put 1 in or 100 in any/all dimensions. — MT0, Mar 15 '17 at 10:56

score 1 · Answer 2 · answered Mar 15 '17 at 12:31

Daniel, I think @MT0 has answered this so don't mark this as an answer I'm just simply expanding his answer into a complete example.

This first step is simply building a list of values between 1 and 100 to help create the table containing box dimensions.

DECLARE @n TABLE (n int)

INSERT INTO @n
SELECT ones.n + 10*tens.n +1
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
ORDER BY 1

Now creating a physical table Boxes to store each combination of box dimensions (1 million records)

CREATE TABLE Boxes (d1 int, d2 int, d3 int)

INSERT INTO Boxes
SELECT a.n, b.n, c.n
    FROM @n a
        cross join @n b
        cross join @n c

Now create a view over this table with three additional columns, these will hold the dimensions in size order (small medium, large) (thanks to MT0 for saving me having to type all that out!)

You could also use a physical table instead of a view but this way offers a bit more flexibility if requirements change.

CREATE VIEW BoxesExtended AS
select 
    *
    , CASE
        WHEN d1 <= d2 AND d1 <= d3 THEN d1
        WHEN d2 <= d3 THEN d2
        ELSE d3
      END AS small
     , CASE
        WHEN d2 <= d1 AND d1 <= d3 THEN d1
        WHEN d3 <= d1 AND d1 <= d2 THEN d1
        WHEN d1 <= d2 AND d2 <= d3 THEN d2
        WHEN d3 <= d2 AND d2 <= d1 THEN d2
        ELSE d3
       END AS middle
     , CASE
        WHEN d1 >= d2 AND d1 >= d3 THEN d1
        WHEN d2 >= d3 THEN d2
        ELSE d3
        END AS large
 from Boxes

Now all we have to do is query the view to get the boxes that match your criteria

SELECT * 
    FROM BoxesExtended
    WHERE (large <=34 and middle <=24 and small <=6)
        OR (large <=21 and middle <=21 and small <=21)

You could extend this by having a 'Bands' table along the lines of

BandID   BandName   MaxSmall   MaxMiddle   MaxLarge
1        BandA      6          24          36
2        BandA      21         21          21
3        BandB      30         40          50

and then join this to the BoxesExtended view. This would allow you to define bands and then query against the view to get matching boxes within each BandName

SQL 3D Volumetric Banding Calulations

2 Answers2