I am trying to get the margin taking the driver pay from the amount billed. In this query I am not sure how to do the code margin in which one set of data is in a sub query, and the other set of data is outside the sub query.
`Select
Customer.Customercode ClientCode,
Customer.Name ClientName,
(select Count(OrderMain.OrderID)
From OrderMain with (readuncommitted)
Left Join Customer with (readuncommitted)
On Ordermain.CustomerID=Customer.CustomerID
Where
Customer.Name like 'ABC%' and
OrderMain.OrderStatus not in ('X') and
OrderMain.OrderDate between '1/1/19' and '12/31/19') JobCount,
(select sum(OrderFee.FeeAmount)
From
OrderFee with (readuncommitted)
Inner Join OrderMain with (readuncommitted) On OrderFee.OrderID=OrderMain.OrderID
Left Join Customer with (readuncommitted) On Ordermain.CustomerID=Customer.CustomerID
Where
Customer.Name like 'ABC%' and
OrderMain.OrderStatus not in ('X') and
OrderMain.OrderDate between '1/1/19' and '12/31/19') AmountBilled,
sum(JobDriver.DriverPay) DriverPay
From
OrderMain with (readuncommitted)
Left Join Customer with (readuncommitted) On Ordermain.CustomerID=Customer.CustomerID
Inner Join Job With (readuncommitted) ON OrderMain.OrderID = Job.OrderID
Inner Join JobDriver With (readuncommitted) ON Job.JobID = JobDriver.JobID
Where
Customer.Name like 'ABC%' and
OrderMain.OrderStatus not in ('X') and
OrderMain.OrderDate between '1/1/19' and '12/31/19'
Group By
Customer.Customercode,
Customer.Name
Order By
Customer.Name
`
Plus it's a drag that I have the yearly date range in three places!
Without DDL and sample data, it will be difficult to help you. Not sure why you have all the hints there, you might as well set the transaction level to read uncommitted before you run any queries.
Please try the query below. The hints look fine to me. But you might want to consider using (shorter) alias names for tables, such as "FROM OrderMain OM with ..." or "Join Customer AS Cus with ...".
SELECT
Matching_Orders.CustomerID,
Matching_Orders.ClientName,
COUNT(Matching_Orders.OrderID) AS JobCount,
SUM(AmountBilled) AS AmountBilled,
SUM(DriverPay) AS DriverPay
FROM (
Select OrderMain.CustomerID, OrderMain.OrderID, Customer.Name AS ClientName
From OrderMain with (readuncommitted)
Inner Join Customer with (readuncommitted)
On Ordermain.CustomerID=Customer.CustomerID
Where
Customer.Name like 'ABC%' and
OrderMain.OrderStatus not in ('X') and
OrderMain.OrderDate between '1/1/19' and '12/31/19'
) AS Matching_Orders
OUTER APPLY (
select sum(OrderFee.FeeAmount) AS AmountBilled
From
OrderFee with (readuncommitted)
Where OrderFee.OrderID = Matching_Orders.OrderID
) AS OrderFee
OUTER APPLY (
select sum(JobDriver.DriverPay) DriverPay
From Job With (readuncommitted)
Inner Join JobDriver With (readuncommitted) ON Job.JobID = JobDriver.JobID
Where MatchingOrders.OrderID = Job.OrderID
) AS JobDriver
GROUP BY
Matching_Orders.ClientName,
Matching_Orders.CustomerID
Order By
Matching_Orders.Name
Thanks a million, this is perfect. I have been trying to find the right code for this for over a year. I was not aware of that outer apply. It was not in any of the books I have. I also found cross apply when I did a search on the outer apply.