Hi, This is my MSSQL Code.
DECLARE @ExpCustomerJobID int = 1094
DROP TABLE IF EXISTS #TempTablePckList
CREATE TABLE #TempTablePckList (MinBoxNumber int NOT NULL, MaxBoxNumber int NOT NULL, BoxItemID int NOT NULL, NoOfBox int NOT NULL, Actual_Weight decimal(18,3) NOT NULL, VolumeWeight decimal(18,3) NOT NULL, ItemID int NOT NULL, QtyPerBox decimal(18,3) NOT NULL, Qty decimal(18,3) NOT NULL)
;WITH CTE AS (
SELECT A.BoxID as 'BoxID',
A.ExpCustomerJobID as 'ExpCustomerJobID',
A.Box_Number as 'Box_Number',
A.ItemID as 'BoxItemID',
A.Actual_Weight as 'ActualWeight',
A.Volume_Weight as 'VolumeWeight',
B.ItemID as 'ItemID',
B.Qty as 'Qty',
ROW_NUMBER() OVER (ORDER BY Box_Number) AS RowNum
FROM Exp_Customer_Jobs_Boxs A
JOIN Exp_Customer_Jobs_BoxItems B ON A.BoxID = B.BoxID
WHERE A.ExpCustomerJobID = @ExpCustomerJobID
),
CTE2 AS (
SELECT *,
Box_Number - RowNum AS Grp
FROM CTE
)
INSERT INTO #TempTablePckList (MinBoxNumber, MaxBoxNumber, BoxItemID, NoOfBox, Actual_Weight, VolumeWeight, ItemID, QtyPerBox, Qty)
SELECT
MIN(Box_Number) as 'MinBoxNumber',
MAX(Box_Number) as 'MaxBoxNumber',
BoxItemID as 'BoxItemID',
COUNT(*) as 'NoOfBox',
SUM(ActualWeight) as 'ActualWeight',
SUM(VolumeWeight) as 'VolumeWeight',
ItemID as 'ItemID',
Qty as 'QtyPerBox',
SUM(Qty) as 'Qty'
FROM CTE2
GROUP BY Grp, BoxItemID, ItemID, Qty
ORDER BY MIN(Box_Number)
SELECT T1.MinBoxNumber,
T1.MaxBoxNumber,
CASE
WHEN T1.MinBoxNumber = T1.MaxBoxNumber THEN CAST(T1.MinBoxNumber as nvarchar)
WHEN T1.MinBoxNumber < T1.MaxBoxNumber THEN CONCAT(MIN(MinBoxNumber), ' ~ ', MAX(MaxBoxNumber))
WHEN T1.MinBoxNumber > T1.MaxBoxNumber THEN 'Error'
END, T1.BoxItemID,
T1.NoOfBox,
T1.Actual_Weight,
T1.VolumeWeight,
STUFF((SELECT ', ' + CAST(T2.ItemID as nvarchar)
FROM #TempTablePckList T2
WHERE T1.MinBoxNumber = T2.MinBoxNumber
FOR XML PATH('')), 1, 1, '') as 'ItemID',
STUFF((SELECT ', ' + CAST(CONVERT(DOUBLE PRECISION,QtyPerBox) as nvarchar)
FROM #TempTablePckList T2
WHERE T1.MinBoxNumber = T2.MinBoxNumber
FOR XML PATH('')), 1, 1, '') as 'QtyPerBox',
STUFF((SELECT ', ' + CAST(CONVERT(DOUBLE PRECISION,Qty) as nvarchar)
FROM #TempTablePckList T2
WHERE T1.MinBoxNumber = T2.MinBoxNumber
FOR XML PATH('')), 1, 1, '') as 'Qty'
FROM #TempTablePckList T1
GROUP BY T1.MinBoxNumber, T1.MaxBoxNumber, T1.BoxItemID, T1.NoOfBox, T1.Actual_Weight, T1.VolumeWeight
ORDER BY T1.MinBoxNumber
