SQL Query - SUM and JOIN multiple tables but return missing record

Question

SQL Query - SUM and JOIN multiple tables but return missing record

glennyboy 121

SQL Query - SUM and JOIN multiple tables but return missing record;

Good day!

here's the sample data link : sample data table.xlsx

Barcodes Table
Item Ledger Entry Table
Trans_ Sales Entry Table
Transfer Line Table
Query result

Need help!

when i JOIN and SUM the Trans_ Sales Entry Table and Transfer Line Table , but the query result return missing record

Thank you!

here's my sql query code;

;WITH Barcodes AS  
(  
SELECT  
 [BBI$Barcodes].[Item No_]  
 ,[BBI$Barcodes].[Description]  
 ,[BBI$Barcodes].[Variant Code]  
FROM [BBI$Barcodes]  
),  
ILEtransfer AS  
(  
SELECT  
 [BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
 ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PDel]  
  
FROM [BBI$Item Ledger Entry]  
WHERE  
 [BBI$Item Ledger Entry].[Location Code]='HPGW'  
 AND [BBI$Item Ledger Entry].[Entry Type] = '4'  
GROUP BY  
 [BBI$Item Ledger Entry].[Location Code]  
 ,[BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
),  
ILEmsales AS  
(  
SELECT  
 [BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
 ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]  
  
FROM [BBI$Item Ledger Entry]  
WHERE  
 [BBI$Item Ledger Entry].[Location Code]='HPGW'  
 AND [BBI$Item Ledger Entry].[Entry Type] = '1'  
 AND [BBI$Item Ledger Entry].[Document No_] NOT LIKE 'HP%'  
GROUP BY  
 [BBI$Item Ledger Entry].[Location Code]  
 ,[BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
),  
ILEpadj AS  
(  
SELECT  
 [BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
 ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PAdj]  
  
FROM [BBI$Item Ledger Entry]  
WHERE  
 [BBI$Item Ledger Entry].[Location Code]='HPGW'  
 AND [BBI$Item Ledger Entry].[Entry Type] = '2'  
GROUP BY  
 [BBI$Item Ledger Entry].[Location Code]  
 ,[BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
),  
ILEnadj AS  
(  
SELECT  
 [BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
 ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [NAdj]  
  
FROM [BBI$Item Ledger Entry]  
WHERE  
 [BBI$Item Ledger Entry].[Location Code]='HPGW'  
 AND [BBI$Item Ledger Entry].[Entry Type] = '3'  
GROUP BY  
 [BBI$Item Ledger Entry].[Location Code]  
 ,[BBI$Item Ledger Entry].[Entry Type]  
 ,[BBI$Item Ledger Entry].[Item No_]  
 ,[BBI$Item Ledger Entry].[Variant Code]  
),  
Transfeline  AS  
(  
SELECT  
    [BBI$Transfer Line].[Transfer-to Code]  
      ,[BBI$Transfer Line].[Item No_]  
      ,MAX([BBI$Transfer Line].[Description]) AS [Description]  
      ,MAX([BBI$Transfer Line].[Description 2]) AS [Description 2]  
      ,[BBI$Transfer Line].[Variant Code]  
      ,SUM([BBI$Transfer Line].[Quantity]) AS [UDel]  
  FROM [BBI$Transfer Line]  
  WHERE  
  [BBI$Transfer Line].[Transfer-to Code] = 'HPGW'  
  GROUP BY  
      [BBI$Transfer Line].[Transfer-to Code]  
      ,[BBI$Transfer Line].[Item No_]  
      ,[BBI$Transfer Line].[Variant Code]  
),  
TSEpsales 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 [PSales]  
FROM [BBI$Trans_ Sales Entry]  
WHERE  
 [BBI$Trans_ Sales Entry].[Store No_]='HPGW'  
 AND [BBI$Trans_ Sales Entry].[Transaction No_] NOT IN (5271,5272,5273,5278,5279,5280,5281,5282,5283,5284,5285,5286,8530,8531,8532,8533,8534,8535,8536,8537,13133,13849)  
GROUP BY  
 [BBI$Trans_ Sales Entry].[Store No_]  
 ,[BBI$Trans_ Sales Entry].[Item No_]  
 ,[BBI$Trans_ Sales Entry].[Variant Code]  
)  
  
SELECT DISTINCT  
 BAR.[Item No_] AS [Item No_]  
 ,BAR.[Description] AS [Description]  
 ,BAR.[Variant Code] AS [Variant Code]  
 ,ISNULL(ILETR.[PDel],0) AS [PDel]  
 ,ISNULL(ILEMS.[MSales],0) AS [MSales]  
 ,ISNULL(ILEPA.[PAdj],0) AS [PAdj]  
 ,ISNULL(ILENA.[NAdj],0) AS [NAdj]  
 ,ISNULL(TL.[UDel],0) AS [UDel]  
 ,ISNULL(TSEPS.[PSales],0) AS [PSales]  
  ,ISNULL(ILETR.[PDel],0)+ISNULL(ILEPA.[PAdj],0)+ISNULL(TL.[UDel],0)+ISNULL(ILEMS.[MSales],0)+ISNULL(ILENA.[NAdj],0)+ISNULL(TSEPS.[PSales],0) AS Total  
  
FROM [BBI$Barcodes] BAR  
  
LEFT JOIN [ILEtransfer] ILETR  
ON ILETR.[Item No_]=BAR.[Item No_]  
AND ILETR.[Variant Code]=BAR.[Variant Code]  
  
LEFT JOIN [ILEmsales] ILEMS  
ON ILEMS.[Item No_]=BAR.[Item No_]  
AND ILEMS.[Variant Code]=BAR.[Variant Code]  
  
LEFT JOIN [ILEpadj] ILEPA  
ON ILEPA.[Item No_]=BAR.[Item No_]  
AND ILEPA.[Variant Code]=BAR.[Variant Code]  
  
LEFT JOIN [ILEnadj] ILENA  
ON ILENA.[Item No_]=BAR.[Item No_]  
AND ILENA.[Variant Code]=BAR.[Variant Code]  
  
FULL JOIN [Transfeline] TL  
ON TL.[Item No_]=BAR.[Item No_]  
AND TL.[Variant Code]=BAR.[Variant Code]  
  
FULL JOIN [TSEpsales] TSEPS  
ON TSEPS.[Item No_]=BAR.[Item No_]  
AND TSEPS.[Variant Code]=BAR.[Variant Code]  
  
WHERE  
ILETR.[Location]='HPGW'  
AND ILETR.[Entry Type]='4'  
OR ILEMS.[Entry Type]='1'  
OR ILEPA.[Entry Type]='2'  
OR ILENA.[Entry Type]='3'

if i run this query alone [BBI$Trans_ Sales Entry], the return result SUM quantity are correct below.

but if i run the whole query with JOIN, the return result SUM quantity from [BBI$Trans_ Sales Entry] have missing record

     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 [PSales]  
     FROM [BBI$Trans_ Sales Entry]  
     WHERE  
         [BBI$Trans_ Sales Entry].[Store No_]='HPGW'  
         AND [BBI$Trans_ Sales Entry].[Transaction No_] NOT IN (5271,5272,5273,5278,5279,5280,5281,5282,5283,5284,5285,5286,8530,8531,8532,8533,8534,8535,8536,8537,13133,13849)      
     GROUP BY  
         [BBI$Trans_ Sales Entry].[Store No_]  
         ,[BBI$Trans_ Sales Entry].[Item No_]  
         ,[BBI$Trans_ Sales Entry].[Variant Code]

Answer accepted by question author

8 additional answers

Your answer

Answer 1

MelissaMa-msft 24,241 Moderator

Hi @glennyboy ,

btw, i dont see in your query the Item table where to JOIN

TSEpsales AS  
   (  
 SELECT  
           [BBI$Trans_ Sales Entry].[Store No_]  
           ,[BBI$Trans_ Sales Entry].[Item No_]  
           ,[BBI$Trans_ Sales Entry].[Variant Code]  
    ,[BBI$Item].[Description]  
           ,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS [PSales]  
       FROM [BBI$Trans_ Sales Entry]  
    LEFT JOIN BBI$Item  
    ON CONVERT(NVARCHAR(255),BBI$Item.No_)=[BBI$Trans_ Sales Entry].[Item No_]  
       WHERE  
           [BBI$Trans_ Sales Entry].[Store No_]='HPGW'  
           AND [BBI$Trans_ Sales Entry].[Transaction No_] NOT IN (5271,5272,5273,5278,5279,5280,5281,5282,5283,5284,5285,5286,8530,8531,8532,8533,8534,8535,8536,8537,13133,13849)      
       GROUP BY  
           [BBI$Trans_ Sales Entry].[Store No_]  
           ,[BBI$Trans_ Sales Entry].[Item No_]  
           ,[BBI$Trans_ Sales Entry].[Variant Code]  
     ,[BBI$Item].[Description]  
   )

What is DDL of your item table? I named it as BBI$Item from my side.

Msg 4104, Level 16, State 1, Line 178
The multi-part identifier "A.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 179
The multi-part identifier "A.Variant Code" could not be bound.

This part is working from my side. Please provide your error snapshot(final part of query ,output and error message) if possible.

Maybe:

   select isnull(a.[Item No_],TSEPS.[Item No_])[Item No_] ,  
   isnull(a.[Description],TSEPS.[Description]) [Description],  
   isnull(a.[Variant Code],TSEPS.[Variant Code]) [Variant Code],  
   isnull(a.[PDel],0) [PDel],  
   isnull(a.[MSales],0) [MSales],  
   isnull(a.[PAdj],0) [PAdj],  
   isnull(a.[NAdj],0) [NAdj],  
   isnull(TL.[UDel],0) [UDel],  
   ISNULL(TSEPS.[PSales],0) AS [PSales],  
   ISNULL(a.[PDel],0)+ISNULL(a.[PAdj],0)+ISNULL(TL.[UDel],0)+ISNULL(a.[MSales],0)+ISNULL(a.[NAdj],0)+ISNULL(TSEPS.[PSales],0) AS Total  
   from total a   
  FULL JOIN [TSEpsales] TSEPS  
   ON TSEPS.[Item No_]=a.[Item No_]  
   AND TSEPS.[Variant Code]=a.[Variant Code]  
   FULL JOIN [Transfeline] TL  
   ON TL.[Item No_]=a.[Item No_]  
   AND TL.[Variant Code]=a.[Variant Code]

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

glennyboy 121 Reputation points

2020-10-06T03:52:17.483+00:00

hi @MelissaMa-msft .

Good day!

What is the exact table name of Item table?

[BBI$Item]

[BBI$Transfer Line]

Item Table and Transfer Line Table

Thank you.
MelissaMa-msft 24,241 Reputation points Moderator

2020-10-06T05:03:46.96+00:00

Hi @glennyboy ,

What is the data type of [BBI$Trans_ Sales Entry].[Item No_] and [BBI$Item].[No_]?

Are you using the same complete query I provided 6 days ago?

It is working fine from my side. You could find the output from attachment and save it as excel.30284-bbi-output.txt

Please provide the snapshot of your last part if possible.

Best regards
Melissa
glennyboy 121 Reputation points

2020-10-06T10:16:44.193+00:00
Hi @MelissaMa-msft ,

What is the data type of [BBI$Trans_ Sales Entry].[Item No_] and [BBI$Item].[No_]?

[BBI$Trans_ Sales Entry].[Item No_] - nvarchar

[BBI$Item].[No_] - nvarchar

Are you using the same complete query I provided 6 days ago?

Yes, i run your full query

i made a mistake on my sample data in [BBI$Trans_ Sales Entry] table, there is no column "Description" and "Description 2"

the column "Description" and "Description 2" is from [BBI$Item].[No_] table

Please provide the snapshot of your last part if possible.

sorry, what do you mean snapshot?

Thank you!
MelissaMa-msft 24,241 Reputation points Moderator

2020-10-07T06:13:49.297+00:00
Hi @glennyboy ,

It is so strange that it is working well from my side and not from your side even though we use the same query.

Is it Case Sensitive from your side?

If yes, you could try to replace last part with below:

from total a FULL JOIN [TSEpsales] TSEPS ON TSEPS.[Item No_]=a.[Item No_] AND TSEPS.[Variant Code]=a.[Variant Code] FULL JOIN [Transfeline] TL ON TL.[Item No_]=a.[Item No_] AND TL.[Variant Code]=a.[Variant Code]

Best regards
Melissa
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2020-10-07T21:22:19.667+00:00

It could certainly help if Glenny posted the full query he is using and not a part through a screen shot.

I still think you need some isnull acrobatics with the full-join, see of on my previous posts above.
MelissaMa-msft 24,241 Reputation points Moderator

2020-10-08T02:09:46.953+00:00

Hi @Erland Sommarskog ,

Actually I tried with your method and there was no error reported.

But the total of Udel was 21 instead of 401 which could be the correct one in the output.

So I had to write the query in my way.

Best regards
Melissa

Answer 2

MelissaMa-msft 24,241 Moderator

Hi @glennyboy ,

I checked your query and sample data again and found that your missing records did not match the condition '[Document No_] NOT LIKE 'HP%''.

Please refer below details:

If you comment below row, you could see the missing record in output:

ILEmsales AS  
 (  
 SELECT  
  [BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
  ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]  
      
 FROM [BBI$Item Ledger Entry]  
 WHERE  
  [BBI$Item Ledger Entry].[Location Code]='HPGW'  
  AND [BBI$Item Ledger Entry].[Entry Type] = '1'  
  --AND [BBI$Item Ledger Entry].[Document No_] NOT LIKE 'HP%'  
 GROUP BY  
  [BBI$Item Ledger Entry].[Location Code]  
  ,[BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
 ),

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

glennyboy 121 Reputation points

2020-09-28T07:01:28.26+00:00

hi @MelissaMa-msft , i posted the query above [BBI$Trans_ Sales Entry], thank you again
MelissaMa-msft 24,241 Reputation points Moderator

2020-09-28T07:08:43.117+00:00

Hi @glennyboy ,

Please check my updated answer.

In my opinion, your issue is related with the missing records do not meet the condition in CTE ILEmsales instead of joining BBI$Trans_ Sales Entry table.

Best regards
Melissa
glennyboy 121 Reputation points

2020-09-28T07:35:51.357+00:00

hi @MelissaMa-msft ,

i already comment '[Document No_] NOT LIKE 'HP%''. and run the whole query and [BBI$Trans_ Sales Entry] query

i compare the results between the two query and the both quantity is correct

but i need to include the '[Document No_] NOT LIKE 'HP%'' and also the Transfer Line Table

thank you again!

Answer 3

Hi @glennyboy ,

If you still need to include the '[Document No_] NOT LIKE 'HP%'' and also the Transfer Line Table, please refer below:

;WITH Barcodes AS  
 (  
 SELECT  
  [BBI$Barcodes].[Item No_]  
  ,[BBI$Barcodes].[Description]  
  ,[BBI$Barcodes].[Variant Code]  
 FROM [BBI$Barcodes]  
 ),  
 ILEtransfer AS  
 (  
 SELECT  
  [BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
  ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PDel]  
      
 FROM [BBI$Item Ledger Entry]  
 WHERE  
  [BBI$Item Ledger Entry].[Location Code]='HPGW'  
  AND [BBI$Item Ledger Entry].[Entry Type] = '4'  
 GROUP BY  
  [BBI$Item Ledger Entry].[Location Code]  
  ,[BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
 ),  
 ILEmsales AS  
 (  
 SELECT  
  [BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
  ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]  
      
 FROM [BBI$Item Ledger Entry]  
 WHERE  
  [BBI$Item Ledger Entry].[Location Code]='HPGW'  
  AND [BBI$Item Ledger Entry].[Entry Type] = '1'  
  AND [BBI$Item Ledger Entry].[Document No_] NOT LIKE 'HP%'  
 GROUP BY  
  [BBI$Item Ledger Entry].[Location Code]  
  ,[BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
 ),  
 ILEpadj AS  
 (  
 SELECT  
  [BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
  ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PAdj]  
      
 FROM [BBI$Item Ledger Entry]  
 WHERE  
  [BBI$Item Ledger Entry].[Location Code]='HPGW'  
  AND [BBI$Item Ledger Entry].[Entry Type] = '2'  
 GROUP BY  
  [BBI$Item Ledger Entry].[Location Code]  
  ,[BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
 ),  
 ILEnadj AS  
 (  
 SELECT  
  [BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Location Code] AS [Location]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
  ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [NAdj]  
      
 FROM [BBI$Item Ledger Entry]  
 WHERE  
  [BBI$Item Ledger Entry].[Location Code]='HPGW'  
  AND [BBI$Item Ledger Entry].[Entry Type] = '3'  
 GROUP BY  
  [BBI$Item Ledger Entry].[Location Code]  
  ,[BBI$Item Ledger Entry].[Entry Type]  
  ,[BBI$Item Ledger Entry].[Item No_]  
  ,[BBI$Item Ledger Entry].[Variant Code]  
 ),  
 Transfeline  AS  
 (  
 SELECT  
     [BBI$Transfer Line].[Transfer-to Code]  
       ,[BBI$Transfer Line].[Item No_]  
       ,MAX([BBI$Transfer Line].[Description]) AS [Description]  
       ,MAX([BBI$Transfer Line].[Description 2]) AS [Description 2]  
       ,[BBI$Transfer Line].[Variant Code]  
       ,SUM([BBI$Transfer Line].[Quantity]) AS [UDel]  
   FROM [BBI$Transfer Line]  
   WHERE  
   [BBI$Transfer Line].[Transfer-to Code] = 'HPGW'  
   GROUP BY  
       [BBI$Transfer Line].[Transfer-to Code]  
       ,[BBI$Transfer Line].[Item No_]  
       ,[BBI$Transfer Line].[Variant Code]  
 ),  
 TSEpsales AS  
 (  
 SELECT  
  [BBI$Trans_ Sales Entry].[Store No_]  
  ,[BBI$Trans_ Sales Entry].[Item No_]  
  ,[BBI$Trans_ Sales Entry].[Variant Code]  
    ,[BBI$Trans_ Sales Entry].Description  
  ,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS [PSales]  
 FROM [BBI$Trans_ Sales Entry]  
 WHERE  
  [BBI$Trans_ Sales Entry].[Store No_]='HPGW'  
  AND [BBI$Trans_ Sales Entry].[Transaction No_] NOT IN (5271,5272,5273,5278,5279,5280,5281,5282,5283,5284,5285,5286,8530,8531,8532,8533,8534,8535,8536,8537,13133,13849)  
 GROUP BY  
  [BBI$Trans_ Sales Entry].[Store No_]  
  ,[BBI$Trans_ Sales Entry].[Item No_]  
  ,[BBI$Trans_ Sales Entry].[Variant Code]  
    ,[BBI$Trans_ Sales Entry].Description  
 )  
 ,total as (     
 SELECT DISTINCT  
  BAR.[Item No_] AS [Item No_]  
  ,BAR.[Description] AS [Description]  
  ,BAR.[Variant Code] AS [Variant Code]  
  ,ISNULL(ILETR.[PDel],0) AS [PDel]  
  ,ISNULL(ILEMS.[MSales],0) AS [MSales]  
  ,ISNULL(ILEPA.[PAdj],0) AS [PAdj]  
  ,ISNULL(ILENA.[NAdj],0) AS [NAdj]  
  ,ISNULL(TL.[UDel],0) AS [UDel]  
  --,ISNULL(TSEPS.[PSales],0) AS [PSales]  
   ,ISNULL(ILETR.[PDel],0)+ISNULL(ILEPA.[PAdj],0)+ISNULL(TL.[UDel],0)+ISNULL(ILEMS.[MSales],0)+ISNULL(ILENA.[NAdj],0) AS Total  
      
 FROM [BBI$Barcodes] BAR  
   
 LEFT JOIN [ILEtransfer] ILETR  
 ON ILETR.[Item No_]=BAR.[Item No_]  
 AND ILETR.[Variant Code]=BAR.[Variant Code]  
      
 LEFT JOIN [ILEmsales] ILEMS  
 ON ILEMS.[Item No_]=BAR.[Item No_]  
 AND ILEMS.[Variant Code]=BAR.[Variant Code]  
      
 LEFT JOIN [ILEpadj] ILEPA  
 ON ILEPA.[Item No_]=BAR.[Item No_]  
 AND ILEPA.[Variant Code]=BAR.[Variant Code]  
      
 LEFT JOIN [ILEnadj] ILENA  
 ON ILENA.[Item No_]=BAR.[Item No_]  
 AND ILENA.[Variant Code]=BAR.[Variant Code]  
      
 FULL JOIN [Transfeline] TL  
 ON TL.[Item No_]=BAR.[Item No_]  
 AND TL.[Variant Code]=BAR.[Variant Code]  
   
 WHERE  
 ILETR.[Location]='HPGW'  
 AND ILETR.[Entry Type]='4'  
 OR ILEMS.[Entry Type]='1'  
 OR ILEPA.[Entry Type]='2'  
 OR ILENA.[Entry Type]='3')  
  
 select isnull(a.[Item No_],TSEPS.[Item No_])[Item No_] ,  
 isnull(a.[Description],TSEPS.[Description]) [Description],  
 isnull(a.[Variant Code],TSEPS.[Variant Code]) [Variant Code],  
 isnull(a.[PDel],0) [PDel],  
 isnull(a.[MSales],0) [MSales],  
 isnull(a.[PAdj],0) [PAdj],  
 isnull(a.[NAdj],0) [NAdj],  
 isnull(a.[UDel],0) [UDel],  
 ISNULL(TSEPS.[PSales],0) AS [PSales],  
 ISNULL(a.[PDel],0)+ISNULL(a.[PAdj],0)+ISNULL(a.[UDel],0)+ISNULL(a.[MSales],0)+ISNULL(a.[NAdj],0)+ISNULL(TSEPS.[PSales],0) AS Total  
 from total a   
FULL JOIN [TSEpsales] TSEPS  
 ON TSEPS.[Item No_]=a.[Item No_]  
 AND TSEPS.[Variant Code]=a.[Variant Code]

Output:

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

MelissaMa-msft 24,241 Reputation points Moderator

2020-09-29T02:16:07.31+00:00

Hi @glennyboy ,

Could you please validate above updated query and check whether it is working?

If it is not working ,please provide more details. Thanks.

Best regards
Melissa
glennyboy 121 Reputation points

2020-09-29T03:24:28.217+00:00

hi @MelissaMa-msft ,

Good day!

thank you for your reply

i will test/run your query code today.

Thank you very much!
glennyboy 121 Reputation points

2020-09-29T04:10:11.113+00:00

hi @MelissaMa-msft ,

i already run/test your query code

Pdel - correct sum qty
MSales - correct sum qty
PAdj - correct sum qty
NAdj - correct sum qty
PSales - correct sum qty

Udel - wrong sum qty

sorry, i made a mistake in my sample data table "Trans_ Sales Entry Table"

there is no column name "Description" in "Trans_ Sales Entry Table"

the "Description" , "Description2" and other info is in Item Table

how do i JOIN another table (Item Table) for the "Description" , "Description2" and other info.

Thank you!

Answer 4

Erland Sommarskog 128.7K MVP Volunteer Moderator

So I looked a little more closely at the query now. (But I did not look much at the Excel file. I want data in a form I can work, and normally that is CREATE TABLE + INSERT statements. Which of course is not workable for 10000+ rows as in this case. On the other hand, we encourage people to produce a small sample set, enough to demonstrate the problem.

Anyway, I think your problem is the WHERE clause at the end:

 WHERE
 ILETR.[Location]='HPGW'
 AND ILETR.[Entry Type]='4'
 OR ILEMS.[Entry Type]='1'
 OR ILEPA.[Entry Type]='2'
 OR ILENA.[Entry Type]='3'

I think you should just remove it. I failed to notice last night that these [Entry Type] have different aliases. More importantly, I see now that they are redundant with the WHERE conditions in the CTE, so they are not needed. But they have the effect that they transform the left joins to inner joins, and the full join to a left join - so that could very well be the reason you are losing rows.

glennyboy 121 Reputation points

2020-09-29T03:59:19.267+00:00

hi @Erland Sommarskog ,

Good day!

Thank you very for your reply.

i already remove the where clause at the end of my query, and run the whole query code

but the return result got wrong quantity in every column

Thank you!
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2020-09-29T22:00:09.523+00:00
I have spent so much time on other posts tonight, so I am short on time for this thread.

However, it occurred to me that we need to work hard with these full joins. Let me say this directly: Two full joins like you have is absolutely not a walk in the park. It is difficult to understand even for an experienced SQL person like me.

Going back to your original query, it may work if you write it as

FULL JOIN [Transfeline] TL ON TL.[Item No_]=BAR.[Item No_] AND TL.[Variant Code]=BAR.[Variant Code] FULL JOIN [TSEpsales] TSEPS ON TSEPS.[Item No_]= isnull(BAR.[Item No_], TL.[Item No_) AND TSEPS.[Variant Code] = isnull(BAR.[Variant Code], TL.[Variant Code])

And noted earlier, the final WHERE clause should not be there.

It would certainly help if you could provide CREATE TABLE + INSERT statements for a small data set and the expected result. The Excel data you posted is too big to work with.

Answer 5

Hi @glennyboy ,

Which table did you refer Item Table? Item Ledger Entry Table or other table?

If Item Ledger Entry Table, please refer below:

,  
  TSEpsales AS  
  (  
  SELECT  
   [BBI$Trans_ Sales Entry].[Store No_]  
   ,[BBI$Trans_ Sales Entry].[Item No_]  
   ,[BBI$Trans_ Sales Entry].[Variant Code]  
     ,ITEM.[Description]  
   ,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS [PSales]  
  FROM [BBI$Trans_ Sales Entry]   
  LEFT JOIN [BBI$Item Ledger Entry] ITEM  
  ON [BBI$Trans_ Sales Entry].[Item No_]=ITEM.[Item No_]  
  AND [BBI$Trans_ Sales Entry].[Variant Code]=ITEM.[Variant Code]  
  WHERE  
   [BBI$Trans_ Sales Entry].[Store No_]='HPGW'  
   AND [BBI$Trans_ Sales Entry].[Transaction No_] NOT IN   
   (5271,5272,5273,5278,5279,5280,5281,5282,5283,5284,5285,5286,  
   8530,8531,8532,8533,8534,8535,8536,8537,13133,13849)  
  GROUP BY  
   [BBI$Trans_ Sales Entry].[Store No_]  
   ,[BBI$Trans_ Sales Entry].[Item No_]  
   ,[BBI$Trans_ Sales Entry].[Variant Code]  
     ,ITEM.[Description]  
  )

But in your Item Ledger Entry Table, the description is null for Variant Code 'BLKLH65' and 'BLKLH70'.

Regarding the wrong sum qty of Udel, is it correct in your original query? What is the desired value of Udel?

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

glennyboy 121 Reputation points

2020-09-29T05:18:47.887+00:00

hi @MelissaMa-msft ,

Which table did you refer Item Table? Item Ledger Entry Table or other table?

other table which is Item table (Description, Description 2, No 2 and other info)

But in your Item Ledger Entry Table, the description is null for Variant Code 'BLKLH65' and 'BLKLH70

all the info in regards Description, Description 2, No 2 is all in Item table (relation to other table is Item No)

Regarding the wrong sum qty of Udel, is it correct in your original query? What is the desired value of Udel?

Udel - SUM of Transfer Line Table

total SUM qty is 401, but in the query only 21

Item and Transfer Line Table

Thank you!

Share via

SQL Query - SUM and JOIN multiple tables but return missing record

8 additional answers

Your answer