Combine tables. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combine tables.

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 10:33:23

Hi all,

Good people of sqlteam, I need your help..

I have 2 tables

Table A
|code|posteddate|amount

Table B
|code|posteddate|amount

Table A has 10000 records
Table B has 4 recortds

I want a table C to give me
|code|posteddate|A_amount|B_amount|diff
XX 122333 12 null 12
yy 12445 10 5 15

Please note the second row on Table C..

This is my try, i think am getting a Cartesian product somewhere...

BEGIN TRAN;

with s as
(
select
isnull(x.dw_sbu_key, y.dw_sbu_key) as dw_sbu_key
,isnull(x.dw_period_key, y.dw_period_key) as dw_period_key
,isnull(x.amount,0) actual_1
,isnull(y.amount,0) actual_2

from
(
select
b.dw_sbu_key
,left(convert(varchar,a.tran_date,112),6) dw_period_key
,isnull(case when a.part_tran_type = N'D' then
a.tran_amt_lcy*-1
else
a.tran_amt_lcy
end,0
) amount

from
stg.dbo.src_dailycommsfee a
left join
edw.dbo.dim_account b on (a.account_number = b.account_number)
) x
full outer join
(
select
b.dw_sbu_key
,left(convert(varchar,a.Posted_Date,112),6) dw_period_key
,isnull(a.amount,0) amount

from dbo.Income_Vols_Final a
left join dbo.DWV_SBU_MODIFIED b on (a.Sbu_key = b.SBU_CODE)
where
Income_Type = N'LC'
) y
on x.dw_sbu_key = y.dw_sbu_key and x.dw_period_key = y.dw_period_key
)
select
s.dw_sbu_key
,s.dw_period_key
,s.actual_1 aa
,s.actual_2 bb
,diff = s.actual_1 - s.actual_2
from s

ROLLBACK

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:48:23

[code]SELECT code,posteddate,A_Amount,B_Amount,
ABS(COALESCE(A_Amount,0)-COALESCE(B_Amount,0)) AS diff
FROM
(
SELECT code,
MAX(posteddate) AS posteddate,
MAX(CASE WHEN Tab='A' THEN amount ELSE NULL END ) AS A_Amount,
MAX(CASE WHEN Tab='A' THEN amount ELSE NULL END ) AS B_Amount
FROM
(
SELECT code,posteddate,amount,'A' as Tab
FROM TableA
UNION ALL
SELECT code,posteddate,amount,'B'
FROM TableB
)t
GROUP BY code
)r[/code]

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-08-27 : 11:12:28

Visakh16

Thanks folk

I saw Jeff's post ( http://weblogs.sqlteam.com/jeffs/archive/2006/06/19/10270.aspx)..

The 2 are appealing, Thanks folks :p

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources