Query question

[dspangenberg]

Hi all,

I'm having difficulties getting this query together.

I have a table cashflow containing several fields: ProjectID, Year, Cash_in Cash_out

Now if i have three records from One project eg:
projectID Year Cash_in CashOut
1 2000 200 300
1 2001 400 200
1 2002 400 400
2 2001 2000 3000
.. ...... ...... .......


Goal is to derive a Payback field that gives me the year in which the project was payedback. So in the case of ProjectID 1 it would be year 2001
payback field = sum (cash_in) >= sum(cash_out)

but if i use the sum function it will calculate every record of project_ID 1
it has to stop where the criteria is met.

any ideas?

any help is appreciated.

Sincerely Yours,

D. Spangenberg

 

[Ziggy]

Hi D,

Can you give some more details, or rephrase. I'm not clear on the project id and how you determined 2001 was paid back?

Ziggy

 

[dspangenberg]

Hi Ziggy,

projectID Year Cash_in CashOut 
1          2000  200      300 
1          2001  400      200 
1          2002  400      400 
2          2001  2000    3000

So for projectID 1, the year in which it was paid back is 2001 cause:
year 2000 --> cash_in >= Cash_out = false

year 2000  --> cash_in >= Cash_out  AND
year 2001  --> cash_in >= Cash_out = true

example in numbers would be 
year  cash_in  cash_out
2000  200       300
2001  400       200

sum   600       500

so 2001 would be the first year in which the project was paid back.

Hopefully you understand what i'm trying to achieve here.


Greetings D.