cursor compare - 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

General SQL Server Forums

New to SQL Server Programming

cursor compare

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-02-27 : 08:57:06

can i use position in cursor..

like

if cur1.(1) = cur1.(2)

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-27 : 09:07:05

what are you trying to achieve ?

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

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-02-27 : 09:09:59

compare next value with current value

challenge everything

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-27 : 09:14:34

khtan was really asking:

"What are you actually trying to do?"

As in -- "what did you start with (tables) / data? What do you need to end up with?"

Not "how are you doing it"

there will be a better way than using a cursor.

So if you describe what you actually *want* to end up with then we can help.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-27 : 09:18:23

thanks TC

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-27 : 09:21:22

quote:
Originally posted by pnpsql

compare next value with current value

challenge everything

typically, this is usually done using row_number().
something like


; with cte as
(
    select rn = row_number() over (partition by somekey order by ...), * 
    from   yourtable
)
select *
from   cte c1
       inner join cte c2 on  c1.somekey = c2.somekey 
                         and c1.rn      = c2.rn - 1
where  . . . .

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

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-02-27 : 22:31:48

simply, i have cursor exactly i am working on some accounting entries, and i need to check for
debit and credit match, there is one row with debit and second with credit amount. now i need to check that first and second will be same . please suggest..

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-27 : 22:40:04

How will you guaranteed that user will input the credit entry immediately after the debit entry ?

In accounting point, it is not necessary one debit entry to match with one credit entry. It might be one to many or many to many.

So if you are just checking for the amount in next immediate line is the same as the current line, than you will have problem.

To validate that the double entry transaction is balance (total debit = total credit), you check the entire transaction as a whole not line by line

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-27 : 22:42:43

something like this

to check for "mismatch" or not balance


select transaction_id
from   transacion_table
group by transaction_id
having sum(debit_amt) <> sum(credit_amt)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources