I’m working on a database project that shows pending orders from OrderTable
. There’s also a second table called TruckTable
that stores percentage values for each order showing how much has been loaded. A third table LocationTable
contains shipping destination info.
SELECT
OT.OrderNumber, OT.LineItem, OT.OrderStatus, OT.RequiredDate,
(SELECT SUM(loadpercentage) AS TotalLoaded
FROM TruckTable TT
WHERE TT.OrderNumber = OT.OrderNumber
AND TT.LineItem = OT.LineItem) AS TotalLoaded
FROM
OrderTable OT
INNER JOIN
LocationTable LT ON OT.ShipTo = LT.CustomerID
WHERE
OT.OrderStatus <= 'E'
AND LT.Territory = '08'
AND (OT.RequiredDate >= '6/1/2025' AND OT.RequiredDate <= '6/7/2025')
The subquery calculates total percentage already loaded on trucks for each order. I need to modify this to only display orders where the total percentage is less than 100%. Some orders might have zero records in the truck table (0% loaded) while others could have partial loading. How can I add this condition to filter out fully loaded orders?
Interesting challenge! Try wrapping your query in a CTE or derived table - then you can filter on TotalLoaded after it’s calculated. Or use HAVING SUM(loadpercentage) < 100
if you’re grouping by order fields. What happens with orders that don’t have truck records? Do they show as null or zero?
Move your subquery to the WHERE clause and use COALESCE to handle null values when orders don’t have truck records. Replace your current WHERE conditions with this:
WHERE
OT.OrderStatus <= 'E'
AND LT.Territory = '08'
AND (OT.RequiredDate >= '6/1/2025' AND OT.RequiredDate <= '6/7/2025')
AND COALESCE((SELECT SUM(loadpercentage)
FROM TruckTable TT
WHERE TT.OrderNumber = OT.OrderNumber
AND TT.LineItem = OT.LineItem), 0) < 100
COALESCE makes sure orders with no truck records return 0 instead of null, so they’ll show up as unloaded orders. This keeps your existing query structure but adds the percentage filter you’re looking for.
you could also try a left join instead of subqueries. join your ordertable with a grouped trucktable on ordernumber and lineitem, then filter where the sum’s under 100 or null. might run faster than repeated subqueries depending on how much data ur working with.