Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How use left join

Answered How use left join

  • Tuesday, February 26, 2013 3:05 PM
     
     
    Sign In to Vote
    0
    Hai friend, Iam having problem about join data
    if query like this :
    select regional_id, cabang, sum(ots_principal) as amuont_NPL, count(order_no) as acct, sum(ots_principal) as tot_ar , count(order_no) as tot_acct 
    from

    (Select b.regional_id, b.cabang_data_audit as cabang, A.OTS_PRINCIPAL, A.DPD, a.order_no, 
    (case 
    when a.dpd = 0 then '01 DPD 0'
    when a.dpd <= 30 then '02 DPD 01-30'
    when a.dpd <= 60 then '03 DPD 31-60'
    when a.dpd <= 90 then '04 DPD 61-90'
    when a.dpd <= 120 then '05 DPD 91-120'
    when a.dpd <= 150 then '06 DPD 121-150'
    when a.dpd <= 180 then '07 DPD 151-180'
    when a.dpd > 180 then '08 DPD 180 up'
    else '09 tdk ada data daily aging'
    end )bucket
    from WOM_AGING.XAGING_MONTHLY a inner join WOM_AUDIT.SWI_CABANG_MAP_AUDIT b
    on a.cab_id =b.cab_id and a.periode= 201212 )

    group by regional_id, cabang
    order by regional_id, cabang

    Result like this :
    Regional_id Cabang   Amount_NPL    ACCT   Total_AR       Total_ACCT
    1              CIPUTAT 179251040032 25500 179251040032 25500
    1              DAAN MOGOT 113962714200 15636 113962714200 15636
    1              JAKARTA        241523040525 32934 241523040525 32934
    1              RAWAMANGUN 189211704206 25210 189211704206 25210


    If I'm add where Dpd >90 result like this
    Regional_id Cabang Amount_NPL ACCT Total_AR Total_ACCT

    1              CIPUTAT          3009385322 475 3009385322 475
    1              DAAN MOGOT   3413288390 469 3413288390 469
    1              JAKARTA         5648169324 814 5648169324 814
    1              RAWAMANGUN 4184253210 539 4184253210 539



    My result should be like this:
    Regional_id Cabang Amount_NPL ACCT Total_AR Total_ACCT
    1 JAKARTA 5648169324 814 241523040525 32934
    1 RAWAMANGUN 4184253210 539 189211704206 25210
    1 DAAN MOGOT 3413288390 469 113962714200 15636
    1 CIPUTAT 3009385322 475 179251040032 25500

    result : amount_npl and acct ( add dpd>90) total _ar total_acct (no add dpd>90)
    how to join for result like up that?
    Please Help me

    Rgds,
     

All Replies

  • Wednesday, February 27, 2013 7:23 AM
     
     
    Sign In to Vote
    0

    Hai Friend help me..

    Sample data

    Please help

    Rgds,

    Indra

    Example data
    Tabel 1
    Periode Branch_id Ots_principal DPD order_no
    2012 1 50000 10 1000
    2012 1 2000 30 2000
    2012 2 3000 80 2000
    2012 3 5000 20 1000
    Tabel 2
    Regional_id
    1
    2
    3
    result 
    regional_id   amount acct
    2   3000 2000 ←if add where dpd >20
    1   2000 2000
    regional_id   Total Tot_acct
    1   52000 30000 ←if no add where dpd>20
    2   3000 20000
    3   5000 10000
    Same field from ots_principal  for amount and total
    same field from order_no for acct and tot_acct
    I want to join result like this
    regional_id   amount acct Total Tot_acct
    2   3000 2000 52000 30000
    1   2000 2000 3000 20000
    3       5000 10000

     
  • Wednesday, February 27, 2013 7:45 AM
     
     
    Sign In to Vote
    0
    hey its not clear like which are the tables for consideration.

    Please have look on the comment

     
  • Wednesday, February 27, 2013 8:41 AM
     
     
    Sign In to Vote
    0

    Hi,

    Please provide table schema so that we can provide you with the best result.

    Thanks

     
  • Wednesday, February 27, 2013 10:19 AM
     
     Answered Has Code
    Sign In to Vote
    0

    something like this ?

     create table t1
(
Periode	int ,
Branch_id	int,
Ots_principal	int,
DPD	int ,
order_no int)

insert into t1 
select 2012,1,50000,10,1000
union all select 2012,	1	,2000,	30,	2000
union all select 2012	,2	,3000	,80	,2000
union all select 2012,	3,	5000,	20,	1000

create table t2 (Regional_id int)
insert into t2 values (1),(2),(3)

     select t2.Regional_id,SUM(t1.Ots_principal) from t2 
left join t1 
on t1.Branch_id= t2.Regional_id
group by t2.Regional_id
 
 select t2.Regional_id,SUM(t1.Ots_principal) from t2 
left join t1 
on t1.Branch_id= t2.Regional_id
where dpd>20
group by t2.Regional_id


    Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

     
  • Wednesday, February 27, 2013 10:20 AM
     
     Answered
    Sign In to Vote
    0

    Hi Dude,

    Here is the below Query which you can use to get your required result.

    ;WITH result1CTE(

    Select Branch_id ,sum(Ots_principal) AS Amount,sum(order_no) as Acct from table1 where dpd >20

    group by Branch_ID

    )

    ,result2CTE

    (

    Select Branch_id ,sum(Ots_principal) AS Total,sum(order_no) as Total_Acct from table1

    )

    SELECT B.Branch_ID as regional_id, A.Amount , A.Acct, B.Total, B.Total_Acct from result2CTE b left join result1CTE a on b.Branch_ID = a.Branch_ID

    Regards

    Naveen