Urgent help -loop logic.. - 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)

Urgent help -loop logic..

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-07 : 04:04:43

Hi

I am struggling to flag matching debits to credits. A debit could potentially match multiple credits (based on specific fields matching), but if a debit matches a credit based on amount and Acc, then that credit cannot match another debit.

i.e.
Debits:
Debit_SRN Debit Acc
1000 500 2
1001 500 2
1002 500 2

Crebit_SRN Crebit Acc
2000 500 2
2001 500 2
2002 500 2

Now, I don't particular which exact Credit that the Debit matches to, but a Credit_ID cannot be repeated for the same Debit ID; therefore it should look something like this:

Debit_SRN Debit Acc Match to Credit_SRN
1000 500 2 2002
1001 500 2 2000
1002 500 2 2001

Does this need to be in a loop? as I have 4 million trans to run this over...

Please can you help.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 04:09:55

[code]
select *
from
(
select Debit_SRN, Debit, Acc, row_no = row_number() over (partition by Acc, Debit order by Debit_SRN)
from Debits
) d
inner join
(
select Credit_SRN, Credit, Acc, row_no = row_number() over (partition by Acc, Credit order by Credit_SRN)
from Credits
) c on d.Acc = c.Acc and d.Debit = c.Credit and d.row_no = c.row_no
[/code]

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-07 : 10:32:35

TRY this too..

create table #sample (Xvalue int,YValue int, ZValue int,progran# int)

insert into #sample

select 300, 400, 500, 1 union all
select 300, 600, 200, 2union all
select 400, 400, 100, 3union all
select 100 ,200 ,500, 4union all
select 200 ,600, 200, 5

select * from #sample

select Xvalue,YValue,ZValue
from(
select row_number() over( order by Xvalue)as r_no,Xvalue from #sample group by Xvalue )x full outer join
(select row_number() over( order by YValue)as r_no,YValue from #sample group by YValue)y on x.r_no =y.r_no full outer join
(select

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 01:51:30

quote:
Originally posted by senthil_nagore

TRY this too..

create table #sample (Xvalue int,YValue int, ZValue int,progran# int)

insert into #sample

select 300, 400, 500, 1 union all
select 300, 600, 200, 2union all
select 400, 400, 100, 3union all
select 100 ,200 ,500, 4union all
select 200 ,600, 200, 5

select * from #sample

select Xvalue,YValue,ZValue
from(
select row_number() over( order by Xvalue)as r_no,Xvalue from #sample group by Xvalue )x full outer join
(select row_number() over( order by YValue)as r_no,YValue from #sample group by YValue)y on x.r_no =y.r_no full outer join
(select

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

The code is incomplete

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources