SUM and JOIN 2 columns with different tables but do not SUM Qty

Good day!

Need help to SUM and JOIN 2 columns with different tables but do no want to SUM qty and duplicates row result

here's the sample data tables;

Transfer Shipment table;

TransTO - TransFROM - ItemNo - Desc - Desc2 - VariantCode - Qty

Location1 HO Item01 Active Black Variant01 5 <------ Delivery

HO Location1 Item01 Active Black Variant01 2 <------ Pull-out

HO Location1 Item01 Active Black Variant01 1 <------ Pull-out

Sales Entry table;

Loc Code - Item No - Variant Code - Qty

Location1 Item01 Variant01 -1 <------ PSales

Item Entry table;

Loc Code - Item No - Desc - Desc2- VariantCode- Qty - EntryType - DocNo

Location1 Item01 Active Black Variant01 4 2 ItemAdj0001 <------ PAdj

Location1 Item01 Active Black Variant01 1 2 StrAdj0001 <------ PAdj

Location1 Item01 Active Black Variant01 -1 1 MSales0001 <------ MSales

Location1 Item01 Active Black Variant01 -1 1 MSales0002 <------ MSales

Location1 Item01 Active Black Variant01 -1 3 LocAdj0005 <------ NAdj

Location1 Item01 Active Black Variant01 -1 3 BtqAdj0008 <------ NAdj

SUM and JOIN (the sql query result should be like this)

Loc Code - Item No - Desc - Desc2 -VarCode- Delivery -PAdj- Pull-out - PSales - MSales - NAdj - Total

Location1 Item01 Active Black Variant01 10 5 3 -2 -2 -2 6

here's my sql query code;

WITH SumarisedTransfers AS
	(
		SELECT
		[BBI$Transfer Shipment Line].[Transfer-to Code] AS [Location]
		,[BBI$Transfer Shipment Line].[Item No_] AS [Item No]
		,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]
		,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]
		,[BBI$Transfer Shipment Line].[Variant Code]
		,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Delivery]
		,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]
		,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand] 
		,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]
		,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]
		FROM 
		[BBI$Transfer Shipment Line]
		GROUP BY
		[BBI$Transfer Shipment Line].[Transfer-to Code]
		,[BBI$Transfer Shipment Line].[Item No_]
		,[BBI$Transfer Shipment Line].[Variant Code]
	),
	Transfersfrom AS
		(
		SELECT
		[BBI$Transfer Shipment Line].[Transfer-from Code] AS [Locationfrom]
		,[BBI$Transfer Shipment Line].[Item No_]
		,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]
		,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]
		,[BBI$Transfer Shipment Line].[Variant Code]
		,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Pull-out]
		,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]
		,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand] 
		,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]
		,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]
		FROM 
		[BBI$Transfer Shipment Line]
		GROUP BY
		[BBI$Transfer Shipment Line].[Transfer-from Code]
		,[BBI$Transfer Shipment Line].[Item No_]
		,[BBI$Transfer Shipment Line].[Variant Code]
	),
	SumarisedSales AS
	(
		SELECT
		[BBI$Trans_ Sales Entry].[Store No_]
		,[BBI$Trans_ Sales Entry].[Item No_]
		,[BBI$Trans_ Sales Entry].[Variant Code]
		,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS [Sales Entry]
		FROM
		[BBI$Trans_ Sales Entry]
		GROUP BY
		[BBI$Trans_ Sales Entry].[Store No_]
		,[BBI$Trans_ Sales Entry].[Item No_]
		,[BBI$Trans_ Sales Entry].[Variant Code]
	),
	Location AS
    (
		SELECT
		[BBI$Location].Name AS [Name]
		FROM [BBI$Location]
	),
	ItemLedgerMSales AS
	(
		SELECT
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
		,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]
		FROM
		[BBI$Item Ledger Entry]
		GROUP BY
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
	),
		ItemLedgerPAdj AS
	(
		SELECT
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
		,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PAdj]
		FROM
		[BBI$Item Ledger Entry]
		GROUP BY
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
	)

SELECT	 ST.[Location],
		 ST.[Item No],
		 ST.[Description],
		 ST.[Description 2],
		ST.[Variant Code],
		ST.[Delivery],
		ISNULL(PADJ.[Quantity],0) AS [PAdj],
		ISNULL(TF.[Pull-out],0) AS [Pull-out],
		ISNULL(SS.[Sales Entry],0) AS [PSales],
		ISNULL(MS.[Quantity],0) AS [MSales],
		ST.[Delivery]+ISNULL(PADJ.[Quantity],0)-ISNULL(TF.[Pull-out],0)+ISNULL(SS.[Sales Entry],0)+ISNULL(MS.[Quantity],0) AS Total 

FROM [SumarisedTransfers] ST

LEFT JOIN [Transfersfrom] TF
ON [ST].[Location] = [TF].[Locationfrom]
AND [ST].[Item No] = [TF].[Item No_]
AND [ST].[Variant Code] = [TF].[Variant Code]

LEFT JOIN [SumarisedSales] SS
ON [SS].[Store No_] = [ST].[Location]
AND [SS].[Item No_] = [ST].[Item No]
AND [SS].[Variant Code] = [ST].[Variant Code]

LEFT JOIN [BBI$Item Ledger Entry] MS
ON [ST].[Location] = [MS].[Location Code]
AND [ST].[Item No] = [MS].[Item No_]
AND [ST].[Variant Code] = [MS].[Variant Code]

LEFT JOIN [BBI$Item Ledger Entry] PADJ
ON [ST].[Location] = [PADJ].[Location Code]
AND [ST].[Item No] = [PADJ].[Item No_]
AND [ST].[Variant Code] = [PADJ].[Variant Code]

WHERE
ST.[Location]='LOCATION1' AND
ST.[Item No]='ITEM01' AND
ST.[Variant Code]='VARIANT01' AND
MS.[Entry Type]='1' AND
MS.[Document No_] LIKE 'MSales%' AND
PADJ.[Entry Type]='2'

but the sql query did not sum qty of PAdj column and shows duplicate result;

LocCode -ItemNo -Desc - Desc2 -VarCode -Delivery - PAdj -Pull-out- PSales - MSales - NAdj -Total

Location1 Item01 Active Black Variant01 10 4 3 -2 -2 -2 5

Location1 Item01 Active Black Variant01 10 1 3 -2 -2 -2 2

Thank you!

Asked by:- glennyboy

: 1539 At:- 9/10/2020 9:18:16 AM

sql ms sql sql query