I am writing a Reporting Segment for an Ambulance Dispatch System. For one of the reports, the customer needs to know if the Patient was on board the ambulance within 30 minutes of the ambulance been assigned. I have struck an issue where duplicate rows are been displayed. This occurs when, for example, an ambulance is dispatched, but then the job is cancelled until a later time, then re-assigned. More info further down.
I have a MySQL query as below which isn't fully complete, but I am testing as I go:
SELECT J1.id, JL1.entry_time, JL1.resource_status, JL2.entry_time,
JL2.resource_status
FROM jobs J1
INNER JOIN job_logs JL2 ON J1.id = JL2.job_id AND JL2.resource_status = 14
INNER JOIN job_logs JL1 ON J1.id = JL1.job_id AND JL1.resource_status = 5
WHERE JL2.entry_time <= (JL1.entry_time + INTERVAL 30 MINUTE);
As stated above, job 434 was created, assigned at 19:01:42, cancelled and then re-assigned at 19:08:31. These are the results of the above query:
jobid, entry_time(JL1), resource_status(JL1), entry_time(JL2),
resource_status(JL2)
416, 2015-12-01 15:26:52, 5, 2015-12-01 15:32:25, 14
417, 2015-12-01 16:28:19, 5, 2015-12-01 16:35:07, 14
426, 2015-12-01 18:03:09, 5, 2015-12-01 18:04:17, 14
426, 2015-12-01 18:03:09, 5, 2015-12-01 18:04:17, 14
427, 2015-12-01 18:15:38, 5, 2015-12-01 18:15:51, 14
434, 2015-12-03 19:01:42, 5, 2015-12-03 19:01:49, 14
434, 2015-12-03 19:08:31, 5, 2015-12-03 19:01:49, 14
438, 2015-12-07 15:33:06, 5, 2015-12-07 15:35:48, 14
439, 2015-12-08 13:06:37, 5, 2015-12-08 13:06:48, 14`
Essentially, I need a way that i can run the query, but if multiple rows with the same job id are returned, only take the time from the latest one. If the times are the same, as with job 426, then return either one, but not both.
Please bear in mind that I'm a junior developer. I have tried using the likes of DISTINCT around J1.id, and adding AND JL2.entry_time <= JL2.entry_time at the end of the INNER JOIN lines to no avail. I have also spoken to a colleague who has a little more knowledge than myself with MySQL and they are also stumped. I'm not sure if i am miles off what i should be trying, but I cannot seem to find a reasonable resolution anywhere for this issue.
I have also considered performing multiple queries on my page, firstly to select the rows that match the resource status criteria of '5', then order by latest time first, take the first row and add the required data to an array in my PHP, then do the same for a resource_status of '14'. This will then provide me with an array full of single jobids, with 2 times, and 2 resource_status' inside each index. I would then have to run another query using the jobid's to gather the rest of the data i require for the report. Obviously it's not ideal to run 3 queries instead of a single query, I'm not entirely sure how big the impact is of running multiple queries rather than one, if any at all, i just know it's not the best way to do things. If it cannot be done in one query, that's fine. I can do multiple if i absolutely have to, but thought i would ask first to ensure.
In relation to multiple queries, It might be worth mentioning that some of the reports will be run against hundreds, if not thousands, of rows of data, dependant on the timeframe which the user specifies. Most of the time it is a month to month report, so will only report on jobs from November for example, (I plan on adding the code to do this to the query once i have this issue resolved), but in January, they will be performing a "yearly report" for the entire year.
Thanks in advance for any assistance, and please let me know if you need any more information.
