query join data - 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)

query join data

Author

Topic

iwahyudi
Starting Member

11 Posts

Posted - 2013-02-26 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-26 : 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

11 Posts

Posted - 2013-02-26 : 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

52326 Posts

Posted - 2013-02-26 : 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

11 Posts

Posted - 2013-02-27 : 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

52326 Posts

Posted - 2013-02-27 : 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

11 Posts

Posted - 2013-02-27 : 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

52326 Posts

Posted - 2013-02-27 : 01:41:14

sorry i cant understand your explanation

explain with some sample data and required output in below format for us to run and see for ourself

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

iwahyudi
Starting Member

11 Posts

Posted - 2013-02-27 : 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

52326 Posts

Posted - 2013-02-27 : 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

11 Posts

Posted - 2013-02-27 : 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

52326 Posts

Posted - 2013-02-27 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources