Pulling dates from a subreport

[n2nuk]

I have already asked this question in a roundabout way in another post I made earlier in the week, since then I have found issues with the data. So I'll start again....

Basically need to produce a report that returns a clients current and most previous worker.
There is a client table that holds all the client information and a caseload_audit table that records a history of all the workers that have been allocated to the client in the past. A client can only one worker allocated to them at any one time.

The caseload_audit table records the worker_id, start_date and end_date.

The end_date for the previous worker is populated with the same date and time when the new worker is allocated. However in some cases there is a time difference (in seconds) between the new and the old worker.

To get the current worker, I have a select statement on my main report that says where the end_date is null.

To get the previous worker is proving tricky. I have created a subreport and linked the reports by the client id and where main report start_date = sub report end_date.
Although this seems to work I have noticed a number of discrepancies and am a bit stick on how to go about resolving them.

There seems to have been issues with the data, in some instances the caseload_audit table has multiple columns where the end_date is null.
To get around this I created a formula that did a max on the start date

@maxstartdate
maximum ({caseload_audit.start_date}, {caseload_audit.client_id})
I have moved all my columns from the detail section to the group column, although this displays the correct date, it is not returning the correct worker.

Due to time difference between the start_date and end_date for the previous worker, the report does not return the previous worker for these clients.

Any ideas how I can overcome these two issues would be much appreciated.

Thanks in advance

 

[lbass]

I think you could just sort the subreport in descending order by datetime, and then place the desired fields in the group header for the client group. Then link the sub to the main report and suppress all sections expect the group header.

-LB

 

[n2nuk]

There are two issues with the data.

on the main report I want to report the current worker, to do this I have added a filter where the end_date is null. However I found that there was more that record with a null record.
To get around this I created a formula to get the max_start_date, grouped by the client_id. I then moved my columns to the group section. This works OK but I am not always getting the worker associated with the start_date.

The other issue is that to get the previous worker I added a sub report link where the date started is the same as the end_date. Again this works OK but there are instances where there is a small time difference in the minute part of the date that is returning no rows for the previous workers for some clients.

 

[lbass]

As I said, do not link on date, but instead sort on it in the subreport in descending order.

-LB