how do i grab the next date

[sbcsandhu]

ok, i am doing a report in sql sever and crystal reports

im writing my query in sql server.

I am grabbing completed appraisel activties, what i want to see is what activity did they go to after they completed appraisel. I have dates for all activites.
so if a person compeleted appraisel activity on 8/1/2007, i want to look for the next date that and return what activity it was.

im thinking some kind of function that would be like next or something? not sure.

 

[bborissov]

Can you post some example date and desired result from it?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sbcsandhu]

ok, this when the appraisal was completed

act_desc sts_dt act_sts
Appraisal 2007-08-21 Completed


a person can have multiple actitives, what i care is on or after 2007-08-21, what was the next act_desc

i just care about the very next, so if the next act_desc is on 2007-08-22, i only want that record and dont care about anything else

hope i explained it good

 

[bborissov]

So from your explanation I assumed that you want something like this:
[tt]
act_desc sts_dt act_sts
------------------------------
Appraisal 2007-08-21 Completed
BlahBlah 2007-08-22 no matter
[/tt]

and you want the result like this:
[tt]
act_desc sts_dt act_sts NextJobDt NextJobDesc
--------------------------------------------------
Appraisal 2007-08-21 Completed 2007-08-22 BlahBlahmatter
[/tt]

Right?
What you want if that person have MANY jobs on 2007-08-22?



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sbcsandhu]

oh that would be perfect,
if next date has multiple activites and i would like to list them all

so appraisal is completed on 8/21 and then on 8/22 he started 3 new activites on the same day, then i would like to list all 3

is that possible?
thank u

 

[djj55]

Do you have key field? Say job name?
djj

 

[sbcsandhu]

i have act_Id

 

[sbcsandhu]

and cwin, which is the person id

 

[sbcsandhu]

any ideas?