October 3, 2014 at 11:04 pm
I'm trying to join multiple tables together using a full outer join, its getting close to the proper result but there are some duplicate rows due to the join clauses. I've got two temp tables with columns of Accountid, Accountcode,Accountname,level1,datemonth,Actual_CurrentMonth,Budget_CurrentMonth. I'm looking to get a table with one row for each Accountid, datemonth pair that has all the values from each of the tables.
Here is the query so far : -
SELECT
AccountID,
AcctCode,
AcctName,
level1,
CAST (datemonth as INT),
max(Actual_CurrentMonth),
max(Budget_CurrentMonth)
FROM
(
SELECT
aa.AccountID,
aa.AcctCode,
aa.AcctName,
aa.Level1,
CAST (aa.datemonth as INT) as datemonth,
isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1
WHERE a1.AccountID = aa.AccountID and month (@FROMDATE) = aa.datemonth and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0)
As Actual_CurrentMonth,
0 as Budget_CurrentMonth
FROM
#MainAccount aa
UNION ALL
SELECT
bb.AccountID,
bb.AcctCode,
bb.AcctName,
bb.Level1,
CAST (bb.datemonth as INT) as datemonth,
0 AS Actual_CurrentMonth,
isnull((select (SUM(b1.BudgetDebit) - SUM(b1.BudgetCredit) ) from #BudgetAccount b1
where bb.AccountID=b1.AccountID and month (@FROMDATE) = bb.datemonth and b1.PeriodStartdate
between @FROMDATE and @TODATE group by b1.group1 , b1.group2 ),0)As Budget_CurrentMonth
FROM
#BudgetAccount bb
) x
GROUP BY
AccountID, AcctCode, AcctName, datemonth,Actual_CurrentMonth,Budget_CurrentMonth,Level1
ORDER BY CAST (datemonth as INT) asc
This almost works, but I get some duplicate rows . For instance, I'll get something like:
_SYS0000000077540010101 20 240Agency (CWT, C&F)4000000000000009-43397.2700000.000000
_SYS0000000077540010101 20 240Agency (CWT, C&F)40000000000000090.000000-4268529.920000
Whereas I would like something like :-
AccountIDAcctCodeAcctNameLevel1DateMonthActual_CurrentMonthBudget_CurrentMonth
_SYS0000000077540010101 20 240Agency (CWT, C&F)4000000000000009-43397.270000-4268529.920000
Is there a way to flatten or merge these rows together ?
October 3, 2014 at 11:43 pm
Quick thought, use NULLIF or skip the ISNULL in the inner query for Actual_CurrentMonth and Budget_CurrentMonth to return NULL instead of 0, then remove those two columns from the group by clause to merge them in the output. If you post a DDL and sample data insert, we can further on this.
😎
Example
SELECT
X.AccountID
,X.AcctCode
,X.AcctName
,X.level1
,X.MONTH_NO
,MAX(NULLIF(X.Actual_CurrentMonth,0)) AS Actual_CurrentMonth
,MAX(NULLIF(X.Budget_CurrentMonth,0)) AS Budget_CurrentMonth
FROM (VALUES
('_SYS00000000775','40010101 20 240','Agency (CWT, C&F)','400000000000000',9,-43397.270000,0.000000 )
,('_SYS00000000775','40010101 20 240','Agency (CWT, C&F)','400000000000000',9,0.000000 ,-4268529.920000)
) AS X(AccountID
,AcctCode
,AcctName
,level1
,MONTH_NO
,Actual_CurrentMonth
,Budget_CurrentMonth)
GROUP BY X.AccountID
,X.AcctCode
,X.AcctName
,X.level1
,X.MONTH_NO
Result
AccountID AcctCode AcctName level1 MONTH_NO Actual_CurrentMonth Budget_CurrentMonth
--------------- --------------- ----------------- --------------- ----------- -------------------- --------------------
_SYS00000000775 40010101 20 240 Agency (CWT, C&F) 400000000000000 9 -43397.270000 -4268529.920000
Edit: added sample
October 7, 2014 at 7:06 am
Thank you Eirikur.... This has worked!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply