Hello to anybody who reads this - I am struggling with a query I wondered if I could get any pointers, this is the gist of it....
'Table 1' is a log of calls made to an office. Each call has a jpb reference (basically the ID from this table.
'Table 2' is where the calls should go onto after an engineer has attended them, and will use the job reference from the first table (this relies on the engineer completing the job on a PDA which is not always the case hence Table 3...)
'Table 3' is a table of every job that has been attended. This will always have the Clients details but will not always have the Job Reference.
What I am trying to do is get a list of jobs that have not been attended...
I have 'clientID' and 'jobID' from Table 1, the easiest way of doing it is a simple Join and check for the 'jobID' on Table 2, but unfortunately not all of the jobs end up on Table 2, so I need to cross reference with Table 3 to check no visits have been done for that client since the job was raised on Table 1. I got as far as this....
SELECT * FROM `loggedCalls` l JOIN
`allCalls` a
ON (l.clientID = a.clientID)
AND (l.loggedDate <= '15-11-29' AND l.priority IN('3','4','5'))
AND l.jobID NOT IN ( SELECT `jobID` FROM `pdaJobs` )
What I am currently struggling with is the next part, which I basically want to say..
AND MAX visit date for this Client ID on the 'allCalls' table is Less than the date this call was made on 'loggedCalls'.
Whatever I try for this part, always leads to no results, even when i know there should be a result.
An example of the result I am looking for..
Job 1 - Logged on 25th November for Client '10432'. Has no visit Logged on PDAjobs and no visit on the all Calls table for the 25th onwards for client 10432 - so that job i would like to get a result for that.
Job 2 - Logged on 25th November for Client '10433'. Has no visit Logged on PDAjobs but Client 10433 does have a visit on the All Calls table for 27th November, so i dont want to see that in the results because we have been to this client after the call was raised.
I hope this makes sense! Im sorry to come here cap in hand, but Ive been going round in circles with this for days, even if somebody tells me its not actually possible then it will come as a relief.
Thanks.