At 02:50 PM 12/5/2004, Namit Nathwani wrote:

>but the result I want is the following
>151 1 01/04/2004 40.00 0.00
>165 2 05/04/2004 0.00 0.00
>178 3 10/04/2004 250.00 0.00
>
>151 has 100.00 Dr - 60.00 Cr (from 165) thus 40.00 Dr
>165 has Nil as all of it's amount (60.00 Cr.) has been used against 151
>178 shows the original amount as is, as no adjustments have been performed.

And if 165 had been 160.00, would the desired
result have been

>151 1 01/04/2004 0.00 0.00
>165 2 05/04/2004 0.00 60.00
>178 3 10/04/2004 250.00 0.00

or

>151 1 01/04/2004 0.00 0.00
>165 2 05/04/2004 0.00 0.00
>178 3 10/04/2004 190.00 0.00

What I'm trying to say is that creating a single SQL
statement to handle logic as complex as this may be
possible but it's not desireable. Use a stored procedure.

Regards,


Ann