June 3, 2010 at 11:24 am
I have a program that attempts to estimate how items will be packaged into boxes based on a number of fields on our part table (weight, case qty, pallet qty, package type, national motorfreight code (nmfc)). Originally I designed this in T-SQL because it was based on database data, and I thought I could benefit from set-based logic, since the shipment was really just one big set of data that I wanted to convert into another set of data. T-SQL seemed like a much better choice than a procedural language where I would have to loop through everything, over and over.
It turns out, however, that the T-SQL code is littered with cursors and loops. While I am able to benefit from set-based operations in aggragates and groupings, about half of the time I'm unable to find anything better than a good old fashioned loop to solve the problems I am faced with. At one point, I'm actually using a second cursor inside a cursor loop. It's pretty ugly, and its quickly transformed into over 700 lines of T-SQL code (with liberal commenting because I need to be able to maintain it).
Allow me to attempt to recreate the logic:
CREATE TABLE temp_parttable (
PartID NVARCHAR(100),
Weight DECIMAL(32,4),
CaseQty DECIMAL(14,4),
PalletQty DECIMAL(14,4),
Loc NVARCHAR(5),
[Full] INT,
FullWeight DECIMAL(32,4),
[Partial] INT,
PartialWeight DECIMAL(32,4),
PartialPercent DECIMAL(14,8),
Extra INT,
ExtraWeight DECIMAL(32,4),
Individual INT,
PackageType NVARCHAR(5),
NMFCode NVARCHAR(15),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
INSERT INTO temp_parttable
SELECT 'SMALL PART','7.0000','4.0000','12.0000','PA','2','336.0000','6','168.0000','0.50000000','0','0.0000','0','00290',NULL,'19.7500','18.7500','14.0000' UNION ALL
SELECT 'MED PART','8.0000','6.0000','15.0000','PA','0','720.0000','11','560.0000','0.80000000','4','32.0000','0','23141',NULL,'23.0000','14.0000','16.0000' UNION ALL
SELECT 'LARGE PART','250.0000','1.0000','3.0000','PA','1','750.0000','1','250.0000','0.33333300','0','0.0000','0','00238',NULL,'70.0000','52.0000','12.0000' UNION ALL
SELECT 'LARGE INDIVIDUAL PART','90.0000',NULL,NULL,'PA',NULL,NULL,'0',NULL,NULL,'0','0.0000','18',NULL,NULL,NULL,NULL,NULL UNION ALL
At this point is has already parsed the part list (fed from the application as a delimited string) and gathered and summarized some data about each of the parts. There's no need to go over that code - it's fairly straightforward - so I'm going to start from the middle. You'll notice from the data that it has also determined from the data whether each part should be shipped by the case or by the pallet, and calculated how many full and partial cases/pallets will be required for that item. There's also this thing I call "individual" parts, where there is no case or pallet qty, the items are divided evenly across all packages, up to a weight limit. The remaining individual items are placed wherever they fit, with new cases/pallets added if needed.
Where I'm having problems is the logic behind "allocation" as a set based operation. Take the partial packages for example. I want to allocate all of the partials until the partial percentage of the combined items is as close to 1 as possible. Once a package is allocated, it can not be allocated again. However, the packages as listed in the data above may need to be broken down into individual items in order to fit into a specific package. Generally, I want to try to fit the largest partial package possible into any case/pallet, working my way down to the smaller (easier to allocate) partials.
The code:
DECLARE @debug INT -- @debug is actually a parameter for the SP
SET @debug = 1
--------------------------------------------------------------------
-- Declare tables and variables needed for next step
--------------------------------------------------------------------
-- Several variables have to be declared
DECLARE @pkg INT
DECLARE @percent DECIMAL(14,8)
DECLARE @C INT, @i INT, @tempc INT
DECLARE @part NVARCHAR(100)
SET @pkg = 1
SET @percent = 0
-- Along with a few temporary tables
DECLARE @partial TABLE (
Package INT,
Qty INT,
NMFCode NVARCHAR(15),
Loc NVARCHAR(5),
PartID NVARCHAR(100),
Items INT,
Weight DECIMAL(32,4),
PackageType NVARCHAR(5),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
DECLARE @temp TABLE (
PartID NVARCHAR(100),
Weight DECIMAL(32,4),
CaseQty DECIMAL(14,4),
PalletQty DECIMAL(14,4),
Loc NVARCHAR(5),
[Full] INT,
FullWeight DECIMAL(32,4),
[Partial] INT,
PartialWeight DECIMAL(32,4),
PartialPercent DECIMAL(14,8),
Extra INT,
ExtraWeight DECIMAL(32,4),
Individual INT,
PackageType NVARCHAR(5),
NMFCode NVARCHAR(15),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
--------------------------------------------------------------------
-- This loop groups together partial packages
--------------------------------------------------------------------
DECLARE @nmfc NVARCHAR(15)
DECLARE c2 CURSOR FOR
SELECT NMFCode, Loc FROM temp_parttable GROUP BY NMFCode, Loc
OPEN c2
FETCH NEXT FROM c2 INTO @nmfc, @loc
-- The outer loop divides packages by NMFC and Location
WHILE @@FETCH_STATUS = 0 BEGIN
IF @debug = 1 SELECT @nmfc AS [nmfc], @loc AS [loc]
SELECT @C = SUM(Partial) FROM temp_parttable
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND PartialPercent <> 0 AND PartialPercent IS NOT NULL
IF @debug = 1 SELECT @C AS [c]
SET @i = 0
-- The inner loop iterates through all partial packages for the current
-- NMFC and Location and allocates them to boxes
WHILE @i < @C BEGIN
DELETE FROM @temp
IF @debug = 1 BEGIN
SELECT * FROM temp_parttable
END
-- Find the largest package that will fit inside the current package
INSERT INTO @temp
SELECT * FROM temp_parttable WHERE PartID IN
(SELECT TOP 1 PartID FROM temp_parttable
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND [Partial] > 0
AND PartialPercent <= (1 - @percent)
AND PartialPercent > 0 AND PartialPercent IS NOT NULL
ORDER BY PartialPercent DESC) AND Loc = @loc
IF @debug = 1 SELECT * FROM @temp
SELECT @tempc = COUNT(*) FROM @temp
-- If we found one...
IF @tempc > 0 BEGIN
-- Insert it into the package list
INSERT INTO @partial
SELECT @pkg, 1, NMFCode, Loc, PartID, [Partial] * CASE WHEN @s-2 >= 1 THEN CaseQty ELSE 1 END, Weight * [Partial],
PackageType, Length, Width, Height
FROM @temp
-- Update our state variables
SELECT @percent = @percent + PartialPercent,
@i = @i + [Partial], @part = PartID
FROM @temp
-- And update the list of packages
UPDATE temp_parttable SET [Partial] = 0, PartialPercent = 0 WHERE PartID = @part AND Loc = @loc
IF @debug = 1 BEGIN
SELECT * FROM @partial
SELECT @percent AS [percent], @i AS
END
END
-- If not...
ELSE BEGIN
-- Check to see if we can grab a single package
INSERT INTO @temp
SELECT * FROM temp_parttable WHERE PartID IN
(SELECT TOP 1 PartID FROM temp_parttable
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND [Partial] > 0
AND CASE WHEN [Partial] = 0 THEN 2 ELSE (PartialPercent / [Partial]) END <= (1 - @percent)
AND PartialPercent > 0 AND PartialPercent IS NOT NULL
ORDER BY PartialPercent DESC)
IF @debug = 1
SELECT * FROM @temp
SELECT @tempc = COUNT(*) FROM @temp
-- If we found one...
IF @tempc > 0 BEGIN
-- Insert it into our package list
INSERT INTO @partial
SELECT @pkg, 1, NMFCode, Loc, PartID, CASE WHEN @s-2 >= 1 THEN CaseQty ELSE 1 END, Weight,
NULL, NULL, NULL, NULL
FROM @temp
-- Update our state variables
SELECT @percent = @percent + PartialPercent / [Partial],
@i = @i + 1, @part = PartID
FROM @temp
-- Update our list of packages
UPDATE temp_parttable SET [Partial] = [Partial] - 1, PartialPercent = PartialPercent - PartialPercent / [Partial]
WHERE @part = PartID AND Loc = @loc
IF @debug = 1 BEGIN
SELECT * FROM @partial
SELECT @percent AS [percent], @i AS
END
END
-- If we still haven't found one...
ELSE BEGIN
-- Then this package is full
-- Advance the next package and continue allocating
SET @pkg = @pkg + 1
SET @percent = 0
END
END
END
-- Add all the partial cases to the last pallet
INSERT INTO @partial
SELECT @pkg, 1, NMFCode, Loc, PartID, Extra, ExtraWeight, PackageType, Length, Width, Height
FROM temp_parttable
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND Extra > 0
SET @pkg = @pkg + 1
SET @percent = 0
-- Grabs the next row for the outer loop
FETCH NEXT FROM c2 INTO @nmfc, @loc
END
CLOSE c2
IF @debug = 1
SELECT * FROM @partial
It's a mess and I'm sorry, but that's the point of this post. That's 200 lines of code to perform what seems like it should be a fairly simple operation.
Following this code, in the same SP, this code is fairly simple:
--------------------------------------------------------------------
-- Add in all the full packages
--------------------------------------------------------------------
INSERT INTO @partial
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) + @pkg - 1, [Full], NMFCode, Loc, PartID,
CASE WHEN @s-2 >= 1 THEN CaseQty * PalletQty ELSE CaseQty END,
FullWeight, PackageType, [Length], Width, Height
FROM @tab3
WHERE [Full] > 0
IF @debug = 1
BEGIN
SELECT '@partial' AS TableName
SELECT * FROM @partial
END
And then finally, we get the biggest mess, the code that distributes the individual items across all the packages:
DECLARE @maxweight INT -- @maxweight is actually calculated earlier in the SP, but setting it to 800 will work fine for this example
SET @maxweight = 800
--------------------------------------------------------------------
-- Declare some temporary tables
--------------------------------------------------------------------
DECLARE @alloc TABLE (
Package INT,
Individual INT,
Qty INT,
Weight DECIMAL(14,4)
)
--------------------------------------------------------------------
-- Add in all the individual items
--------------------------------------------------------------------
IF @debug >= 1
SELECT 'Add in all the individual items' AS Comment, @pkg AS [@pkg]
DECLARE icur CURSOR FOR
SELECT PartID, Weight, Individual, PackageType, NMFCode, Loc, Length, Width, Height FROM @tab3 WHERE Individual >= 1
OPEN icur
DECLARE @partid NVARCHAR(100), @weight DECIMAL(32,4), @individual INT,
@packagetype NVARCHAR(5), @nmfcode NVARCHAR(15), @length DECIMAL(14,4), @width DECIMAL(14,4), @height DECIMAL(14,4)
FETCH NEXT FROM icur INTO @partid, @weight, @individual, @packagetype, @nmfcode, @loc, @length, @width, @height
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @pkgcount INT
SELECT @pkgcount = SUM(Qty) FROM
(SELECT MAX(Qty) AS Qty, MAX(PackageType) AS PackageType, MAX(NMFCode) AS NMFCode,
MAX(Loc) AS Loc FROM @partial GROUP BY Package) A
WHERE (PackageType = @packagetype OR @packagetype IS NULL)
AND (NMFCode = @nmfcode OR (NMFCode IS NULL AND @nmfcode IS NULL))
AND Loc = Loc
SET @pkgcount = CASE WHEN COALESCE(@pkgcount,1) = 0 THEN 1 ELSE COALESCE(@pkgcount,1) END
DECLARE @per INT, @remainder INT
SET @per = @individual / @pkgcount
SET @remainder = @individual % @pkgcount
IF @debug >= 1
BEGIN
SELECT @partid AS PartID, @weight AS Weight, @individual AS Individual,
@pkgcount AS PackageCount, @per AS Per, @remainder AS Remainder
END
DELETE FROM @alloc
INSERT INTO @alloc
SELECT Package,
CASE
WHEN SUM(Weight) > @maxweight
THEN 0
WHEN SUM(Weight) + @per * @weight > @maxweight
THEN (@maxweight - SUM(Weight)) / @weight
ELSE @per
END AS Individual,
MAX(Qty) AS Qty,
SUM(Weight) + CASE
WHEN SUM(Weight) > @maxweight
THEN 0
WHEN SUM(Weight) + @individual * @weight > @maxweight
THEN ((@maxweight - SUM(Weight)) / @weight)
ELSE @per
END * @weight AS Weight
FROM @partial
WHERE (PackageType = @packagetype OR @packagetype IS NULL)
AND (NMFCode = @nmfcode OR (NMFCode IS NULL AND @nmfcode IS NULL))
AND Loc = Loc
GROUP BY Package
IF @debug >= 1
BEGIN
SELECT '@alloc' AS TableName
SELECT * FROM @alloc
END
DECLARE @left INT
SELECT @left = @individual - COALESCE(SUM(Qty * Individual),0) FROM @alloc
DECLARE pcur CURSOR FOR
SELECT Package FROM @alloc
DECLARE @package INT
OPEN pcur
FETCH NEXT FROM pcur INTO @package
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @newind INT
SELECT @newind = CASE
WHEN Weight > @maxweight THEN Individual
ELSE CASE
WHEN Weight + @left * @weight <= @maxweight THEN @left
ELSE (@maxweight - Weight) / @weight
END
END
FROM @alloc
WHERE Package = @package
IF @debug >= 1
SELECT @newind AS NewIndividual
UPDATE @alloc SET Individual = Individual + @newind WHERE Package = @package
SET @left = @left - @newind
FETCH NEXT FROM pcur INTO @package
END
CLOSE pcur
DEALLOCATE pcur
INSERT INTO @partial
SELECT Package, Qty, @nmfcode, @loc, @partid, Individual, @weight * Individual, @packagetype, @length, @width, @height
FROM @alloc
WHERE Individual > 0
IF @debug = 1
BEGIN
SELECT '@partial' AS TableName
SELECT * FROM @partial
END
IF @debug >= 1
SELECT @left AS [Left]
WHILE @left > 0
BEGIN
DECLARE @this INT
SET @this = CASE
WHEN @left * @weight > @maxweight
THEN @maxweight/@weight
ELSE @left
END
INSERT INTO @partial
SELECT @pkg+1, 1, @nmfcode, @loc, @partid, @this, @this * @weight, @packagetype, @length, @width, @height
SET @pkg = @pkg + 1
SET @left = @left - @maxweight/@weight
END
IF @debug = 1
BEGIN
SELECT '@partial' AS TableName
SELECT * FROM @partial
END
FETCH NEXT FROM icur INTO @partid, @weight, @individual, @packagetype, @nmfcode, @loc, @length, @width, @height
END
CLOSE icur
DEALLOCATE icur
IF @debug = 1
SELECT * FROM @partial
I'm wondering if there's an easier way to get at what I'm looking for. Suprisingly, this is actually reasonably fast for the purpose it was intended. It takes less than a second, then I use the data to connect to a UPS web service to get a rate. The web service connection actually takes several seconds, so the fraction of a second spent on SQL is neglibile. However, this code is unwieldy and difficult to maintain. In the last week I've had at least a half a dozen bugs that were the result of a miniscule typo somewhere in the 700 lines. Literally, neglecting a set of parenthesis caused a order or precidence error that cause a whole class of orders to evaluate incorrectly. At one point, I neglected to clear out the @alloc table, creating loads of erroneous data. This leads me to believe that those two large chunks of code need to be refactored, but I'm not sure where to start...
--J
June 4, 2010 at 2:24 am
Hello,
this is quite interesting problem, but I wasn't able to understand the table. Could you please explain in words, what the data in temp_parttable mean?
I mean, if I look at the table, I have absolutely no idea, how many pieces of which product you want to ship, what are these "full" and "partial" columns, whether the dimensions (length etc.) apply to 1 piece or some package or the whole amount of the row etc.etc. Please just take the columns that enter calculations and explain their meaning, and then describe what at least one of the rows means practically.
BTW, your insert into temp_parttable ends with "UNION ALL" - I suppose that's just a typo, but maybe there are some missing rows behind it?
Generally, similar problems can often be solved with a set-based approach using ranking functions (ROW_NUMBER())... but so far I can't tell whether it is applicable in this particular case. I can't promise that I'll have time to work on it, let alone solve it, but I'll try during the the weekend, if you give me some more info.
June 4, 2010 at 5:43 am
Length, Width and Height are the same for every distinct package type, but the data for that comes from somewhere else so I grab it all at once. Two items of the same package type can be grouped together, but not two items with different package types. An item with a NULL package type can be grouped with anything.
Once I have qty, case qty and pallet qty, I turn that number into full cases (floor of qty/case qty), and the remainder becomes a partial case (qty mod case qty). Full pallets and partial pallets use the same calculatation based on pallet qty. The percentages are how full the partials are (partial case/case qty). Partial cases/pallets may be combined with other partials with the same package type and nmfc. They may be combined by percent. A 33% full package can be combined with a 50% full package, the package is then considered to be 83% full. Hope that makes sense.
It's a complicated problem, so there might not be a clean easy way to do it. I have a feeling the place to start is by joining the qty against a tally table on qty >= tally id. This would give me one row for every item, avoiding the second loop in the code the combines partials. This would do nothing, however (I don't believe), to help me with the individual parts code near the end. The individual parts still need to be divided evenly (which may be possible with another join), but up to a limit (which definitely complicates things).
Thanks for the help.
--J
June 5, 2010 at 4:36 am
I'm sorry, but I still don't get it.
Let's look at the first line:
Weight = 7 - that's weight of one piece, right?
CaseQty = 4, PalletQty = 12 - what is this? Does that mean you want to pack 4 cases and 12 pallets, or is it how many pieces you can fit into one case/pallet?
Loc = 'PA' ... does this indicate that this item is on a pallet?
Full = 2, Partial=6 - full and partial what? cases? pallets? It seems like the number 6 corresponds with PartialPercent 50% and PalletQty 12.. but the FullWeight and PartialWeight are absolutely confusing for me.
How come FullWeight is 720 on the second line, where Full = 0, and 15*8=120?
Extra - is this number of pieces that are "free" (not in a case or on a pallet)?
Maybe the problem is in the design of this table? It seems there are lots of things pre-summed and pre-calculated in there, and maybe it would be easier for you to work with the data in more "raw" state?
June 5, 2010 at 10:04 pm
Case Qty is the number of items that make a case. Pallet Qty is the number of cases that make a pallet.
At the point in the code where I start, it has already determined whether to use a case or a pallet (a pallet in this example). So full is the number of full pallets, and partial is the remainder. The determination of case vs. pallet is actually stored in a poorly named variable, @s-2, which I don't belive is referenced in this code. It uses it earlier to create the data you see, and it uses it later in the final return. @s-2 is actually an integer variable, storing the number of full pallets, so @s-2 = 0 means case, and @s-2 > 0 means pallet.
Loc (location; loc is a standardized abbreviation in our environment) is a legacy requirement. Originally, it was going to consolidate packages shipped from different locations. It turned out being necessary to make an entirely different request for each location. The loc field was part of the parameters, however, so it was never dropped. It still groups packages by location, although it's not required to. A loc of PA means shipment from our Pennsylvania office.
I think the code before all this would help. I'm not in a position right now where I can get at it, but I'll post it as soon as I can. It takes a delimited list of parts and quantities (and locations), and turns it into the data you see. That code would probably answer all of your questions.
Thanks again for the assistance.
--J
June 7, 2010 at 1:08 am
Ahh.. I begin to see a light.
Weight is weight of 1 piece.
CaseQty is number of pieces in a case, but PalletQty is number of CASES on a pallet.
Full is number of full pallets, Partial is number of CASES that don't fill a pallet and Extra is number of PIECES that don't fill a case.
PartialWeight is weight of (Partial+Extra)... that's a bit strange, but probably you have some reason for it.
Now all the weights are correct, finally. It could be a bit different sometimes - based on the @s-2 parameter, as you mentioned - but let's stick to this explanation, since it covers the example well.
Individual = 18 probably means that there are 18 "large individual parts" in the shipment... OK, now it seems we could start thinking about the solution.
Still one question remains: "Two items of the same package type can be grouped together, but not two items with different package types." First 3 lines have 3 different package types. Does that mean I can't mix these products on one pallet, or what do you mean by "grouping"?
June 7, 2010 at 4:41 am
This looks very much like a recent problem posed by Joe Celko.
I suggest that you will find much of interest in this link: http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-eggs-in-one-basket/
June 7, 2010 at 8:41 am
sounds like the classic bin-packing problem, or a variant of it: http://en.wikipedia.org/wiki/Bin_packing_problem
See here for some SQL Server specific goodness. Note there are multiple parts to this series, and lots of good stuff in the discussions of the various posts as well: http://sqlblog.com/blogs/hugo_kornelis/archive/2008/10/27/bin-packing-part-4-the-set-based-disaster.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 7, 2010 at 9:40 am
Thanks for all the information.
Based on the information presented, it looks like I'd have to jump through a lot of hoops (that I don't necessarily want to jump through) in order to refactor this as set-based. Even then, it looks like I may even suffer a performance penalty as a result. It's all starting to sound like more work than it's worth. This might actually be one of those time's when it's appropriate to use a cursor.
Since my goal is readibility and maintainability, rather than performance, I'm thinking I'd be best served refactoring it procedurally - breaking the code up into several functions rather than just one SP. I think that will get me what I want with the least amount of work required.
Thanks again for the help,
--J
June 8, 2010 at 1:23 am
I don't think you would suffer performance penalty, but yes, if you want to use a set-based approach, you'd probably have to start in some earlier stage of data processing and prepare the data in a different format... and that might be rather complicated, if there are some other parts of the code that rely on data being supplied in the current format.
If I have time, I'll take one more look at your code during the next weekend (now that I understand what it does) and look for some optimization possibilities without changes to data structure.
June 8, 2010 at 8:06 am
I welcome any input you can offer. Here is the stored procedure in its entirety:
CREATE PROCEDURE [dbo].[VMFG_UPSPackageConfig]
@partlist NVARCHAR(MAX),
@debug INT = 0
AS
--------------------------------------------------------------------
-- Table Declarations
--------------------------------------------------------------------
DECLARE @items TABLE (
Item VARCHAR(255)
)
DECLARE @itemlist TABLE (
PartID VARCHAR(255),
Qty INT,
Loc VARCHAR(5)
)
--------------------------------------------------------------------
-- Split Partlist into a Table
--------------------------------------------------------------------
INSERT INTO @items
SELECT Item FROM master.dbo.fn_SSRS_SplitParams(@partlist, '|')
IF @debug = 1
SELECT * FROM @items
--------------------------------------------------------------------
-- Turn the single column table into three columns
--------------------------------------------------------------------
DECLARE @item NVARCHAR(255)
DECLARE @qty NVARCHAR(255)
DECLARE @loc NVARCHAR(255)
DECLARE c CURSOR FOR
SELECT Item FROM @items
OPEN c
FETCH NEXT FROM c INTO @item
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM c INTO @qty
FETCH NEXT FROM c INTO @loc
INSERT INTO @itemlist
SELECT @item, CAST(@qty AS INT), CAST(@loc AS NVARCHAR(5))
FETCH NEXT FROM c INTO @item
END
IF @debug = 1
BEGIN
SELECT * FROM @itemlist
SELECT PartID, SUM(Qty) AS Qty FROM @itemlist GROUP BY PartID
END
CLOSE c
--------------------------------------------------------------------
-- Get the part data from Visual
--------------------------------------------------------------------
DECLARE @query VARCHAR(MAX), @finalQuery VARCHAR(MAX)
DECLARE @tab TABLE (
PartID NVARCHAR(100),
Weight DECIMAL(14,4),
CaseQty DECIMAL(14,4),
PalletQty DECIMAL(14,4),
PackageType NVARCHAR(5),
NMFCode NVARCHAR(15),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
SELECT @query =
'
select ID, WEIGHT, CASE_QTY, PALLET_QTY, PT.CODE, NMFC_CODE_ID, PT.LENGTH, PT.WIDTH, PT.HEIGHT
from PART P
left join PACKAGE_TYPE PT on coalesce(P.PACKAGE_TYPE, NULL) = PT.CODE
where ID IN (' + master.dbo.strconcat('''''' + PartID + ''''' ') +')
'
FROM @itemlist
PRINT @query
SET @finalQuery = 'SELECT * FROM OPENQUERY(VMFG, ''' + @query + ''')'
INSERT INTO @tab
EXEC(@finalQuery)
IF @debug = 1
SELECT * FROM @tab
--------------------------------------------------------------------
-- Check for a couple of error conditions
--------------------------------------------------------------------
-- No weight
DECLARE @nw INT
SELECT @nw = COUNT(*) FROM @tab WHERE Weight < 0 OR Weight IS NULL
IF @nw > 0
BEGIN
SELECT PartID, Weight,
CASE
WHEN Weight < 0 THEN 'Weight is negative'
WHEN Weight IS NULL THEN 'Weight is null'
END AS Error
FROM @tab
WHERE Weight < 0 OR Weight IS NULL
RETURN
END
--------------------------------------------------------------------
-- Now manipulate the data to get case and pallet counts
--------------------------------------------------------------------
DECLARE @tab2 TABLE (
PartID NVARCHAR(100),
Loc NVARCHAR(5),
Qty INT,
FullCases INT,
PartialCase INT,
FullCaseWeight DECIMAL(32,4),
PartialCaseWeight DECIMAL(32,4),
PartialCasePercent DECIMAL(14,8),
FullPallets INT,
PartialPallet INT,
FullPalletWeight DECIMAL(32,4),
PartialPalletWeight DECIMAL(32,4),
PartialPalletPercent DECIMAL(14,8),
Weight DECIMAL(14,4),
CaseQty DECIMAL(14,4),
PalletQty DECIMAL(14,4),
PackageType NVARCHAR(5),
NMFCode NVARCHAR(15),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
INSERT INTO @tab2
SELECT
PartID, Loc, Qty,
FullCases, PartialCase,
FullCaseWeight, PartialCaseWeight, PartialCasePercent,
FullPallets, PartialPallet,
FullPalletWeight,
PartialPallet * CaseQty * Weight + PartialCaseWeight AS PartialPalletWeight,
(PartialPallet + CASE WHEN PartialCase > 0 THEN 1 ELSE 0 END) / PalletQty AS PartialPalletPercent,
Weight, CaseQty, PalletQty, PackageType,
NMFCode, [Length], Width, Height
FROM
(SELECT
*,
PartialCase * Weight AS PartialCaseWeight,
PartialCase / CaseQty AS PartialCasePercent,
CASE
WHEN (PalletQty IS NULL OR PalletQty = 0) AND PackageType NOT IN ('00238', '00239')
THEN 0
ELSE FLOOR((FullCases + CASE WHEN PartialCase > 0 THEN 1 ELSE 0 END) / PalletQty)
END AS FullPallets,
CASE
WHEN PalletQty IS NULL OR PalletQty = 0
THEN FullCases
ELSE CAST(FullCases % PalletQty AS INT)
END AS PartialPallet,
CaseQty * PalletQty * Weight AS FullPalletWeight
FROM
(SELECT
CASE
WHEN CaseQty IS NULL OR CaseQty = 0
THEN 0
ELSE FLOOR(Qty / CaseQty)
END AS FullCases,
CASE
WHEN CaseQty IS NULL OR CaseQty = 0
THEN Qty
ELSE CAST(Qty % CaseQty AS INT)
END AS PartialCase,
CaseQty * Weight AS FullCaseWeight,
T.*, IL.Qty, IL.Loc
FROM @tab T
LEFT JOIN (SELECT PartID, SUM(Qty) AS Qty, Loc FROM @itemlist GROUP BY PartID, Loc) IL ON T.PartID = IL.PartID) A) B
IF @debug >= 1
SELECT * FROM @tab2
--------------------------------------------------------------------
-- Decide whether to ship freight or standard
--------------------------------------------------------------------
DECLARE @s-2 DECIMAL(32,8), @sf DECIMAL(32, 8)
SELECT @s-2 = SUM(FullPallets + PartialPalletPercent), @sf = SUM(FullPallets) FROM @tab2
SELECT @s-2 = @s-2 + COUNT(*) FROM @tab2 WHERE PackageType IN ('00238', '00239')
--------------------------------------------------------------------
-- Check for individual weight
--------------------------------------------------------------------
DECLARE @maxweight INT
SET @maxweight = CASE WHEN @s-2 >= 1 THEN 800 ELSE 50 END
-- Weight > 50, no case qty
SELECT @nw = COUNT(*) FROM @tab WHERE Weight > @maxweight AND CaseQty IS NULL
IF @nw > 0
BEGIN
SELECT PartID, Weight, 'Part too heavy for package shipment' AS Error
FROM @tab
WHERE Weight > @maxweight AND CaseQty IS NULL
RETURN
END
--------------------------------------------------------------------
-- Grab necessary fields based on shipment type
--------------------------------------------------------------------
IF @debug = 1
SELECT @s-2 AS s, @sf AS sf
DECLARE @tab3 TABLE (
PartID NVARCHAR(100),
Weight DECIMAL(32,4),
CaseQty DECIMAL(14,4),
PalletQty DECIMAL(14,4),
Loc NVARCHAR(5),
[Full] INT,
FullWeight DECIMAL(32,4),
[Partial] INT,
PartialWeight DECIMAL(32,4),
PartialPercent DECIMAL(14,8),
Extra INT,
ExtraWeight DECIMAL(32,4),
Individual INT,
PackageType NVARCHAR(5),
NMFCode NVARCHAR(15),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
INSERT INTO @tab3
SELECT
PartID,
Weight, CaseQty, PalletQty,
Loc,
CASE
WHEN @s-2 >= 1
THEN FullPallets
ELSE FullCases
END AS [Full],
CASE
WHEN @s-2 >= 1
THEN FullPalletWeight
ELSE FullCaseWeight
END AS [FullWeight],
CASE
WHEN @s-2 >= 1
THEN PartialPallet
ELSE PartialCase
END AS [Partial],
CASE
WHEN @s-2 >= 1
THEN PartialPalletWeight
ELSE PartialCaseWeight
END AS [PartialWeight],
CASE
WHEN @s-2 >= 1
THEN PartialPalletPercent
ELSE PartialCasePercent
END AS PartialPercent,
CASE
WHEN (@s >= 1 AND (CaseQty IS NOT NULL))
THEN PartialCase
ELSE 0
END AS Extra,
CASE
WHEN (@s >= 1 AND (CaseQty IS NOT NULL))
THEN PartialCaseWeight
ELSE 0
END AS ExtraWeight,
CASE
WHEN CaseQty IS NULL
THEN PartialCase
ELSE 0
END AS Individual,
PackageType, NMFCode, [Length], Width, Height
FROM @tab2
IF @debug = 2
BEGIN
SELECT '@tab3' AS TableName
SELECT * FROM @tab3
END
IF @debug >= 2
SELECT 'SELECT ' + COALESCE(QUOTENAME(PartID, ''''), 'NULL') + ',' +
COALESCE(QUOTENAME(Weight, ''''), 'NULL') + ',' + COALESCE(QUOTENAME(CaseQty, ''''), 'NULL') + ',' + COALESCE(QUOTENAME(PalletQty, ''''), 'NULL') + ',' +
COALESCE(QUOTENAME(Loc, ''''), 'NULL') + ',' +
COALESCE(QUOTENAME([Full], ''''), 'NULL') + ',' + COALESCE(QUOTENAME(FullWeight, ''''), 'NULL') + ',' + COALESCE(QUOTENAME(Partial, ''''), 'NULL') + ',' +
COALESCE(QUOTENAME(PartialWeight, ''''), 'NULL') + ',' + COALESCE(QUOTENAME(PartialPercent, ''''), 'NULL') + ',' +
COALESCE(QUOTENAME(Extra, ''''), 'NULL') + ',' + COALESCE(QUOTENAME(ExtraWeight, ''''), 'NULL') + ',' +
COALESCE(QUOTENAME(Individual, ''''), 'NULL') + ',' +
COALESCE(QUOTENAME(PackageType, ''''), 'NULL') + ',' + COALESCE(QUOTENAME(NMFCode, ''''), 'NULL') + ',' + COALESCE(QUOTENAME([Length], ''''), 'NULL') + ',' +
COALESCE(QUOTENAME(Width, ''''), 'NULL') + ',' + COALESCE(QUOTENAME(Height, ''''), 'NULL') + ' UNION ALL'
FROM @tab3
--------------------------------------------------------------------
-- Declare tables and variables needed for next step
--------------------------------------------------------------------
-- Several variables have to be declared
DECLARE @pkg INT
DECLARE @percent DECIMAL(14,8)
DECLARE @C INT, @i INT, @tempc INT
DECLARE @part NVARCHAR(100)
SET @pkg = 1
SET @percent = 0
-- Along with a few temporary tables
DECLARE @partial TABLE (
Package INT,
Qty INT,
NMFCode NVARCHAR(15),
Loc NVARCHAR(5),
PartID NVARCHAR(100),
Items INT,
Weight DECIMAL(32,4),
PackageType NVARCHAR(5),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
DECLARE @temp TABLE (
PartID NVARCHAR(100),
Weight DECIMAL(32,4),
CaseQty DECIMAL(14,4),
PalletQty DECIMAL(14,4),
Loc NVARCHAR(5),
[Full] INT,
FullWeight DECIMAL(32,4),
[Partial] INT,
PartialWeight DECIMAL(32,4),
PartialPercent DECIMAL(14,8),
Extra INT,
ExtraWeight DECIMAL(32,4),
Individual INT,
PackageType NVARCHAR(5),
NMFCode NVARCHAR(15),
[Length] DECIMAL(14,4),
Width DECIMAL(14,4),
Height DECIMAL(14,4)
)
--------------------------------------------------------------------
-- This loop groups together partial packages
--------------------------------------------------------------------
DECLARE @nmfc NVARCHAR(15)
DECLARE c2 CURSOR FOR
SELECT NMFCode, Loc FROM @tab3 GROUP BY NMFCode, Loc
OPEN c2
FETCH NEXT FROM c2 INTO @nmfc, @loc
-- The outer loop divides packages by NMFC and Location
WHILE @@FETCH_STATUS = 0 BEGIN
IF @debug = 1 SELECT @nmfc AS [nmfc], @loc AS [loc]
SELECT @C = SUM(Partial) FROM @tab3
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND PartialPercent <> 0 AND PartialPercent IS NOT NULL
IF @debug = 1 SELECT @C AS [c]
SET @i = 0
-- The inner loop iterates through all partial packages for the current
-- NMFC and Location and allocates them to boxes
WHILE @i < @C BEGIN
DELETE FROM @temp
IF @debug = 1 BEGIN
SELECT * FROM @tab3
END
-- Find the largest package that will fit inside the current package
INSERT INTO @temp
SELECT * FROM @tab3 WHERE PartID IN
(SELECT TOP 1 PartID FROM @tab3
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND [Partial] > 0
AND PartialPercent <= (1 - @percent)
AND PartialPercent > 0 AND PartialPercent IS NOT NULL
ORDER BY PartialPercent DESC) AND Loc = @loc
IF @debug = 1 SELECT * FROM @temp
SELECT @tempc = COUNT(*) FROM @temp
-- If we found one...
IF @tempc > 0 BEGIN
-- Insert it into the package list
INSERT INTO @partial
SELECT @pkg, 1, NMFCode, Loc, PartID, [Partial] * CASE WHEN @s-2 >= 1 THEN CaseQty ELSE 1 END, Weight * [Partial],
PackageType, Length, Width, Height
FROM @temp
-- Update our state variables
SELECT @percent = @percent + PartialPercent,
@i = @i + [Partial], @part = PartID
FROM @temp
-- And update the list of packages
UPDATE @tab3 SET [Partial] = 0, PartialPercent = 0 WHERE PartID = @part AND Loc = @loc
IF @debug = 1 BEGIN
SELECT * FROM @partial
SELECT @percent AS [percent], @i AS
END
END
-- If not...
ELSE BEGIN
-- Check to see if we can grab a single package
INSERT INTO @temp
SELECT * FROM @tab3 WHERE PartID IN
(SELECT TOP 1 PartID FROM @tab3
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND [Partial] > 0
AND CASE WHEN [Partial] = 0 THEN 2 ELSE (PartialPercent / [Partial]) END <= (1 - @percent)
AND PartialPercent > 0 AND PartialPercent IS NOT NULL
ORDER BY PartialPercent DESC)
IF @debug = 1
SELECT * FROM @temp
SELECT @tempc = COUNT(*) FROM @temp
-- If we found one...
IF @tempc > 0 BEGIN
-- Insert it into our package list
INSERT INTO @partial
SELECT @pkg, 1, NMFCode, Loc, PartID, CASE WHEN @s-2 >= 1 THEN CaseQty ELSE 1 END, Weight,
NULL, NULL, NULL, NULL
FROM @temp
-- Update our state variables
SELECT @percent = @percent + PartialPercent / [Partial],
@i = @i + 1, @part = PartID
FROM @temp
-- Update our list of packages
UPDATE @tab3 SET [Partial] = [Partial] - 1, PartialPercent = PartialPercent - PartialPercent / [Partial]
WHERE @part = PartID AND Loc = @loc
IF @debug = 1 BEGIN
SELECT * FROM @partial
SELECT @percent AS [percent], @i AS
END
END
-- If we still haven't found one...
ELSE BEGIN
-- Then this package is full
-- Advance the next package and continue allocating
SET @pkg = @pkg + 1
SET @percent = 0
END
END
END
-- Add all the partial cases to the last pallet
INSERT INTO @partial
SELECT @pkg, 1, NMFCode, Loc, PartID, Extra, ExtraWeight, PackageType, Length, Width, Height
FROM @tab3
WHERE (NMFCode = @nmfc OR (NMFCode IS NULL AND @nmfc IS NULL))
AND Loc = @loc
AND Extra > 0
SET @pkg = @pkg + 1
SET @percent = 0
-- Grabs the next row for the outer loop
FETCH NEXT FROM c2 INTO @nmfc, @loc
END
CLOSE c2
IF @debug = 1
SELECT * FROM @partial
--------------------------------------------------------------------
-- Add in all the full packages
--------------------------------------------------------------------
INSERT INTO @partial
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) + @pkg - 1, [Full], NMFCode, Loc, PartID,
CASE WHEN @s-2 >= 1 THEN CaseQty * PalletQty ELSE CaseQty END,
FullWeight, PackageType, [Length], Width, Height
FROM @tab3
WHERE [Full] > 0
IF @debug = 1
BEGIN
SELECT '@partial' AS TableName
SELECT * FROM @partial
END
--------------------------------------------------------------------
-- Declare some temporary tables
--------------------------------------------------------------------
DECLARE @alloc TABLE (
Package INT,
Individual INT,
Qty INT,
Weight DECIMAL(14,4)
)
--------------------------------------------------------------------
-- Add in all the individual items
--------------------------------------------------------------------
IF @debug >= 1
SELECT 'Add in all the individual items' AS Comment, @pkg AS [@pkg]
DECLARE icur CURSOR FOR
SELECT PartID, Weight, Individual, PackageType, NMFCode, Loc, Length, Width, Height FROM @tab3 WHERE Individual >= 1
OPEN icur
DECLARE @partid NVARCHAR(100), @weight DECIMAL(32,4), @individual INT,
@packagetype NVARCHAR(5), @nmfcode NVARCHAR(15), @length DECIMAL(14,4), @width DECIMAL(14,4), @height DECIMAL(14,4)
FETCH NEXT FROM icur INTO @partid, @weight, @individual, @packagetype, @nmfcode, @loc, @length, @width, @height
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @pkgcount INT
SELECT @pkgcount = SUM(Qty) FROM
(SELECT MAX(Qty) AS Qty, MAX(PackageType) AS PackageType, MAX(NMFCode) AS NMFCode,
MAX(Loc) AS Loc FROM @partial GROUP BY Package) A
WHERE (PackageType = @packagetype OR @packagetype IS NULL)
AND (NMFCode = @nmfcode OR (NMFCode IS NULL AND @nmfcode IS NULL))
AND Loc = Loc
SET @pkgcount = CASE WHEN COALESCE(@pkgcount,1) = 0 THEN 1 ELSE COALESCE(@pkgcount,1) END
DECLARE @per INT, @remainder INT
SET @per = @individual / @pkgcount
SET @remainder = @individual % @pkgcount
IF @debug >= 1
BEGIN
SELECT @partid AS PartID, @weight AS Weight, @individual AS Individual,
@pkgcount AS PackageCount, @per AS Per, @remainder AS Remainder
END
DELETE FROM @alloc
INSERT INTO @alloc
SELECT Package,
CASE
WHEN SUM(Weight) > @maxweight
THEN 0
WHEN SUM(Weight) + @per * @weight > @maxweight
THEN (@maxweight - SUM(Weight)) / @weight
ELSE @per
END AS Individual,
MAX(Qty) AS Qty,
SUM(Weight) + CASE
WHEN SUM(Weight) > @maxweight
THEN 0
WHEN SUM(Weight) + @individual * @weight > @maxweight
THEN ((@maxweight - SUM(Weight)) / @weight)
ELSE @per
END * @weight AS Weight
FROM @partial
WHERE (PackageType = @packagetype OR @packagetype IS NULL)
AND (NMFCode = @nmfcode OR (NMFCode IS NULL AND @nmfcode IS NULL))
AND Loc = Loc
GROUP BY Package
IF @debug >= 1
BEGIN
SELECT '@alloc' AS TableName
SELECT * FROM @alloc
END
DECLARE @left INT
SELECT @left = @individual - COALESCE(SUM(Qty * Individual),0) FROM @alloc
DECLARE pcur CURSOR FOR
SELECT Package FROM @alloc
DECLARE @package INT
OPEN pcur
FETCH NEXT FROM pcur INTO @package
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @newind INT
SELECT @newind = CASE
WHEN Weight > @maxweight THEN Individual
ELSE CASE
WHEN Weight + @left * @weight <= @maxweight THEN @left
ELSE (@maxweight - Weight) / @weight
END
END
FROM @alloc
WHERE Package = @package
IF @debug >= 1
SELECT @newind AS NewIndividual
UPDATE @alloc SET Individual = Individual + @newind WHERE Package = @package
SET @left = @left - @newind
FETCH NEXT FROM pcur INTO @package
END
CLOSE pcur
DEALLOCATE pcur
INSERT INTO @partial
SELECT Package, Qty, @nmfcode, @loc, @partid, Individual, @weight * Individual, @packagetype, @length, @width, @height
FROM @alloc
WHERE Individual > 0
IF @debug = 1
BEGIN
SELECT '@partial' AS TableName
SELECT * FROM @partial
END
IF @debug >= 1
SELECT @left AS [Left]
WHILE @left > 0
BEGIN
DECLARE @this INT
SET @this = CASE
WHEN @left * @weight > @maxweight
THEN @maxweight/@weight
ELSE @left
END
INSERT INTO @partial
SELECT @pkg+1, 1, @nmfcode, @loc, @partid, @this, @this * @weight, @packagetype, @length, @width, @height
SET @pkg = @pkg + 1
SET @left = @left - @maxweight/@weight
END
IF @debug = 1
BEGIN
SELECT '@partial' AS TableName
SELECT * FROM @partial
END
FETCH NEXT FROM icur INTO @partid, @weight, @individual, @packagetype, @nmfcode, @loc, @length, @width, @height
END
CLOSE icur
DEALLOCATE icur
IF @debug = 1
SELECT * FROM @partial
--------------------------------------------------------------------
-- Now summarize the data
--------------------------------------------------------------------
SELECT
MAX(Qty) AS Qty,
CASE WHEN @s-2 >= 1 THEN 'Pallet' ELSE 'Case' END AS PkgType,
LTRIM(master.dbo.strconcat(' ' + CAST(Items AS NVARCHAR(10)) + 'x ' + PartID + ' (' + CAST(CAST(ROUND(Weight,1) AS DECIMAL(20,1)) AS NVARCHAR(10)) + ' lbs.)')) AS [Parts],
CEILING(SUM(Weight)) AS Weight,
MAX(Loc) AS Loc,
MAX(COALESCE(NMFCode, '85')) AS NMFCode,
MAX(PackageType) AS PackageType, MAX(CEILING([Length])) AS [Length], MAX(CEILING(Width)) AS Width, MAX(CEILING(Height)) AS Height
FROM
(SELECT Package, MAX(Qty) AS Qty, MAX(NMFCode) AS NMFCode, MAX(Loc) AS Loc, PartID, SUM(Items) AS Items, SUM(Weight) AS Weight,
MAX(PackageType) AS PackageType, MAX([Length]) AS [Length], MAX(Width) AS Width, MAX(Height) AS Height
FROM @partial
GROUP BY Package, PartID) A
GROUP BY Package
It retrieves some of the data from an Oracle linked server. I'm not sure there's any way you can emulate that if you wanted to test this yourself...
It also calls a CLR aggregate (master.dbo.strconcat). The aggregate is actually just one of the sample CLRs from the BOL. It's used instead of the somewhat messier FOR XML construct to concatenate strings as an aggregate.
Thanks again,
--J
June 13, 2010 at 11:52 am
Hello,
unfortunately, I was too optimistic in saying that I already understand the data. There still is something that doesn't fit... Look at the MED PART. In the original table, you have Partial=11 + Extra=4, PartialWeight=560, ExtraWeight=32. Two things are rather confusing. First, as it seems, PartialWeight includes also ExtraWeight (560=11*6*8 + 4*8).
Second, if I run your code, these 11 "partials" (which according to weight in the original table should be 11 cases containing 6 pcs each) are allocated to package 1, but suddenly have weight = 88 (corresponds to 11 pieces, not 11 cases). However, I'm not sure about that second problem, because I had to edit the code to run it and may have introduced the error doing that.
I suppose that if your code works for you, you can stick with it. Just for fun, I played around with the packing problem. In your place, I would prefer to prepare the data in a different format. As far as I could understand the data, this should be the same order as in your example:
if object_id('tempdb..#order') is not null drop table #order
CREATE TABLE #order(id INT IDENTITY, PartID VARCHAR(100), Quantity INT)
INSERT INTO #order(PartID, Quantity)
SELECT 'SMALL PART', 120 UNION ALL
SELECT 'MED PART', 70 UNION ALL
SELECT 'LARGE PART', 4 UNION ALL
SELECT 'LARGE INDIVIDUAL PART', 18
if object_id('tempdb..#product_packs') is not null drop table #product_packs
CREATE TABLE #product_packs (PartID VARCHAR(100), Qty_case INT, Qty_pallet INT, Weight MONEY)
INSERT INTO #product_packs (PartID, Qty_case, Qty_pallet, Weight)
SELECT 'SMALL PART', 4, 48, 7 UNION ALL
SELECT 'MED PART', 6, 90, 8 UNION ALL
SELECT 'LARGE PART', 1, 3, 250 UNION ALL
SELECT 'LARGE INDIVIDUAL PART', 0, 0, 90
And this is my attempt at packing (I skipped the "large individual" parts that should be packed individually to some weight limit, since I didn't know precisely how that should work). As you'll see, I wasn't able to avoid the loop in one place - I really don't know how to find the largest item that can fit on a pallet without a loop of some kind:
if object_id('tempdb..#result') is not null drop table #result
CREATE TABLE #result(packno INT, PartID VARCHAR(100), qty INT, weight MONEY)
/*step 1 - full pallets*/
INSERT INTO #result(packno, PartID, qty)
SELECT ROW_NUMBER() OVER (ORDER BY PartID), Q.PartID, Q.qty_pallet
FROM
(SELECT o.PartID, o.quantity/p.qty_pallet as pal, p.qty_pallet
FROM #order o
JOIN #product_packs p ON p.PartID=o.PartID
WHERE p.qty_pallet > 0 and o.quantity/p.qty_pallet > 0) as Q
JOIN numbers n ON n.number <= Q.pal AND n.number > 0
/*step 2 - full cases and individual ("extra") items - ordered by size, to make it easier
to find the largest one that can be placed on a pallet
i.e. all remaining objects except those that will use weight limit ("large individual" parts)*/
if object_id('tempdb..#cases') is not null drop table #cases
CREATE TABLE #cases(PartID VARCHAR(100), CaseNo INT, CaseSize MONEY, PackNo INT, packtype CHAR(4))
INSERT INTO #cases(PartID, CaseNo, CaseSize, packtype)
SELECT temp.PartID, ROW_NUMBER() OVER (ORDER BY temp.percentage DESC), percentage, packtype
FROM
(SELECT Q.PartID, Q.percentage, Q.packtype
FROM
(SELECT o.PartID,
(o.quantity - ISNULL(alloc.allocated,0)) / p.qty_case as rest_cases,
(1.00/(p.qty_pallet/p.qty_case))*100 as percentage,
'CASE' as packtype
FROM #order o
JOIN #product_packs p ON p.PartID=o.PartID
LEFT JOIN
(select PartID, SUM(qty) as allocated
from #result r
group by PartID) as alloc ON alloc.PartID=o.PartID
WHERE p.qty_pallet > 0) as Q
JOIN numbers n ON n.number <= Q.rest_cases AND n.number > 0
UNION ALL
SELECT X.PartID, X.percentage, X.packtype
FROM
(SELECT o.PartID,
(o.quantity) % p.qty_case as rest_indi,
(1.00/(p.qty_pallet))*100 as percentage,
'INDI' as packtype
FROM #order o
JOIN #product_packs p ON p.PartID=o.PartID
WHERE p.qty_pallet > 0) as X
JOIN numbers n ON n.number <= X.rest_indi AND n.number > 0) as temp
/*start packing - (@size=0 means pallet is 0% full)*/
DECLARE @pack INT, @size money
SET @pack=1+(select max(PackNo) from #result)
SET @size=0
/*repeat until there are no unallocated rows*/
WHILE EXISTS(select * from #cases where PackNo is null)
BEGIN
/*find the biggest part that can fit onto the pallet, allocate it and update the current "fullness" of the pallet*/
UPDATE #cases
SET PackNo=@pack, @size=@size+CaseSize
WHERE CaseNo=(select min(CaseNo) from #cases where PackNo is null and CaseSize < (100.00-@size))
/*if there is no part that can fit inside current pallet, create a new empty pallet*/
IF @@ROWCOUNT = 0 SELECT @pack=@pack+1, @size=0
END
/*now insert the allocated rows into final table*/
INSERT INTO #result(packno, PartID, qty)
SELECT Q.PackNo, Q.PartID, SUM(CASE WHEN Q.packtype='CASE' THEN Q.qty*p.qty_case ELSE Q.qty END)
FROM
(SELECT PackNo, c.PartID, c.packtype, COUNT(*) as qty
FROM #cases c
GROUP BY c.PackNo, c.PartID, c.packtype) as Q
JOIN #product_packs p ON p.PartID=Q.PartID
GROUP BY Q.PackNo, Q.PartID
I decided to work with quantities in pieces in the result, but of course it wouldn't be hard to change that and report how many cases and how many individual items there are on each pallet.
Well, that's it. Maybe it will help you or give you some ideas, but even if it doesn't, I had some fun with it 🙂
Thanks!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply