Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Group like shipment to ShipUnit based on Item, Qty

Posted on 2011-05-04
10
Medium Priority
?
342 Views
Last Modified: 2012-05-11
Looking for some help to create an update statement in SQL 2005 --

Basically we have an order details table that contains, ShipUnit, Item and Quantity

I need to know how many ShipUnit locations get the same Item and Quantity so our warehouse can
stage the items - We call this grouping like shipments.

In SQL I have tried cursors, inner joins, exists, in, not exists - even the new PIVOT command from
SQL 2005 -- kinda stuck here

All items in a MasterPO must go in the same box, - a shipment may contain multiple MasterPo's

Attached is the sample csv file, which is a snapshot of the SQL table.

sample.csv
0
Comment
Question by:simonajbart
  • 4
  • 4
  • 2
10 Comments
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35689929
Not entirely sure what you need but this is my best guess...

Select q.*,   BoxNum = ROW_NUMBER () OVER (ORDER BY "Master PO#", "Ship Unit" )
From
(
      Select "Master PO#" , "Ship Unit", Sum(Qty) as TotalQty
      From sample
      Group by  "Master PO#", "Ship Unit"
) q
0
 

Author Comment

by:simonajbart
ID: 35690200
Cool - but not entirely -  here is a better example

ShipUnit       Item             Qty
5                  XXX             1
5                  YYY            2
5                  VVV            1

6                  XXX             1
6                  YYY             2

7                  XXX              1
7                  VVV             1

8                 XXX              1
8                 YYY              2


The only two shipments above that are the same are ShipUnit 6 and ShipUnit 8 -
The other shipments are different - I effectively need to group like shipments and give the lineitems are group code.

But the shipments have to be exactly the same, Item and Qty
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35691378
Interesting problem...

Try the following
It uses an internal table to crash all the items together in a string (using FOR XML PATH ) for easy comparison.

We then open a cursor for all ungrouped shipunits and try and find a match.   If we don't we allocate the next available group id.  
We then update the underlying table and the in-memory table.

/*
update sample set GroupId = 0 where GroupId IS NULL or GroupId <> 0
*/

Drop Procedure AllocateGroupId
Go
CREATE PROCEDURE AllocateGroupId
as

Declare @myTable Table
(   ShipUnit int
  , GroupId int
  , ItemString varchar(2000)
)

Declare @myCursor CURSOR
Declare @shipUnit nvarchar(50)
Declare @nextGroupId int
Declare @groupId int
Declare @itemString nvarchar(2000)

Update sample set GroupId = 0 where GroupId is null

insert into @myTable (ShipUnit, GroupId, ItemString)
select distinct ShipUnit, GroupId
, ItemString =
(   select ',' + Item + ':' + CAST (Qty as varchar)
    from sample i
      where i.ShipUnit = sample.ShipUnit
      order by Item
      FOR  XML PATH ('')
)
from sample

Select @nextGroupId = Coalesce(MAX(GroupId),0)+ 1 from @myTable

SET @myCursor = CURSOR FAST_FORWARD
FOR
Select Distinct *
From @myTable
where GroupId = 0

OPEN @myCursor
FETCH NEXT FROM @myCursor INTO @shipUnit, @groupId, @itemString

WHILE @@FETCH_STATUS = 0
BEGIN

      select @groupId = GroupId from @myTable T
      where T.ItemString = @itemString
      and T.ShipUnit <> @shipUnit

      if Coalesce(@groupId,0) = 0
      BEGIN
            set @groupId = @nextGroupId
            set @nextGroupId = @nextGroupId+1
      END

      update sample set GroupId = @groupId where ShipUnit = @shipUnit
      update @myTable set GroupId = @groupId where ShipUnit = @shipUnit
      
      FETCH NEXT FROM @myCursor INTO @shipUnit, @groupId, @itemString
END

CLOSE @myCursor
DEALLOCATE @myCursor
GO


Exec AllocateGroupId
go
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35691381
One limitation is the 2000 char limit on the internal table.    Maybe creating it as a view would give a bit more laltitude
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35691388
Also forgot to say, I added an int colum called GroupId to your sample table
0
 

Author Comment

by:simonajbart
ID: 35691615
Thanks Paul - we are getting closer -- this is exactly the path I was headed down. I appreciate the help as always. See below

shipunit         groupid          itemstring
1      1      ,W62068-1:1,W62068-10:1,W62068-11:1
1002      243      ,W62068-1:1,W62068-10:2,W62068-11:2
1012      239      ,W62068-1:1,W62068-10:1,W62068-11:1
102      24      ,W62068-1:1,W62068-10:2,W62068-11:2
1020      233      ,W62068-1:1,W62068-10:2,W62068-11:1

here is a snippet of the insert into the #temp table - group 1 is the same as grroup 239
and group 243 is the same as group 24

They need to be the same group numbers is they have the same items and quantities

Thanks sampleout.txt
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35691957
try this query.
;WITH CTE1 
     AS (SELECT *,Item + ';' + CONVERT(VARCHAR,Qty) Item_Qty 
           FROM Order_Detail_Table), 
     CTE2 
     AS (SELECT DISTINCT [ShipUnit],STUFF((  SELECT DISTINCT ',' + CONVERT(VARCHAR,Item_Qty) 
                                               FROM CTE1 AS t2 
                                              WHERE t1.[ShipUnit] = t2.[ShipUnit] 
                                           ORDER BY ',' + CONVERT(VARCHAR,Item_Qty) 
                                           for xml path('')),1,1,'') Group_Item_Qty 
           FROM CTE1 t1), 
     CTE3 
     AS (SELECT *,COUNT(* ) 
                    OVER(PARTITION BY Group_Item_Qty ) cnt 
           FROM CTE2) 
SELECT ShipUnit 
  FROM CTE3 
 WHERE cnt > 1 

Open in new window

tested with your sample
DECLARE  @table  TABLE([ShipUnit] INT,Item VARCHAR(30),Qty INT ) 

INSERT @table 
SELECT 5,'XXX',1 
UNION ALL 
SELECT 5,'YYY',2 
UNION ALL 
SELECT 5,'VVV',1 
UNION ALL 
SELECT 6,'XXX',1 
UNION ALL 
SELECT 6,'YYY',2 
UNION ALL 
SELECT 7,'XXX',1 
UNION ALL 
SELECT 7,'VVV',1 
UNION ALL 
SELECT 8,'XXX',1 
UNION ALL 
SELECT 8,'YYY',2 

;WITH CTE1 
     AS (SELECT *,Item + ';' + CONVERT(VARCHAR,Qty) Item_Qty 
           FROM @table), 
     CTE2 
     AS (SELECT DISTINCT [ShipUnit],STUFF((  SELECT DISTINCT ',' + CONVERT(VARCHAR,Item_Qty) 
                                               FROM CTE1 AS t2 
                                              WHERE t1.[ShipUnit] = t2.[ShipUnit] 
                                           ORDER BY ',' + CONVERT(VARCHAR,Item_Qty) 
                                           for xml path('')),1,1,'') Group_Item_Qty 
           FROM CTE1 t1), 
     CTE3 
     AS (SELECT *,COUNT(* ) 
                    OVER(PARTITION BY Group_Item_Qty ) cnt 
           FROM CTE2) 
SELECT ShipUnit 
  FROM CTE3 
 WHERE cnt > 1 
/*
ShipUnit
6
8
*/

Open in new window

0
 

Author Comment

by:simonajbart
ID: 35692741
Sharath - this is almost there -- attached is what my results look like after two pivot tables from excel.
This is what we have been using to ship by -
your results are also attached - on the sheet yourresults - as you can see very close.

SELECT Count(ShipUnit), cnt
FROM CTE3
GROUP BY cnt
ORDER BY cnt desc
delinstructions.xlsx
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 35692904
I do not understand your question. I filtered for one particular Group_Item_Qty and get those Ship Units which have same combination of Item and Qty.
;WITH CTE1 
     AS (SELECT *,Item + ';' + CONVERT(VARCHAR,Qty) Item_Qty 
           FROM [sample]), 
     CTE2 
     AS (SELECT DISTINCT [Ship Unit],STUFF((  SELECT DISTINCT ',' + CONVERT(VARCHAR,Item_Qty) 
                                               FROM CTE1 AS t2 
                                              WHERE t1.[Ship Unit] = t2.[Ship Unit] 
                                           ORDER BY ',' + CONVERT(VARCHAR,Item_Qty) 
                                           for xml path('')),1,1,'') Group_Item_Qty 
           FROM CTE1 t1), 
     CTE3 
     AS (SELECT *,COUNT(* ) 
                    OVER(PARTITION BY Group_Item_Qty ) cnt 
           FROM CTE2) 
SELECT [Ship Unit]
  FROM CTE3 
  WHERE cnt = 7 -- and [Ship Unit] in (1389,2751,2814,1188,1948,496,237)
/*
Ship Unit
1389
2751
2814
1948
496
237
1188
Group_Item_Qty:
W61912-1;3,W61912-10;4,W61912-11;2,W61912-15;1,W61912-16;3,W61912-2;3,W61912-3;2,W61912-4;1,W61912-5;2,W61912-6;1,W61912-7;6,W61912-8;6,W61912-9;4,W62068-1;2,W62068-10;4,W62068-11;4,W62068-2;2,W62068-3;2,W62068-4;2,W62068-5;2,W62068-6;4,W62068-7;4,W62068-8;4,W62068-9;4
*/

Open in new window

0
 

Author Comment

by:simonajbart
ID: 35693345
You were spot on Sharath - my sincere thanks to both you guys, you got me out of a bind.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question