CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Kinyanjui Kamau
Kinyanjui Kamau

Reputation: 1936

Combining two queries to add additional column

I have two SQL queries that I want to combine into one. The 2nd should add an additional column to the 1st.

Query #1:

SELECT 
    T3.AcctCode, T3.AcctName,
    SUM(CASE WHEN T0.TaxDate BETWEEN '2015-12-01' AND '2015-12-31'
             THEN T1.Debit - T1.Credit 
             ELSE 0 END)* -1 AS [Balance],
    SUM(T1.Debit - T1.Credit)* -1  AS [YTD Balance],
    (CASE WHEN T3.AcctCode BETWEEN '611110' AND '612000'
       THEN 1 WHEN T3.AcctCode BETWEEN '541100' AND '541950'
       THEN 2 WHEN T3.AcctCode BETWEEN '542100' AND '542700'
       THEN 3 WHEN T3.AcctCode = '543100'
       THEN 4 WHEN T3.AcctCode BETWEEN '511100' AND '511950'
       THEN 5 WHEN T3.AcctCode BETWEEN '512100' AND '512995'
       THEN 6 WHEN T3.AcctCode = '513100'
       THEN 7 ELSE 0 END) AS [Group]
FROM 
    OJDT T0
INNER JOIN 
    JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN 
    OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN 
    OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE 
    T0.TaxDate BETWEEN '2015-04-01' AND '2015-12-31'     
    AND T2.GroupMask IN(5,6)
GROUP BY 
    T3.AcctCode, T3.AcctName

Sample Output

enter image description here

Query #2:

SELECT 
    T1.FatherNum, SUM(T0.CredLTotal) AS [Budget]
FROM 
    OBGT T0
LEFT JOIN 
    OACT T1 ON T0.[AcctCode] = T1.[AcctCode]
WHERE 
    T1.GroupMask IN (5, 6) 
    AND (T1.FatherNum BETWEEN '511100' AND '612000') 
    AND YEAR(T0.FinancYear) = 2015 
GROUP BY 
    T1.FatherNum

Sample Output

enter image description here

I have come up with this:

SELECT 
    T3.AcctCode, T3.AcctName,
    SUM(CASE WHEN T0.TaxDate BETWEEN '2015-12-01' AND '2015-12-31'
             THEN T1.Debit - T1.Credit 
             ELSE 0 END)* -1 AS [Balance],
    SUM(T1.Debit - T1.Credit)* -1  AS [YTD Balance],
    SUM(T4.CredLTotal) AS [Budget],
    (CASE WHEN T3.AcctCode BETWEEN '611110' AND '612000'
       THEN 1 WHEN T3.AcctCode BETWEEN '541100' AND '541950'
       THEN 2 WHEN T3.AcctCode BETWEEN '542100' AND '542700'
       THEN 3 WHEN T3.AcctCode = '543100'
       THEN 4 WHEN T3.AcctCode BETWEEN '511100' AND '511950'
       THEN 5 WHEN T3.AcctCode BETWEEN '512100' AND '512995'
       THEN 6 WHEN T3.AcctCode = '513100'
       THEN 7 ELSE 0 END) AS [Group]
FROM 
    OJDT T0
INNER JOIN 
    JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN 
    OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN 
    OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
LEFT JOIN 
    OBGT T4 ON (T4.[AcctCode] = T2.[AcctCode] 
            AND YEAR(T4.FinancYear) = 2015)
WHERE 
    T0.TaxDate BETWEEN '2015-04-01' AND '2015-12-31'     
    AND T2.GroupMask IN (5, 6)     
GROUP BY 
    T3.AcctCode, T3.AcctName, T2.FatherNum
ORDER BY 
    T3.AcctCode

Sample Output

enter image description here

However, the column Balance shows incorrect figures, i.e. not returning the results of the 2nd query.

How do I get SUM(T4.CredLTotal) AS [Budget] to sum properly?

Upvotes: 1

Views: 137

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Without looking into details of these complex queries, it looks like you want to simply join two queries together.

In the first query you group by AcctCode, AcctName. I assume that result will have one row per AcctCode, i.e. AcctCode will not repeat in the result set.

In the second query you group by FatherNum, so it will not repeat in the result set as well.

All you need is join them together. I repeated your queries as Common Table Expressions (CTE) and joined them.

WITH
CTE_Query1
AS
(
    SELECT 
        T3.AcctCode, T3.AcctName,
        SUM(CASE WHEN T0.TaxDate BETWEEN '2015-12-01' AND '2015-12-31'
                 THEN T1.Debit - T1.Credit 
                 ELSE 0 END)* -1 AS [Balance],
        SUM(T1.Debit - T1.Credit)* -1  AS [YTD Balance],
        (CASE WHEN T3.AcctCode BETWEEN '611110' AND '612000'
           THEN 1 WHEN T3.AcctCode BETWEEN '541100' AND '541950'
           THEN 2 WHEN T3.AcctCode BETWEEN '542100' AND '542700'
           THEN 3 WHEN T3.AcctCode = '543100'
           THEN 4 WHEN T3.AcctCode BETWEEN '511100' AND '511950'
           THEN 5 WHEN T3.AcctCode BETWEEN '512100' AND '512995'
           THEN 6 WHEN T3.AcctCode = '513100'
           THEN 7 ELSE 0 END) AS [Group]
    FROM 
        OJDT T0
    INNER JOIN 
        JDT1 T1 ON T0.[TransId] = T1.[TransId]
    INNER JOIN 
        OACT T2 ON T1.[Account] = T2.[AcctCode]
    INNER JOIN 
        OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
    WHERE 
        T0.TaxDate BETWEEN '2015-04-01' AND '2015-12-31'     
        AND T2.GroupMask IN(5,6)
    GROUP BY 
        T3.AcctCode, T3.AcctName
)
,CTE_Query2
AS
(
    SELECT 
        T1.FatherNum, SUM(T0.CredLTotal) AS [Budget]
    FROM 
        OBGT T0
    LEFT JOIN 
        OACT T1 ON T0.[AcctCode] = T1.[AcctCode]
    WHERE 
        T1.GroupMask IN (5, 6) 
        AND (T1.FatherNum BETWEEN '511100' AND '612000') 
        AND YEAR(T0.FinancYear) = 2015 
    GROUP BY 
        T1.FatherNum
)
SELECT *
FROM
    CTE_Query1
    INNER JOIN CTE_Query2 ON CTE_Query2.FatherNum = CTE_Query1.AcctCode
;

Upvotes: 1

Related Questions