View query without sub selecting T-SQL

Question

so I'm trying to build a view query but I keep failing using only joins so I ended up with this deformation.. Any tips on how I can write this query so I don't have to use 6 subselects? The FeeSum and PaymentSum can be null, so ideally I do not want those in my result set and I also wouldn't like results where the FeeSum and the PaymentSum are equal.

Quick note: client is the table where the clients informations are stored (name, adress, etc..) customer has a fk on client and is kind of a shell table for the client that store more information for the client, payment is a list of all payments a customer did, order is a list of all orders a customer did.

The goal is to get a list where we can track which customer has open fees to pay, based on the orders. It's a legacy project so don't ask why people can order before paying :)

SELECT 
    cu.Id as [CustomerId]
    , CASE
        WHEN cl.IsPerson = 1
        THEN cl.[AdditionalName] + ' ' + cl.[Name]
        ELSE cl.AdditionalName
        END as [Name]
    , cl.CustomerNumber
    , (SELECT SUM(o.Fee) FROM [publication].[Order] o WHERE o.[State] = 2 AND o.CustomerId = cu.Id) as [FeeSum]
    , (SELECT SUM(p.Amount) FROM [publication].[Payment] p WHERE p.CustomerId = cu.Id) as [PaymentSum]
    , (SELECT MAX(o.OrderDate) FROM [publication].[Order] o WHERE o.[State] = 2 AND o.CustomerId = cu.Id) as [LastOrderDate]
    , (SELECT MAX(p.PaymentDate) FROM [publication].[Payment] p WHERE p.CustomerId = cu.Id) as [LastPaymentDate]
    , (SELECT MAX(f.Created) FROM [client].[File] f WHERE f.TemplateName = 'Reminder' AND f.ClientId = cl.Id) as [LastReminderDate]
    , (SELECT MAX(f.Created) FROM [client].[File] f WHERE f.TemplateName = 'Warning' AND f.ClientId = cl.Id) as [LastWarningDate]
FROM 
    [publication].[Customer] cu
JOIN
    [client].[Client] cl
    ON cl.Id = cu.ClientId
WHERE
    cu.[Type] = 0

Thanks in advance and I hope I didn't do anything wrong.

Kind regards

Answer 1

You could rewrite the correlated subqueries to instead use joins:

SELECT
    cu.Id AS [CustomerId],
    CASE WHEN cl.IsPerson = 1
         THEN cl.[AdditionalName] + ' ' + cl.[Name]
         ELSE cl.AdditionalName END AS [Name],
    cl.CustomerNumber,
    o.FeeSum,
    p.PaymentSum,
    o.LastOrderDate,
    p.LastPaymentDate,
    f.LastReminderDate,
    f.LastWarningDate
FROM [publication].[Customer] cu
INNER JOIN [client].[Client] cl
    ON cl.Id = cu.ClientId
INNER JOIN
(
    SELECT CustomerId, SUM(Fee) AS [FeeSum], MAX(OrderDate) AS [LastOrderDate]
    FROM [publication].[Order]
    WHERE o.[State] = 2
    GROUP BY CustomerId
) o
    ON o.CustomerId = cu.Id
INNER JOIN
(
    SELECT CustomerId, SUM(Amount) AS [PaymentSum], MAX(PaymentDate) AS [LastPaymentDate]
    FROM [publication].[Payment]
    WHERE o.[State] = 2
    GROUP BY CustomerId
) p
    ON p.CustomerId = cu.Id
INNER JOIN
(
    SELECT ClientId,
           MAX(CASE WHEN TemplateName = 'Reminder' THEN Created END) AS [LastReminderDate],
           MAX(CASE WHEN TemplateName = 'Warning'  THEN Created END) AS [LastWarningDate]
    FROM [client].[File]
    GROUP BY ClientId
) f
    ON f.ClientId = cl.Id
WHERE
    cu.[Type] = 0;