| Author |
 Topic  |
|
|
iwahyudi
Starting Member
Indonesia
11 Posts |
 Posted - 02/26/2013 : 06:16:08
|
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
always same ...
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
how ti join for result like up that?
Please Help
Rgds,
Indra Wahyudi |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/26/2013 : 08:29:58
|
You haven't used bucket column in outer query. Then what is the purpose of this CASE statement in Inner query?
(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
It is better to get quick response by posting Sample input data and also expected output? And what is the logic there in your output?
--
Chandu |
 |
|
|
iwahyudi
Starting Member
Indonesia
11 Posts |
Posted - 02/26/2013 : 08:57:36
|
hai Bandi thx u for response
i don't used coloum bucket because no necessary to that table...before that i used
if use join? how because result before add dpd>90 and after that
any suggestion?
Indra Wahyudi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/26/2013 : 23:41:03
|
quote:
Originally posted by iwahyudi
hai Bandi thx u for response
i don't used coloum bucket because no necessary to that table...before that i used
if use join? how because result before add dpd>90 and after that
any suggestion?
Indra Wahyudi
show us how you've added the where condition in query.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
iwahyudi
Starting Member
Indonesia
11 Posts |
Posted - 02/27/2013 : 00:30:16
|
Hai Visakh16 thx for response...
this condition i add this query:
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 )
where dpd > 90
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 3009385322 475 3009385322 475
1 DAAN MOGOT 3413288390 469 3413288390 469
1 JAKARTA 5648169324 814 5648169324 814
1 RAWAMANGUN 4184253210 539 4184253210 539
Result i'm want to for amount_npl and Acct is add dpd>90 , and value for rotal_ar and total acct value not add dpd>90
how friend? thx because i'm confuse to put join that...
rgds
Indra Wahyudi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/27/2013 : 00:33:36
|
you mean this?
select regional_id, cabang, sum(case when dpd > 90 then ots_principal else 0 end) 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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
iwahyudi
Starting Member
Indonesia
11 Posts |
Posted - 02/27/2013 : 01:34:17
|
Hai Visakh16 thx for response, did u answer like example this?
example table:
Tabel WOM_AGING.XAGING_MONTHLY:
Periode | Cabang_Id | order_No | Ots_Principal | DPD
2012 | 1 | 0270000 | 6700300000 | 2
2012 | 1 | 0303000 | 4500100000 | 10
Tabel WOM_AUDIT.SWI_CABANG_MAP_AUDIT:
Regional_id |Cabang_data_Audit
1 | Ciputat
2 | Bandung
Result:
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
And result for amount_NPL and ACCT after DPD > 90
and result for Total_AR and Total_ACCT not include DPD >90 or not using DPD >90 but both field amount_NPL and Total _AR same field is ots_Principal
And field for ACCT and Tot_ACCT same field from order_no
How Friend Visakh16?
Rgds,
Indra Wahyudi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
|
|
iwahyudi
Starting Member
Indonesia
11 Posts |
Posted - 02/27/2013 : 02:11:23
|
Hai visakh16 sorry bothering u again
example like this:
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
Rgds,
Indra Wahyudi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/27/2013 : 04:22:12
|
your output doesnt make any sense
how can you show data for two regionids in same row?
the first row of output shows
2 3000 2000 52000 30000
of which 3000 2000 belongs to regionid 2 and 52000 30000 to region id 1. how will you be able to relate them together?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
iwahyudi
Starting Member
Indonesia
11 Posts |
Posted - 02/27/2013 : 04:55:04
|
hai visakh16, thx u for response
Don't no , because my user want to like that and i'm confuse tooo...
but i try to use response u use sum(case when dpd...
rgds
Indra Wahyudi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/27/2013 : 05:19:58
|
there should be a field based on which you can relate them to bring all required data in the same row.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
query join data
