Calculations With Two Joins

Johnathan Simpson 586 Reputation points
2022-01-17T17:35:28.393+00:00

Hello, trying to calculate how many line items for an order shipped dduring a designated time period. I have the below sample DDL and query, but my query is not producing accurate results at all. For example, it outputs ABC = 16, which if you look at the data ABC ONLY has 6, and the output never shows DEF?

What am I missing here, how should this query be changed to produce accurate output?
Create Table __ShipLineItemInfo
(
Id int IDENTITY(1,1) PRIMARY KEY,
ShipId int NOT NULL,
ShipLineId nvarchar(max) NOT NULL
)

 Create Table __ShipmentsData 
 (
     Id int IDENTITY(1,1) PRIMARY KEY,
     ShipId int NOT NULL ,
     ShipCost decimal(18,2) NOT NULL,
     TrackingNumber varchar(max) NOT NULL,
     ShipOrderNumber varchar(max) NOT NULL
 )

 Create Table __Shipment 
 (
     Id int IDENTITY(1,1) PRIMARY KEY,
     ShipId int NOT NULL ,
     ShipDate datetime2
)
INSERt INTO __Shipment VALUES
(23692, '2021-12-10 06:40:07.4370000')
,(23708, '2021-12-19 06:40:07.4370000')
,(23710, '2021-12-01 06:40:07.4370000')
 INSERT INTO __ShipLineItemInfo VALUES
 (23692, 'ABC-7')
 ,(23708, 'ABC-1')
 ,(23708, 'ABC-2')
 ,(23708, 'ABC-3')
 ,(23708, 'ABC-4')
 ,(23708, 'ABC-5')
 ,(23710, 'DEF-1')
 ,(23710, 'DEF-2')


 INSERT INTO __ShipmentsData VALUES
 (23692, '13.60', '111111111111', 'ABC')
 ,(23708, '18.07', '222222222222', 'ABC')
 ,(23708, '14.33', '333333333333', 'ABC')
 ,(23708, '14.33', '444444444444', 'ABC')
 ,(32710, '18.19', '999999999999', 'DEF')





Select
sd.ShipOrderNumber
,TotalLines = COUNT(sl.ShipLineId)
FROM __ShipmentsData sd
JOIN __ShipLineItemInfo sl
ON sd.ShipId = sl.ShipId
JOIN __Shipment sp
ON sp.ShipId = sl.ShipId
where sp.ShipDate >= '12/01/2021'
AND sp.ShipDate <= '12/31/2021'
GROUP BY sd.ShipOrderNumber
ORDER BY sd.ShipOrderNumber
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-01-18T02:59:34.503+00:00

    Hi,@Johnathan Simpson
    You need to understand join first, refer to this document for details
    In your query :

        FROM __ShipmentsData sd    
          JOIN __ShipLineItemInfo sl   
          ON sd.ShipId = sl.ShipId  
    

    As is shown in the picture below, For each shipid in table sd, it will match the same shipid in table sl, so for '23692', there is 1 corresponding shipid in table sl; then for 3 repeated '23708' in table sd, each ' 23708' has 5 corresponding shipid in table sl, a total of 3×5=15 values are obtained, which eventually leads to 1+15=16 'ABC'
    Finally, why there is no 'DEF', because the '32710' in the table sd insert should be '23710'
    165836-image.png
    You can use select distinct to remove the duplicate Shipid in table sd,like this:

     Select sd.ShipOrderNumber ,TotalLines = COUNT(sl.ShipLineId)  
     FROM (select distinct Shipid,ShipOrderNumber from #__ShipmentsData )sd   
          JOIN #__ShipLineItemInfo sl ON sd.ShipId = sl.ShipId  
          JOIN #__Shipment sp ON sp.ShipId = sl.ShipId  
     where sp.ShipDate >= '12/01/2021' AND sp.ShipDate <= '12/31/2021'  
     GROUP BY sd.ShipOrderNumber  
     ORDER BY sd.ShipOrderNumber   
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-01-17T23:03:14.887+00:00

    Hey, that is exactly the same problem that I help you with yesterday. Did you get what was wrong with that one? You have many rows in both tables with id 23708, so if you join directly on that key, you get the multiplication of the tables. You must aggregate first and then join - just like I showed you yesterday.

    As for why DEF not showing up, I think that the answer is that you have a typo.

    0 comments No comments