Pass value in a Variable

Question

I want to pass the value of 1 of my columns in my SELECT statements in a Variable.

Here's my sample code:

DECLARE @TotalExpense DECIMAL(18,2)
SELECT a.[Posting Date] AS [Check Date],
    a.[Document No_] AS [Check Number],
    a.[Vendor No_],
    (
    SELECT SUM(CASE WHEN GLEntry.[VAT Amount] <> 0 THEN CAST(GLEntry.Amount AS DECIMAL(18,2)) ELSE 0 END) FROM [BLI$Detailed Vendor Ledg_ Entry] DtldVendLedger
    LEFT JOIN [BLI$G_L Entry] GLEntry
    ON GLEntry.[Document No_] = (SELECT VendLedger.[Document No_] FROM [BLI$Vendor Ledger Entry] VendLedger
                                 WHERE VendLedger.[Entry No_] = DtldVendLedger.[Vendor Ledger Entry No_])
    WHERE DtldVendLedger.[Entry Type] = 2 AND DtldVendLedger.[Initial Document Type] = 2 AND DtldVendLedger.[Document No_] = a.[Document No_]
        AND GLEntry.[Source Code] = 'PURCHASES' AND GLEntry.[G_L Account No_] NOT IN ('2003','1402','1403','1401')
    ) AS [Vatable],

    (
    SELECT SUM(CASE WHEN GLEntry.[VAT Amount] <> 0 THEN CAST(GLEntry.Amount * 0.12 AS DECIMAL(18,2)) ELSE 0 END) FROM [BLI$Detailed Vendor Ledg_ Entry] DtldVendLedger
    LEFT JOIN [BLI$G_L Entry] GLEntry
    ON GLEntry.[Document No_] = (SELECT VendLedger.[Document No_] FROM [BLI$Vendor Ledger Entry] VendLedger
                                 WHERE VendLedger.[Entry No_] = DtldVendLedger.[Vendor Ledger Entry No_])
    WHERE DtldVendLedger.[Entry Type] = 2 AND DtldVendLedger.[Initial Document Type] = 2 AND DtldVendLedger.[Document No_] = a.[Document No_]
        AND GLEntry.[Source Code] = 'PURCHASES' AND GLEntry.[G_L Account No_] NOT IN ('2003','1402','1403','1401')
    ) AS [Input Tax],

    (
    SELECT SUM(CASE WHEN GLEntry.[VAT Amount] = 0 THEN CAST(GLEntry.Amount AS DECIMAL(18,2)) ELSE 0 END) FROM [BLI$Detailed Vendor Ledg_ Entry] DtldVendLedger
    LEFT JOIN [BLI$G_L Entry] GLEntry
    ON GLEntry.[Document No_] = (SELECT VendLedger.[Document No_] FROM [BLI$Vendor Ledger Entry] VendLedger
                                 WHERE VendLedger.[Entry No_] = DtldVendLedger.[Vendor Ledger Entry No_])
    WHERE DtldVendLedger.[Entry Type] = 2 AND DtldVendLedger.[Initial Document Type] = 2 AND DtldVendLedger.[Document No_] = a.[Document No_]
        AND GLEntry.[Source Code] = 'PURCHASES' AND GLEntry.[G_L Account No_] NOT IN ('2003','1402','1403','1401')
    ) AS [Non-Vat]

I want to Sum the Vatable, Input Tax, and Nonvat then pass the value into a variable then that variable will be used in my SELECT statements.

Answer 1

I tried to rearrange your query a bit. In your query the part with the DtldVendLedger query inside the join condition should not be valid. If you got that to run I have no idea how.

Because of the way I think you're summing I don't think it matters whether you used outer joins or inner joins. The rows with null GLEntry count as zeroes anyway.

DECLARE @vatable DECIMAL(18, 2);
DECLARE @inputtax DECIMAL(18, 2);
DECLARE @nonvat DECIMAL(18, 2);

SELECT
    @vatable = SUM(
        CASE
            WHEN GLEntry.[VAT Amount] <> 0
            THEN CAST(GLEntry.Amount AS DECIMAL(18,2)) ELSE 0 END
    ),
    @nonvat = SUM(
        CASE
            WHEN GLEntry.[VAT Amount]  = 0
            THEN CAST(GLEntry.Amount AS DECIMAL(18,2)) ELSE 0 END
    )
FROM
    A as a /* I'm assuming there was a FROM referencing a table "a" */
    [BLI$Detailed Vendor Ledg_ Entry] DtldVendLedger
    INNER JOIN
        ON DtldVendLedger.[Document No_] = a.[Document No_]
    LEFT OUTER JOIN [BLI$Vendor Ledger Entry] VendLedger
        ON VendLedger.[Entry No_] = DtldVendLedger.[Vendor Ledger Entry No_]
    LEFT OUTER JOIN [BLI$G_L Entry] GLEntry
        ON GLEntry.[Document No_] = VendLedger.[Document No_]
WHERE 
        a.??? = ???
    AND DtldVendLedger.[Entry Type] = 2
    AND DtldVendLedger.[Initial Document Type] = 2 
    AND GLEntry.[Source Code] = 'PURCHASES'
    AND GLEntry.[G_L Account No_] NOT IN ('2003','1402','1403','1401');

SET @inputtax = 0.12 * @vatable;