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
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