CODEWITHSUNDEEP
sqlsql-server
fracture
fracture

Reputation: 141

Issue with MAX statement on CASE column in MS SQL

I'm having trouble getting the second MAX statement in the query below to work as i would like, to give an example of the output I get at the moment;

PART                    FREE_STOCK      BIN             ME_BIN
01TSC7EM                21.0000         A1E             NULL
01TSC7EM010MLG           7.0000         A1E             NULL
01TSC7EM010MME           7.0000         218             218         
01TSC7EM010MSM           3.0000         407             NULL
01TSC7EM010MXL           4.0000         407             NULL

The first MAX for the BIN column is working as i expect with the grouping meaning that the the short part gets the BIN location with the most FREE_STOCK, I was hoping by using a case statement and following similar logic that I could also return the BIN location for a part ending '%ME' and use the same MAX behaviour to set it for the short part too.

Is this because i'm using a case statement to provide the values rather than doing it against a column? Any suggestions for how I can get this to work?

SELECT dbo.part_bins.part, SUM(dbo.part_bins.free_stock) AS FREE_STOCK, MAX(PART_BINS.BIN) AS Bin, 
       MAX(CASE WHEN part_bins.part like '%ME' then PART_BINS.BIN END) AS ME_Bin
FROM   dbo.part_bins 
WHERE  dbo.part_bins.free_stock > 0 
GROUP BY dbo.part_bins.part
UNION
SELECT part.master_part AS part, SUM(dbo.part_bins.free_stock) AS FREE_STOCK,     MAX(PART_BINS.BIN) AS Bin, 
       MAX(CASE WHEN part.master_part like '%ME' THEN PART_BINS.BIN END) AS ME_Bin
FROM   dbo.part_bins 
WHERE  dbo.part_bins.free_stock > 0                   
GROUP BY part.master_part

I'm hoping I can get the final result to be:

PART                    FREE_STOCK      BIN             ME_BIN
01TSC7EM                21.0000         A1E             218
01TSC7EM010MLG           7.0000         A1E             NULL
01TSC7EM010MME           7.0000         218             218         
01TSC7EM010MSM           3.0000         407             NULL
01TSC7EM010MXL           4.0000         407             NULL

Upvotes: 2

Views: 5404

Answers (1)

fracture
fracture

Reputation: 141

It's not pretty, but this was my final solution. I found I needed to remove the MAX CASE statement in the first select:

MAX(CASE WHEN part_bins.part like '%ME' then PART_BINS.BIN END) AS ME_Bin

And instead leave it as a blank column:

'' AS ME_Bin

This allowed the group by part.master_part to work as I had intended in conjunction with the MAX CASE statement in the second SELECT. Other changes are to account for some differences between product sizes for men and women, as well as taking into account if free stock is available none of which is really relevant to the solution.

SELECT     dbo.part_bins.part, SUM(dbo.part_bins.free_stock) AS FREE_STOCK, MAX(PART_BINS.BIN) AS Bin, '' as ME_bin

FROM         dbo.part_bins LEFT OUTER JOIN
                      dbo.part ON dbo.part_bins.part = dbo.part.part LEFT OUTER JOIN
                      dbo.stor ON dbo.part_bins.store = dbo.stor.store LEFT OUTER JOIN
                      dbo.stor_bdtl ON dbo.part_bins.store = dbo.stor_bdtl.store AND dbo.part_bins.bin = dbo.stor_bdtl.bin
WHERE     (dbo.stor.on_hand = 1) AND (dbo.part_bins.free_stock > 0) AND (dbo.stor.sales = 1) OR
                      (dbo.stor.on_hand = 1) AND (dbo.part_bins.free_stock > 0) AND (dbo.stor.production = 1)
GROUP BY dbo.part_bins.part
UNION
SELECT     part.master_part AS part, SUM(dbo.part_bins.free_stock) AS FREE_STOCK, MAX(PART_BINS.BIN) AS Bin, 
        MAX (case 
            when part.prod_group like 'ME%' AND part_bins.part like '%ME' then part_bins.bin
            when part.prod_group like 'ME%' AND part_bins.part like '%32' then part_bins.bin
            when part.prod_group like 'LA%' AND part_bins.part like '%08' then part_bins.bin
            when part.prod_group like 'LA%' AND part_bins.part like '%XS' then part_bins.bin
        end) as ME_bin

FROM         dbo.part_bins LEFT OUTER JOIN
                      dbo.part ON dbo.part_bins.part = dbo.part.part LEFT OUTER JOIN
                      dbo.stor ON dbo.part_bins.store = dbo.stor.store LEFT OUTER JOIN
                      dbo.stor_bdtl ON dbo.part_bins.store = dbo.stor_bdtl.store AND dbo.part_bins.bin = dbo.stor_bdtl.bin
WHERE     (dbo.stor.on_hand = 1) AND (dbo.part_bins.free_stock > 0) AND (dbo.stor.sales = 1) OR
                      (dbo.stor.on_hand = 1) AND (dbo.part_bins.free_stock > 0) AND (dbo.stor.production = 1)
GROUP BY part.master_part

Upvotes: 1

Related Questions