SQLTeam.com | Weblogs | Forums

Need help with the right code to get the margin

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.

First, welcome!

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

Scott

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.

Again thanks for the help.
TonyCel.

You're very welcome, glad it helped!