Joining 2 queries to match Months side by side I have 2 SQL statements that I want to join up. The first gets the actual data per month for Turnover accounts. The second query gets budgets (if any) for the same Turnover accounts.

Query 1 Actuals

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
FROM OACT T0
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
LEFT JOIN OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] 
WHERE T0.GroupMask = 4 AND T0.Levels = 5
AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q
PIVOT
(
    SUM(Amount) 
    FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query


Sample data


Query 2 Budgets

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'AcctCode', T3.AcctName AS 'Description', T3.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) THEN T1.DebLTotal ELSE T1.CredLTotal END AS BudgetAmt, T1.Line_ID AS 'LineID'
FROM OBGT T0 INNER JOIN BGT1 T1 ON T0.[AbsId] = T1.[BudgId]
INNER JOIN OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
WHERE T3.GroupMask = 4) AS q
PIVOT
(
    SUM(BudgetAmt)
    FOR LineID IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])
) AS P


Sample Data


For the 2nd query, 0 represents Jan, 1 represents Feb ... and 11 represents December.

How do I combine the 2 queries such that I can get data such as below?



Not a duplicate as this contains a query to retrieve budgets.
use a union all 

the same way you have used in your other question, combine 2 queries

the idea is exactly the same.

All you have to do is 

select * from 
(
select [set of columns]
from query1
union all
select [same set of columns as before]
from query2

) as Q
PIVOT
(
SUM(Amount) 
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query


I think, 
if I understood it right, I got it modified for you, see the picture below.

It is a UNION ALL plus a CASE.

creating the tables to get the exercise done was a bit troublesome.



    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )


    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )


    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int

    )



    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)



    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)


    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4


    insert into @OBGT 
    select 
    AcctCode,
    AbsId,
    CredLTotal, 
    DebLTotal,
    Line_ID
    from @BGT1


    --select * from @OBGT 

    --select * from @OACT

    --select * from @BGT1


    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate



    SELECT T0.AcctCode AS 'SAP Code', 
           T3.AcctName AS 'Description', 
           T3.FatherNum, 1 AS 'Group', 
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) 
THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0 
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
    WHERE T3.GroupMask = 4



    SELECT * FROM
    (

    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate

    UNION ALL

    SELECT T0.AcctCode AS 'SAP Code', 
           T3.AcctName AS 'Description', 
           T3.FatherNum, 1 AS 'Group', 
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) 
           THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0 
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
    WHERE T3.GroupMask = 4


    ) AS q
    PIVOT
    (
        SUM(Amount) 
        FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS query




I believe, if I understood well, you want something like the picture above.

if that is not right, then, please create a picture of what you need, even if you do it in excel, but try to use the values according to the tables in the script below. Or post your table definitions with some scripts inserting data into them, then we would be definitely on the same page.

Also, I have not included all of your columns, if they are really important you can add them to the script below.

 SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )


    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )


    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int, 
    DebLTotal int,
    Line_ID int

    )



    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)



    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311215',4,'20151231',5,2)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311216',4,'20151231',5,3)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311219',4,'20151030',5,3)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311217',5,'20151231',8,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311218',6,'20151231',4,6)


        insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)


    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)


    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4


    insert into @OBGT 
    select 
    AcctCode,
    AbsId,
    CredLTotal, 
    DebLTotal,
    Line_ID
    from @BGT1


    --select * from @OBGT 

    --select * from @OACT

    --select * from @BGT1

    declare @months table(
     i smallint primary key clustered,
     name varchar(25))

     insert into @months
     select 1,'jan'
        union all select
            2, 'feb'
        union all select 
            3, 'mar'
        union all select 
            4, 'apr'
        union all select 
            5, 'may'
        union all select 
            6, 'jun'
        union all select 
            7, 'jul'
        union all select 
            8, 'aug'
        union all select 
            9, 'sep'
        union all select 
            10, 'out'
        union all select 
            11, 'nov'
        union all select 
            12, 'dec'


SELECT 
 K.[SAP Code]
,K.Description,
[jan_Budget],
[jan_Amount],[feb_Budget],
[feb_Amount],[mar_Budget],
[mar_Amount],[apr_Budget],
[apr_Amount],[may_Budget],
[may_Amount],[jun_Budget],
[jun_Amount],[jul_Budget],
[jul_Amount],[aug_Budget],
[aug_Amount],[sep_Budget],
[sep_Amount],[out_Budget],
[out_Amount],[nov_Budget],
[nov_Amount],[dec_Budget],
[dec_Amount]

FROM

(
    SELECT A.* 
      FROM (

                SELECT K.[SAP Code]
                      ,K.[Description]
                      ,AMOUNT = COALESCE(K.Amount,0)
                      ,PIVOT_KEY = M.NAME + '_Amount'
                FROM @months M 
                     LEFT OUTER JOIN 

                        (   SELECT T0.AcctCode AS 'SAP Code', 
                                   T0.AcctName AS 'Description', 
                                   T0.FatherNum, 1 AS 'Group', 
                                   'Turnover' AS [Definition],
                            MONTH(T1.RefDate) AS 'Month', 
                            SUM(T1.Debit - T1.Credit) AS 'Amount'
                            FROM @OACT T0
                            LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
                            WHERE T0.GroupMask = 4 AND T0.Levels = 5
                            AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
                            GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate
                        ) K
                        ON K.Month = M.i

           ) RADHE
    PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Amount],[feb_Amount],[mar_Amount],
                                         [apr_Amount],[may_Amount],[jun_Amount],
                                         [jul_Amount],[aug_Amount],[sep_Amount],
                                         [out_Amount],[nov_Amount],[dec_Amount])) A
    where [SAP Code] is not null


) K  

INNER JOIN

(

SELECT *
  FROM (

              SELECT K.[SAP Code]
                  ,K.[Description]
                  ,AMOUNT = COALESCE(K.Amount,0)
                  ,PIVOT_KEY = M.NAME + '_Budget'
            FROM @months M 
                 LEFT OUTER JOIN 

                    (   

                        SELECT T0.AcctCode AS 'SAP Code', 
                               T3.AcctName AS 'Description', 
                               T3.FatherNum, 1 AS 'Group', 
                                T1.Line_ID+1 AS 'Month',

                               CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) 
                    THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

                              FROM @OBGT T0 
                        INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
                        INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
                        WHERE T3.GroupMask = 4

                    ) K
                    ON K.Month = M.i

       ) RADHE
PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Budget],[feb_Budget],[mar_Budget],
                                     [apr_Budget],[may_Budget],[jun_Budget],
                                     [jul_Budget],[aug_Budget],[sep_Budget],
                                     [out_Budget],[nov_Budget],[dec_Budget])) B

) R
ON 1=1
AND  K.[SAP Code] = R.[SAP Code]