0

Below tables :I am trying to calculate
openLeadsCount having status 1

I tried but getting wrong results

SELECT COUNT(lead.id) FROM lead JOIN lead_status ls WHERE ls.lead_id=lead.id 
AND ls.id=(SELECT MAX(id) FROM lead_status lst WHERE lst.status=1 )




Lead table:
    id created_on
    1    2014-1-13 
    2    2014-1-18

lead_status table
    id lead_id   created_on       lead_status
    1    1       2014-01-13:05:00      1       
    2    1       2014-01-14:06:00      2
    3    1       2014-01-14:06:00      1
    4    2       2014-01-14:06:00      1
2
Contributors
1
Reply
16
Views
3 Years
Discussion Span
Last Post by Reverend Jim
0

Try

SELECT COUNT(lead.id) 
  FROM lead JOIN lead_status ls 
    ON ls.lead_id=lead.id 
   AND ls.id=(SELECT MAX(id) FROM lead_status WHERE lead_status=1)

Note the replacement of WHERE with ON and the correction of the field name in lst.status=1 to lead_status=1.

This is a valid query, however, I am not sure this is the query you want because I don't know what constitutes an openLead and you didn't give an example of the recordset you wanted returned.

Edited by Reverend Jim

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.