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,246 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,246 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,246 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,246 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

I did not look into this in detail. However, I note that you have:

 LEFT JOIN [ILEtransfer] ILETR
 ON ILETR.[Item No_]=BAR.[Item No_]
 AND ILETR.[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'

The conditions on ILETR in the WHERE clause, effectively transform that left join to an inner join, and likewise the WHERE clause transforms the two full joins to left joins. Possibly, these conditions should be moved to the ON clause for the left join. If an outer join was what you intended in the first place. Maybe you actually wanted an inner join? But even in that case you have to be careful since you have those full outer joins. (Which, again, I don't know if you really need.)

Also, the condition in the WHERE clause looks suspicious. As you have written it, you are saying "all entry types 1, 2 and 3. And entry type 4, but only if the location is HPGW". Is that what you want?

glennyboy 121 Reputation points

2020-09-28T04:40:19.947+00:00

Good day!

thank you for your reply

As you have written it, you are saying "all entry types 1, 2 and 3. And entry type 4, but only if the location is HPGW". Is that what you want?

yes, one location (HPGW), but i want to sum all by entry types 1, 2, 3 and 4

do i still need to include the where clause from the last part of my query?

Answer 3

MelissaMa-msft 24,246 Moderator

Hi @glennyboy ,

I found that there was no data of Item No_ is '27019 000' or '271-3878' in Transfer Line Table while there were some data in Trans_ Sales Entry Table after checking your sample data excel.

In your query, both of tables are join with Barcodes table using Item No_.

Then of course there will be no data if you use join.

Is there any possible that you could remove the join with Transfer Line Table or use exists in your where condition?

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-28T05:47:55.863+00:00
hi @MelissaMa-msft

Good day!

Thank you for your reply,

I found that there was no data of Item No_ is '27019 000' or '271-3878' in Transfer Line Table while there were some data in Trans_ Sales Entry Table after checking your sample data excel.

yes, but my main table is Barcodes

In your query, both of tables are join with Barcodes table using Item No_.

Item No_ and Variant Code

Is there any possible that you could remove the join with Transfer Line Table or use exists in your where condition?

i already remove Transfer Line Table from join, still getting missing record

ii will post query and result

again thank you

Answer 4

Hi @glennyboy ,

BBI$Item.No_ was defined as float in my side when I imported it from excel. So I have to convert the date type of BBI$Item.No_ in order to have the same data type with Item No_.

You could change this part if necessary from your side.

Please find the updated query from 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$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]  
  )  
  ,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(ILETR.[PDel],0)+ISNULL(ILEPA.[PAdj],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]  
       
  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(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-09-29T08:59:45.2+00:00

hi @MelissaMa-msft ,

i run the query, but get this error;

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.

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

thank you very much for your help
MelissaMa-msft 24,246 Reputation points Moderator

2020-09-30T01:49:03.36+00:00

Hi @glennyboy ,

What is the exact table name of Item table?

What is the data type of No_ from item table and Item No_ from BBI$Trans_ Sales Entry table.

Please provide the DDL of all mentioned tables if possible.

Best regards
Melissa

Answer 5

hi @MelissaMa-msft ,

Good day!

here's the full query, i comment some of the code;

Line 135 --,ISNULL(TSEPS.[PSales],0) AS [PSales]

Still getting wrong SUM qty for UDel

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

Thank you!

     ;WITH Barcodes AS  
      (  
      SELECT  
       [BBI$Barcodes].[Item No_]  
       ,[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]  
      )  
      ,total as (     
      SELECT DISTINCT  
	   ILETR.[Location]  
       ,BAR.[Item No_] AS [Item No_]  
       ,IT.[Description] AS [Description]  
	   ,IT.[Description 2] AS [Description 2]  
	   ,IT.[No_ 2] AS [No_ 2]  
       ,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]  
              
      LEFT JOIN [Transfeline] TL  
      ON TL.[Item No_]=BAR.[Item No_]  
      AND TL.[Variant Code]=BAR.[Variant Code]  
  
	  LEFT JOIN [BBI$Item] IT  
	  ON IT.[No_]=BAR.[Item No_]  
           
      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],[Description])[Description] ,  
	  isnull(a.[Description],[Description 2])[Description 2] ,  
	  isnull(a.[No_ 2],[No_ 2])[No2] ,  
      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]

MelissaMa-msft 24,246 Reputation points Moderator

2020-10-12T07:01:02.037+00:00

Hi @glennyboy ,

If you would like to have the output total SUM qty is 401, you have to change the position of below part from inside the total CTE to the last select statement.

FULL JOIN [Transfeline] TL ON TL.[Item No_]=BAR.[Item No_] AND TL.[Variant Code]=BAR.[Variant Code]

Best regards
Melissa
glennyboy 121 Reputation points

2020-10-12T07:12:38.2+00:00

hi @MelissaMa-msft ,

thank you for the reply

add this code;

FULL JOIN [Transfeline] TL ON TL.[Item No_]=BAR.[Item No_] AND TL.[Variant Code]=BAR.[Variant Code]

after or before this last statement code;

FULL JOIN [TSEpsales] TSEPS ON TSEPS.[Item No_]=a.[Item No_] AND TSEPS.[Variant Code]=a.[Variant Code]

Thank you!
glennyboy 121 Reputation points

2020-10-12T07:19:08.007+00:00

getting this error message;

Msg 4104, Level 16, State 1, Line 189
The multi-part identifier "BAR.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 190
The multi-part identifier "BAR.Variant Code" could not be bound.
Msg 209, Level 16, State 1, Line 172
Ambiguous column name 'Description'.
Msg 209, Level 16, State 1, Line 173
Ambiguous column name 'Description 2'.
MelissaMa-msft 24,246 Reputation points Moderator

2020-10-12T07:23:49.253+00:00

Hi @glennyboy ,

Please refer attached txt for updated code.

31604-updated-code.txt
I still could not find the cause of the multi-part identifier error you mentioned before.

Best regards
Melissa
glennyboy 121 Reputation points

2020-10-12T07:42:17.98+00:00

hi @MelissaMa-msft ,

the SUM qty for UDel is now 401

thank you very much for your help
MelissaMa-msft 24,246 Reputation points Moderator

2020-10-12T07:45:44.137+00:00

Hi @glennyboy ,

That's great. I am glad to hear this good news.

May I know how you fixed the multi-part identifier error from your side?

Best regards
Melissa
glennyboy 121 Reputation points

2020-10-12T07:52:23.123+00:00

hi @MelissaMa-msft ,

incorrect code;

FULL JOIN [Transfeline] TL ON TL.[Item No_]=BAR.[Item No_] AND TL.[Variant Code]=BAR.[Variant Code]

correct code;

FULL JOIN [Transfeline] TL ON TL.[Item No_]=a.[Item No_] AND TL.[Variant Code]=a.[Variant Code]

again thank you very much!
glennyboy 121 Reputation points

2020-10-12T08:18:44.817+00:00

hi @MelissaMa-msft ,

sorry, i got excited in the correct SUM qty of UDel, but when i check the column of Item No_, Description, No2, Variant Code i got NULL result

thank you
glennyboy 121 Reputation points

2020-10-12T08:33:15.453+00:00

hi @MelissaMa-msft ,

i will post another question, since this is different issue.

Thank you!

Share via

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

8 additional answers

Your answer