returning current and previous activities

[n2nuk]

I am searching through a caseload history table that shows all the workers that have been allocated to a particular client.

the table lists the date the worker was allocated to the client and the date the worker left the clients caseload.

the current worker is identifed as they do not have an end date.

the previous record(s) all display the worker that was assigned to the case and the date they left.

The date left for the previous worker always seems to be the same as the date joined for the next worker who superseded the previous worker.

All dates are in dd/mm/yyyy hh:mm:ss.

Any ideas how I can filter the report so that it only returns the current worker and the worker previous to the current one?

Thanks in advance.

 

[IanWaterman]

You can not really filter the data, but you can suppress the Previous workers

Sort workers by Start date descending. This will list latest at top.

Create at Running Total which does distinct count of workers.

In details section expert go to the suppress formula and enter

{#YourWorkerRT} >2

All other workesr will be suppressed, be careful if you want to do sums or counts, the other worker records are there just suppressed.

You will need to use RTs with a reverse condition ({#YourWorkerRT} <=2)

Ian

 

[n2nuk]

Thanks for you help, and your suggestion works great, but...

I would like to report the current and previous workers on the same row.

I attached the caseload history table to return the current worker, all I had to do was to say where the end date was null.
To return the previous worker I added a subreport and linked it to my main report using two links, by the client id and secondly where the current workers start date is the same as the end date from the previous row.
This works in most cases as the end date of the previous worker and start date for the current worker are usually the same. However I have come across records where the start date is out by seconds. date format is in dd/mm/yyyy hh:mm:ss
the other issue is that although I get the correct start date for the current worker the worker is different as it seems to return one of the previous workers.

Many thanks