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

Question

Post reply

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

glennyboy

Old Hand

Points: 348

September 10, 2020 at 8:52 am

Go to Answer

#3787475

Good day!

Need help re SUM and JOIN 2 columns with different tables but did not 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 duplicates 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!

This topic was modified 4 years, 2 months ago by glennyboy.
This topic was modified 4 years, 2 months ago by glennyboy.
This topic was modified 4 years, 2 months ago by glennyboy.

Cebisa

SSC Enthusiast

Points: 151
More actions
September 11, 2020 at 10:15 am
Answer
#3787822

You have 2 left join statements for BBI$Item ledger entry.
I would take the second reference out
Change your where clause to
(
(MS.[Entry Type]='1' AND
MS.[Document No_] LIKE 'MSales%' )
OR
MS[Entry Type]='2')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.