Peculiar T-SQL requirement. - 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)

Peculiar T-SQL requirement.

Author

Topic

Hariarul
Posting Yak Master

160 Posts

Posted - 2007-03-14 : 05:51:34

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet
------------------------------
A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet
------------------------------
A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

Hariarul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:15:17

khtan, do you remember the link to my algorithm?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 06:16:08

quote:
Originally posted by Peso

khtan, do you remember the link to my algorithm?

Peter Larsson
Helsingborg, Sweden

why me ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 06:17:40

found it http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:18:06

Because you're the best!

Not this one...
The other one with counters for each and one facevalue?
Slimmed down the iterations from 2^45 down to 50,000 something.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 06:19:28

oh yeah. You make some improvement over the other thread. I try . .

this ?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610
[EDIT] Oops wrong one

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:21:03

This one!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:22:38

This new one also calculates BestUnder and BestOver sums (and choose the nearest), if an exact sum is not available!

Peter Larsson
Helsingborg, Sweden

Hariarul
Posting Yak Master

160 Posts

Posted - 2007-03-14 : 06:59:17

Thanks a lot Peso & Khtan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources