Good day!
Need help re SUM and JOIN 2 columns with different tables but it duplicates results
here's the sample data tables;
Transfer Shipment table;
Loc Code - Item No - Description - Description2 - Variant Code - Qty
Location1 Item01 Active Black Variant01 1
Location1 Item01 Active Black Variant01 2
Location1 Item01 Active Black Variant02 1
Location1 Item01 Active Black Variant02 1
Sales Entry table;
Loc Code - Item No - Variant Code - Qty
Location1 Item01 Variant01 -1
Location1 Item01 Variant01 -1
SUM and JOIN (the sql query result should be like this)
Loc Code - Item No - Description - Description2 - Variant Code -Transfer(Qty) - Sales(Qty) - Total
Location1 Item01 Active Black Variant01 3 -2 1
Location1 Item01 Active Black Variant02 2 0 2
here's my sql query code
SELECT
MAX([BBI$Transfer Shipment Line].[Transfer-to Code]) AS [Loc Code]
,MAX([BBI$Transfer Shipment Line].[Item No_])
,MAX([BBI$Transfer Shipment Line].[Description])
,MAX([BBI$Transfer Shipment Line].[Description 2])
,MAX([BBI$Transfer Shipment Line].[Variant Code])
,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Transfer Order]
,COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS [Sales Entry]
,SUM([BBI$Transfer Shipment Line].[Quantity])+COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS Total
FROM [BBI$Transfer Shipment Line]
LEFT JOIN [BBI$Trans_ Sales Entry]
ON [BBI$Transfer Shipment Line].[Transfer-to Code]=[BBI$Trans_ Sales Entry].[Store No_] AND
[BBI$Transfer Shipment Line].[Item No_]=[BBI$Trans_ Sales Entry].[Item No_] AND
[BBI$Transfer Shipment Line].[Variant Code]=[BBI$Trans_ Sales Entry].[Variant Code]
WHERE
[BBI$Transfer Shipment Line].[Transfer-to Code]='LOCATION1' AND
[BBI$Transfer Shipment Line].[Item No_]='ITEM01' AND
[BBI$Transfer Shipment Line].[Variant Code]='VARIANT01'
GROUP BY
[BBI$Transfer Shipment Line].[Transfer-to Code]
,[BBI$Transfer Shipment Line].[Item No_]
,[BBI$Transfer Shipment Line].[Variant Code]
but the sql query duplicates result;
Loc Code - Item No - Description - Description2 - Variant Code -Transfer(Qty) - Sales(Qty) - Total
Location1 Item01 Active Black Variant01 9 -6 3
Thank you.
You will need to summarise the transfers and sales before joining them, the duplicate lines for each transfer and sale is the issue here as the join is producing a exploded product.
CREATE TABLE #TransferShipment
(LocCode VARCHAR(50), ItemNo VARCHAR(50), Description VARCHAR(50), Description2 VARCHAR(50), VariantCode VARCHAR(50), Qty INT)
INSERT INTO [#TransferShipment]
VALUES
('Location1','Item01','Active','Black','Variant01',1),
('Location1','Item01','Active','Black','Variant01',2),
('Location1','Item01','Active','Black','Variant02',1),
('Location1','Item01','Active','Black','Variant02',1)
CREATE TABLE #SalesEntry
(LocCode VARCHAR(50), ItemNo VARCHAR(50), VariantCode VARCHAR(50), Qty INT)
INSERT INTO [#SalesEntry]
VALUES
('Location1','Item01','Variant01',-1),
('Location1','Item01','Variant01',-1)
;WITH SumarisedTransfers AS
(
SELECT
LocCode,
ItemNo,
Description,
Description2,
VariantCode,
SUM(Qty) AS TransferQty
FROM
[#TransferShipment]
GROUP BY
LocCode,
ItemNo,
Description,
Description2,
VariantCode
),
SumarisedSales AS
(
SELECT
LocCode,
ItemNo,
VariantCode,
SUM(Qty) AS SalesQty
FROM
[#SalesEntry]
GROUP BY
[LocCode],
[ItemNo],
VariantCode
)
SELECT ST.*, ISNULL(SS.[SalesQty],0) AS SalesQty, ST.[TransferQty]-ISNULL(SS.[SalesQty],0) AS Total FROM [SumarisedTransfers] ST
LEFT JOIN [SumarisedSales] SS
ON [SS].[LocCode] = [ST].[LocCode]
AND [SS].[ItemNo] = [ST].[ItemNo]
AND [SS].[VariantCode] = [ST].[VariantCode]
DROP TABLE #TransferShipment
DROP TABLE #SalesEntry
August 30, 2020 at 4:13 am
thank you very much for your help
btw;
how can i not include the "0" (zero) from Total result
Thank you again
September 3, 2020 at 5:55 pm
Add
WHERE SS.SalesQty <> 0
to Anthony's query.
September 7, 2020 at 7:19 am
thank you very much scott arendt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply