Would this be a join?

Question

Would this be a join?

nbeason

SSC Enthusiast

Points: 181
More actions
April 27, 2021 at 8:01 pm

#3876493

Would this be a join of some kind?
I've got two select statements, one with a sum feature, that I'd like to result in all the columns from the first statement with the addition of a final column "TotalShipped" from the second statement based on a primary key of "Item". I've been doing some reading on Joins with aggregate functions but I don't know if I'm headed in the correct direction.
The first statement is:
SELECT Item,ForecastDemandQty,ForecastDemandMAD,MinShelfQty,MaxShelfQty,QuantityOnHand,QuantityCommitted,QuantityAllocated,QuantityReleased,ExcludeFromDistraNet
FROM tblimItemLoc
WHERE Location = '01' AND VendorPurchaseFrom LIKE 'PHHPD%'
ORDER BY ForecastDemandMAD DESC
The second statement is:
SELECT Item,SUM(TxQty) AS 'TotalShipped'
FROM IMInvTxHistory
WHERE Location = '01' AND TxDate > '2021-01-01' AND TxCode LIKE 'SHIP%'
Group By Item

Viewing 4 posts - 1 through 3 (of 3 total)

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

ScottPletcher SSC Guru Points: 101103 More actions · Answer 1

Yes, you could use a JOIN.

SELECT IL.Item,ForecastDemandQty,ForecastDemandMAD,MinShelfQty,MaxShelfQty,
QuantityOnHand,QuantityCommitted,QuantityAllocated,QuantityReleased,ExcludeFromDistraNet,
ITH.TotalShipped

FROM tblimItemLoc IL

LEFT OUTER JOIN (
    SELECT Item,SUM(TxQty) AS 'TotalShipped'
    FROM IMInvTxHistory
    WHERE Location = '01' AND TxDate > '2021-01-01' AND TxCode LIKE 'SHIP%'
    Group By Item
) AS ITH ON
    ITH.Item = IL.Item

WHERE Location = '01' AND VendorPurchaseFrom LIKE 'PHHPD%'

ORDER BY ForecastDemandMAD DESC

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

nbeason SSC Enthusiast Points: 181 More actions · Answer 2

nbeason

SSC Enthusiast

Points: 181

April 27, 2021 at 8:50 pm

#3876543

Thank You!

That is fantastic

ScottPletcher SSC Guru Points: 101103 More actions · Answer 3

You're welcome! Thanks for the feedback.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".