Hi.
Issue centers around trying to establish one single "Closed date" for a Case.
There exists no official closed date field in the DB. The closed date is this case is actually the last post attached to the CaseNo.
Also because a case can only have one status at a time it is hard to create a condition to extract this field.
i.e when Status 'Closed' as in this case you recieve two fields.
Furthermore this table exists out of the original Case table where the startdate is found in another table called "History". Hence we must join both tables by means of Case_ID.
| Case_ID | Updated | Status |
|---|
| 171 | 04-10-15 07:56:05 | Closed |
| 171 | 04-10-15 07:57:14 | Closed |
| 176 | 04-10-15 10:48:14 | Closed |
| 176 | 04-10-15 10:50:17 | Closed |
| 178 | 12-07-12 10:00:00 | Registered |
The mission with this is to extract the last post of every timestamp related to a Case_No in order to have an actual Close date, ie. 04-10-15 10:50:17 for case 176 and where the status is not = Closed then there should exist no close_date.
The DB model connecting the fields is attached.
Any Tips on how we can 1. Extract the last post of the entry in Updated when status = 'closed', and how to ignore (give no value) to cases of other status types, ie Registered.
