# Calculations With Two Joins

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

1. 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

``````    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'

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