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

Question

Post reply

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

glennyboy

Old Hand

Points: 348

September 27, 2020 at 9:26 am

#3791145

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

Good day!

here's the sample data link

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

Trans sales entry 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'

This topic was modified 4 years, 1 month ago by glennyboy.

Attachments:

You must be logged in to view attached files.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

A couple of things

1. Opening an xlsx file is going to be a tough sell around here. Not a good practice.

2. Table aliases are essential for readable code. Please choose appropriate and minimally short table aliases.

3. Try it without the WHERE clause. Add those conditions to the LEFT JOIN ON conditions instead.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

glennyboy Old Hand Points: 348 More actions · Answer 2

good day!

thank you for your reply

1. sorry, my mistake

2. well noted on this

3. i will try this, ill remove the where clause at the part of my query

again thank you

glennyboy Old Hand Points: 348 More actions · Answer 3

Steve Collins wrote:

A couple of things
1. Opening an xlsx file is going to be a tough sell around here. Not a good practice.
2. Table aliases are essential for readable code. Please choose appropriate and minimally short table aliases.
3. Try it without the WHERE clause. Add those conditions to the LEFT JOIN ON conditions instead.

i already remove the where clause and add where clause to the left join on, but the return result got wrong quantity in every column

Thank you

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

glennyboy wrote:

i already remove the where clause and add where clause to the left join on, but the return result got wrong quantity in every column

It's good news we're making progress. Previously there was nothing returned and now it's something but the wrong something? To diagnose what the query is doing is difficult without sample data to see the conversion of input to output. It might also help to post the updated code. Looking at the CTE definitions it appears you're summarizing pieces and then FULL OUTER JOIN'ing them together. The WHERE clauses and GROUP BY's are similar such that crosstabs using conditional aggregation could simplify the query. Here's my attempt to refactor

;WITH 
BC AS (
    SELECT [Item No_], [Description], [Variant Code]
    FROM [BBI$Barcodes]),
ILE AS (
    SELECT [Entry Type], [Location Code] AS [Location], [Item No_], [Variant Code]
   SUM(case when [Entry Type] = '1' and [Document No_] NOT LIKE 'HP%' then Quantity else 0 end) AS MSales
   ,SUM(case when [Entry Type] = '2' then Quantity else 0 end) AS PAdj
   ,SUM(case when [Entry Type] = '3' then Quantity else 0 end) AS NAdj
   ,SUM(case when [Entry Type] = '4' then Quantity else 0 end) AS PDel
    FROM [BBI$Item Ledger Entry]
    WHERE [Location Code]='HPGW'
    GROUP BY [Entry Type], [Location Code], [Item No_], [Variant Code]),
TL AS (
    SELECT [Transfer-to Code], [Item No_], [Variant Code]
          ,SUM([BBI$Transfer Line].[Quantity]) AS UDel
    FROM [BBI$Transfer Line]
    WHERE [Transfer-to Code] = 'HPGW'
    GROUP BY [Transfer-to Code], [Item No_], [Variant Code]),
TSE AS (
    SELECT [Store No_], [Item No_], [Variant Code]
       ,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS PSales
    FROM [BBI$Trans_ Sales Entry]
    WHERE [Store No_]='HPGW'
      AND [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 [Store No_], [Item No_], [Variant Code])
SELECT b.[Item No_], b.[Description], b.[Variant Code]
       ,ISNULL(il.PDel, 0) AS PDel, ISNULL(il.MSales, 0) AS MSales
       ,ISNULL(IL.PAdj, 0) AS PAdj, ISNULL(il.NAdj, 0) AS NAdj
       ,ISNULL(TL.UDel, 0) AS UDel, ISNULL(ts.PSales, 0) AS PSales
       ,ISNULL(il.PDel, 0)+ISNULL(il.MSales, 0)+ISNULL(IL.PAdj, 0)+
        ISNULL(il.NAdj, 0)+ISNULL(TL.UDel, 0)+ISNULL(ts.PSales, 0) AS Total
FROM bc b
     LEFT JOIN ILE il on b.[Item No_]=il.[Item No_]
                         and b.[Variant Code]=il.[Variant Code]
     left JOIN TL t on  b.[Item No_]=t.[Item No_]
                         and b.[Variant Code]=t.[Variant Code]
     left JOIN TSE ts ON b.[Item No_]=ts.[Item No_]
                         AND b.[Variant Code]=ts.[Variant Code];

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

4. There 56 left brackets and 56 right brackets in your code which could/should be replaced by nothing. They're necessitated by spaces in object names. Please, please, please never again, not even once, assign a name to an object in SQL which contains a space. When you reach this fork in the road there are 2 ways you can go. Either a combination of upper case and lower case, like VariantCode, or lower case and underscore, like variant_code, are permissible imo. For me (this is an editorial and my personal preference) I like lowercase and underscores. However, UpperLower is probably the more popular convention on SQL Server.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

glennyboy Old Hand Points: 348 More actions · Answer 6

Good day!

thank you for reply

i run your query, but im getting this error;

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 19

Incorrect syntax near ','.

Thank you!

query code error 1

Steve Collins SSCrazy Points: 2157 More actions · Answer 7

It's missing a comma at the end of line 6. There's not any sample data so I'm not able to run the code.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

glennyboy Old Hand Points: 348 More actions · Answer 8

Good day!

is it okay if i share to you the sample data table via dropbox or google sheet.

thank you again

Steve Collins wrote:

It's missing a comma at the end of line 6. There's not any sample data so I'm not able to run the code.

sql query error 1