Creating a sub query

[gchg]

This query...

BEGIN
DECLARE DocumentDate Date;
DECLARE AssessmentsNormal INTEGER;
SET DocumentDate = '2025-01-01';
SET AssessmentsNormal = (SELECT Dues_Nominal FROM Annual);

SELECT IsNull(Position, 0) AS 'Position', 
IsNull(Nom_Code, 0) as 'NomCode', 
IsNull(Nom_Name, 'NA')as 'NomName',
Category_Type,
(SELECT IsNull(SUM(Debit),0) FROM A_Nominal WHERE Nominal_Code = NomCode and Item_Date <= DocumentDate ) as 'Debit',
(SELECT IsNull(SUM(Credit),0) FROM A_Nominal WHERE Nominal_Code = NomCode and Item_Date <= DocumentDate ) as 'Credit',

CASE WHEN NomCode = 1100 THEN

0



ELSE
(SELECT IsNull(SUM(Debit), 0) - IsNull(SUM(Credit),0) FROM A_Nominal WHERE Nominal_Code = NomCode and Item_Date <= DocumentDate )
END

AS 'Balance'
FROM Balance_Sheet_Templates
WHERE Form_ID = 1 AND Category_TYPE = 8
ORDER BY Category_Type, Position 


END

Produced this output

SQL Output

...and this query...

BEGIN 
DECLARE AssessmentsNormal INTEGER;
DECLARE DocumentDate Date;
SET AssessmentsNormal = (SELECT Dues_Nominal FROM Annual);
SET DocumentDate = '2025-01-01';
SELECT Nominal_Code as 'NomCode',NL_Codes.NL_Description as 'NomDesc', 

(SELECT IsNull(SUM(SUBQUERY.Totals), 0) as 'SQ' FROM
(SELECT Customer_ID as 'vID', 
CASE WHEN A_Sales_Ledger.Nominal_Code =  AssessmentsNormal  THEN 
(SELECT IsNull(SUM(Debit) - SUM(Credit), 0) AS 'Totals' FROM A_Sales_Ledger WHERE Document_Date <= DocumentDate AND (Nominal_Code = NomCode OR Nominal_Code = 0) AND Customer_ID = vID) 
ELSE 
(SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE Document_Date <= DocumentDate AND Nominal_Code = NomCode AND Customer_ID = vID) 
END 
AS 'Totals' 
FROM Customers 
WHERE Totals > 0 
GROUP BY Customer_ID) 
AS SUBQUERY) 
FROM A_Sales_Ledger 
JOIN NL_Codes ON NL_Codes.NL_Code = A_Sales_Ledger.Nominal_Code 
WHERE Nominal_Code != 0 
GROUP BY Nominal_Code, NL_Description 
ORDER BY Nominal_Code
End

...produces this output...

This is a subset of the NomCode 1100 in the first table (at the moment it's set to zero as I try to figure it out). 

What I am trying to achieve is something like this

NomCode	NomName
1007	Escrow Account
1100	4000 HOA Fees
	4001 Bridge Ass
	4005 Water Co

Whilst I can achieve it running queries back and forth, I can also read 'War and Peace' from cover to cover waiting for the results so trying to make it a lot faster 🙂