CODEWITHSUNDEEP
sqlsql-serversubquery
Ryan Knell
Ryan Knell

Reputation: 11

Nested Queries, Best way to do this. Example Attached

Righto. So I have the following SQL Server query: 

SELECT        St.Financial_Account_ID, St.Business_ID, St.Financial_Account_Desc, St.Financial_Account_Type_ID, St.Financial_Account_Active, 
                     St.Financial_Account_OpeningBalance, St.CRTotal, St.DRTotal, CASE st.Financial_Account_Type_ID WHEN 1 THEN (DRTotal - CRTotal) 
                     WHEN 2 THEN (CRTotal - DRTotal) WHEN 3 THEN (CRTotal - DRTotal) WHEN 4 THEN (DRTotal - CRTotal) END AS Balance, 
                     Financial_Account_Type.Financial_Account_Type_Desc
FROM            (SELECT        Financial_Account_ID, Business_ID, Financial_Account_Desc, Financial_Account_Type_ID, Financial_Account_Active, 
                                                Financial_Account_OpeningBalance,
                                                    (SELECT        ISNULL(SUM(Financial_Transaction_Amount), 0) AS Expr1
                                                      FROM            Financial_Transaction AS Financial_Transaction_1
                                                      WHERE        (Financial_Transaction_CR_AccountID = FA.Financial_Account_ID) AND (Financial_Transaction_Date <= @ToDate)) AS CRTotal,
                                                    (SELECT        ISNULL(SUM(Financial_Transaction_Amount), 0) AS Expr1
                                                      FROM            Financial_Transaction AS Financial_Transaction_2
                                                      WHERE        (Financial_Transaction_DR_AccountID = FA.Financial_Account_ID) AND (Financial_Transaction_Date <= @ToDate)) AS DRTotal
                      FROM            Financial_Account AS FA) AS St INNER JOIN
                     Financial_Account_Type ON St.Financial_Account_Type_ID = Financial_Account_Type.Financial_Account_Type_ID
WHERE        (St.Financial_Account_Type_ID = 1 OR
                     St.Financial_Account_Type_ID = 2) AND (St.Business_ID = @BusinessID) AND (CASE st.Financial_Account_Type_ID WHEN 1 THEN (DRTotal - CRTotal) 
                     WHEN 2 THEN (CRTotal - DRTotal) WHEN 3 THEN (CRTotal - DRTotal) WHEN 4 THEN (DRTotal - CRTotal) END <> 0)

This gets my balance sheet, and its used in a telerik report hence the variables etc.

I now need to create a "Comparison Balance sheet" nothing all too complex about this, but to do that I need to generate the above report twice with different dates, and make a percentage changed column.

I can see how to do all this, but its gonna get pretty messy by the time I'm done and debugging something is going to be a chore, whats more this is going to be a pretty simple example once all is said and done. Its only going to get more complex from here.

What I am looking for is either:

A querybuilder type thingo that will simplify the debugging of the sub tables that I am generating, or is there a more procedural way to write these things... such as

Query Go Query Go

Does that sort of thing work? and how do I link everything together? havent found any articles, but then everything only ever seems to introduce basic select statements.

Happy to do my own reading, just need a push in the right direction.

Thanks for your help

Upvotes: 1

Views: 2651

Answers (1)

WOPR
WOPR

Reputation: 5393

Have a look at Common Table Expressions. They should make that code more maintainable, and may possibly improve performance.

http://msdn.microsoft.com/en-us/library/ms190766.aspx

Upvotes: 2

Related Questions