>>.Selecting the most recent record anyone ? ? ?

[webmaster1]

Hi All,

 

I have a CRM that amends records with contact details in a primary table (masterdata) and logs the call results of outbound calls made for each of these records in a secondary table (resultsnotations).

 

When the call result for a record is equal to "Schedule Call-Back" the end user is asked to enter a schedule date. This a means of reminding the end user of outbound call on the day in question (i.e. a To-Do List Reminder).

 

I have a page named let's say schedule.php. I want this page to loop the output (the looping isn't a problem) for each record where its most recent call result (resultsnotations.callresult1) is equal to "Schedule Call-Back" and the schedule date (resultsnotations.scheduledate) is less than or equal to (>=) today's date.

 

I'm having difficulty defining 'today' in my mySQL query. I mistakenly thought I had the correct query below : :

 

SELECT 
masterdata.masterdataid, 
masterdata.companyname, 
masterdata.contactname, 
IFNULL( resultsnotations.prospectrating, 'Not Rated' ) AS prospectrating, 
max( resultsnotations.lastactivity ) AS lastactivity, 
resultsnotations.scheduledate
FROM masterdata
JOIN resultsnotations ON masterdata.masterdataid = resultsnotations.masterdataid
WHERE resultsnotations.callresult1 = 'Schedule Call-Back'
AND masterdata.stream='stream4'
GROUP BY masterdata.masterdataid
ORDER BY resultsnotations.scheduledate

 

After hastily running this live it was pointed out to me that this was not querying the most recent date. Any help would be greatly appreciated.

 

I've tried illustrating my issue a little simpler below:

 

 

 

[kickstart]

Hi

 

A quick play.

 

Think I would have join on a SELECT which brings back the latest activity for each masterdataid.

 

Something like this (not tested)

 

SELECT 
masterdata.masterdataid, 
masterdata.companyname, 
masterdata.contactname, 
IFNULL( resultsnotations.prospectrating, 'Not Rated' ) AS prospectrating, 
resultsnotations.scheduledate
FROM masterdata
JOIN ( SELECT masterdataid, max(lastactivity) AS Verylastactivity
FROM resultsnotations
GROUP BY masterdataid) LatestMasterData 
ON LatestMasterData.masterdataid = masterdata.masterdataid
JOIN resultsnotations 
ON LatestMasterData.masterdataid = resultsnotations.masterdataid 
AND LatestMasterData.Verylastactivity = resultsnotations.lastactivity
WHERE resultsnotations.callresult1 = 'Schedule Call-Back'
AND masterdata.stream='stream4'
GROUP BY masterdata.masterdataid
ORDER BY resultsnotations.scheduledate

 

All the best

 

Keith

[fenway]

There was an entire thread on this issue just yesterday....

[webmaster1]

Sorry about that Fenway. I'll use the search function going forward.

 

@kickstart: Thank you so much for the query. I've ran it a few times and can't find any fault with it.

 

I don't understand how the consecutive joins work (I didn't even know this could be done). I'm also unfamiliar (not that I'm familiar with much at all tbh) with the use of the nested query in the first join.

 

The issue is solved but I'll leave this open for a few days to see if anyone can shine a little more light on the two points I've just outlined.

 

Thanks again kickstart!