CODEWITHSUNDEEP
sqlsql-server
rigamonk
rigamonk

Reputation: 1181

Make Unioned Queries show on the same row

I have a set of queries that go by each month. They hit against a set of account numbers, but when they return they show a staggered result:

554000  355 Transportation  COS-Fuel    NULL                    68652.230000000000
554000  355 Transportation  COS-Fuel    48923.270000000000  NULL

I would like them to be joined together (that's only two months, it'll get messy with ass 12)

554000  355 Transportation  COS-Fuel  48923.270000000000   68652.230000000000

Here's how i have it set up:

    ---JAN Actuals
set @month = '1'
 SET @1Start = (SELECT StartDate FROM MiscReportTables.dbo.FiscalCalendar WHERE @year = [Year] AND Period = 1)
 SET @1End = (SELECT EndDate FROM MiscReportTables.dbo.FiscalCalendar WHERE @year = [Year] AND Period = 1)
Select distinct 
    LEDGERTRANS.Dimension as 'Department', 
    DIMENSIONS.DESCRIPTION as 'Division', 
    LedgerTable.AccountName as 'GL Description',
Case When @month = '1' Then SUM(AMOUNTMST) END as '1',
Case When @month = '2' Then SUM(AMOUNTMST)  END as '2',
Case When @month = '3' Then SUM(AMOUNTMST)  END as '3',
Case When @month = '4' Then SUM(AMOUNTMST) END  as '4',
Case When @month = '5' Then SUM(AMOUNTMST)  END as '5',
Case When @month = '6' Then SUM(AMOUNTMST) END  as '6',
Case When @month = '7' Then SUM(AMOUNTMST) END  as '7',
Case When @month = '8' Then SUM(AMOUNTMST)  END as '8',
Case When @month = '9' Then SUM(AMOUNTMST)  END as '9',
Case When @month = '10' Then SUM(AMOUNTMST)  END as '10',
Case When @month = '11' Then SUM(AMOUNTMST)  END as '11',
Case When @month = '12' Then SUM(AMOUNTMST)  END as '12'
Into #tempJan
from LEDGERTRANS 
Join LedgerTable on LedgerTable.Accountnum = Ledgertrans.AccountNum
Join Dimensions on NUM = LEDGERTRANS.Dimension2_
where transdate BETWEEN @1Start AND @1End and LedgerTrans.Dimension2_ in (@division)
Group By LEDGERTRANS.AccountNum,LEDGERTRANS.Dimension,LEDGERTRANS.Dimension2_,LedgerTable.AccountName,DIMENSIONS.DESCRIPTION

---FEB Actuals
set @month = '2'
 SET @2Start = (SELECT StartDate FROM MiscReportTables.dbo.FiscalCalendar WHERE @year = [Year] AND Period = dbo.fnGetPeriod(2,@month))
 SET @2End = (SELECT EndDate FROM MiscReportTables.dbo.FiscalCalendar WHERE @year = [Year] AND Period = dbo.fnGetPeriod(2,@month))
Select distinct 
    LEDGERTRANS.Dimension as 'Department', 
    DIMENSIONS.DESCRIPTION as 'Division', 
    LedgerTable.AccountName as 'GL Description',
Case When @month = '1' Then SUM(AMOUNTMST) END as '1',
Case When @month = '2' Then SUM(AMOUNTMST)  END as '2',
Case When @month = '3' Then SUM(AMOUNTMST)  END as '3',
Case When @month = '4' Then SUM(AMOUNTMST) END  as '4',
Case When @month = '5' Then SUM(AMOUNTMST)  END as '5',
Case When @month = '6' Then SUM(AMOUNTMST) END  as '6',
Case When @month = '7' Then SUM(AMOUNTMST) END  as '7',
Case When @month = '8' Then SUM(AMOUNTMST)  END as '8',
Case When @month = '9' Then SUM(AMOUNTMST)  END as '9',
Case When @month = '10' Then SUM(AMOUNTMST)  END as '10',
Case When @month = '11' Then SUM(AMOUNTMST)  END as '11',
Case When @month = '12' Then SUM(AMOUNTMST)  END as '12'
Into #tempFeb
from LEDGERTRANS 
Join LedgerTable on LedgerTable.Accountnum = Ledgertrans.AccountNum
Join Dimensions on NUM = LEDGERTRANS.Dimension2_
where transdate BETWEEN @2Start AND @2End and LedgerTrans.Dimension2_ in (@division)
Group By LEDGERTRANS.AccountNum,LEDGERTRANS.Dimension,LEDGERTRANS.Dimension2_,LedgerTable.AccountName,DIMENSIONS.DESCRIPTION


SELECT distinct LedgerTable.AccountNum,
    Department, 
    Division, 
    [GL Description], 
    [1],
    [2]
from #tempFeb join LedgerTable on LedgerTable.AccountName = #tempFeb.[GL Description]

union
SELECT distinct LedgerTable.AccountNum,
    Department, 
    Division, 
    [GL Description], 
    [1],
    [2]
from #tempJan join LedgerTable on LedgerTable.AccountName = #tempJan.[GL Description]

drop table #tempJan
drop table #tempFeb

Upvotes: 0

Views: 70

Answers (2)

fnightangel
fnightangel

Reputation: 426

Other option maybe is aggregate:

SELECT
    T.AccountNum,
    T.Department,
    T.Division,
    SUM(ISNULL(T.JAN, 0)) AS JAN,
    SUM(ISNULL(T.FEB, 0)) AS FEB
FROM (
    SELECT distinct 
        LedgerTable.AccountNum,
        Department, 
        Division, 
        [GL Description], 
        [1] AS JAN,
        [2] AS FEB
    FROM 
        #tempFeb
    JOIN LedgerTable 
        ON LedgerTable.AccountName = #tempFeb.[GL Description]

    UNION

    SELECT distinct 
        LedgerTable.AccountNum,
        Department, 
        Division, 
        [GL Description], 
        [1] AS JAN,
        [2] AS FEB
    FROM 
        #tempJan 
    JOIN LedgerTable 
        ON LedgerTable.AccountName = #tempJan.[GL Description]
) T
GROUP BY
    T.AccountNum,
    T.Department,
    T.Division

Upvotes: 2

Andrew
Andrew

Reputation: 8703

You'll need to use a join instead of a union. Something like:

select
t1.department,
t1.division,
t1.[GL Description],
t1.1,
t2.1
...
from

(
SELECT distinct LedgerTable.AccountNum,
    Department, 
    Division, 
    [GL Description], 
    [1],
    [2]
from #tempFeb join LedgerTable on LedgerTable.AccountName = #tempFeb.[GL Description]
) t1
inner join
(
SELECT distinct LedgerTable.AccountNum,
    Department, 
    Division, 
    [GL Description], 
    [1],
    [2]
from #tempJan join LedgerTable on LedgerTable.AccountName = #tempJan.[GL Description]
) t2
on t1.departement = t2.department
and t1.division = t2.division
and t1.[GL Description] = t2.[GL Description]

That's not exact code, in particular I don't understand your aliases (your columns named 1,2).

Upvotes: 1

Related Questions