Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2012
    Posts
    19

    Unanswered: Nil activity query - no activity in last 10 days

    I have a database set up as part of my customer relationship management system.

    Customers have many call notes attached to them.

    What I am trying to do is develop a query to pull Distinct accounts with no calls notes attached with a date in the last 10 days.

    The current query I am using is as follows

    Code:
    select distinct account.name, meetings.status, meetings.date_held 
    from account, meetings, meetings_account
    where account.id = meetings_account.account_id 
    and meetings_account.meeting_id = meetings.id
    and meetings.status = 'Held'
    and meetings.date_held NOT BETWEEN DATE_ADD(CURDATE(), INTERVAL -40 DAY) AND CURDATE() ORDER BY `account`.`name` ASC
    However this just brings a list of all call notes attached to the account not in the last 10 days regardless of whether they have actually had a call in the last 10 days.

    Basically my staff will be using this list as a list of the most important clients to call - ie clients who have been ignore for over 10 days.

    I Hope that makes sense. The only way i can think of it working is to run a query to find the call note with the most recent date held, and if it is greater than 10days then display the record.

    Can any body help with this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT account.name
      FROM ( SELECT meetings_account.account_id
               FROM meetings_account
             LEFT OUTER
               JOIN meetings
                 ON meetings.id = meetings_account.meeting_id
                AND meetings.status = 'Held'
                AND meetings.date_held BETWEEN CURRENT_DATE - INTERVAL 40 DAY
                                           AND CURRENT_DATE
              WHERE meetings.id IS NULL
           ) AS none
    INNER
      JOIN account
        ON account.id = none.account_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    19
    Hi r937,

    Thanks heaps for the response. I ran the query and it generated a list of accounts. I then took note of one account, went in and held a meeting the previous day.

    When I then ran the query again, this account was still listed in the list. I was hoping the account would not be listed if I have held a meeting in the last 40days.

    Is this a function of the code, or without knowing the full structure of tables you couldn't really debug as the code should work?

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my bad

    try this --
    Code:
    SELECT account.name
      FROM account
    LEFT OUTER
      JOIN ( SELECT meetings_account.account_id
               FROM meetings_account
             INNER
               JOIN meetings
                 ON meetings.id = meetings_account.meeting_id
                AND meetings.status = 'Held'
                AND meetings.date_held BETWEEN CURRENT_DATE - INTERVAL 40 DAY
                                           AND CURRENT_DATE
           ) AS none
        ON none.account_id = account.id 
     WHERE none.account_id IS NULL
    Last edited by r937; 06-25-12 at 22:10.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2012
    Posts
    19
    r937,

    That works perfectly thank you very much! One thing I forgot to add, and I am unsure of how to add to this code is there are 3 types of accounts Prospect, Cold, Customer. I only want it to look through the Prospect and cold accounts so and also have this displayed with the account name so

    Code:
    SELECT account.name, account.category
    FROM account
    WHERE account.category = 'Customer'
    OR account.category = 'Prospect'
    I tried to play around a little to slot it into your code but couldn't quite get it.

    Any help would be greatly appreciated! Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT account.name
      FROM account
    LEFT OUTER
      JOIN ( SELECT meetings_account.account_id
               FROM meetings_account
             INNER
               JOIN meetings
                 ON meetings.id = meetings_account.meeting_id
                AND meetings.status = 'Held'
                AND meetings.date_held BETWEEN CURRENT_DATE - INTERVAL 40 DAY
                                           AND CURRENT_DATE
           ) AS none
        ON none.account_id = account.id 
     WHERE account.category IN ('Customer','Prospect')
       AND none.account_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2012
    Posts
    19
    Thank you so much! I have learnt so much and can now apply this to other queries.

    Just out of interest if you wanted to add a field in the display from another table where would that go. IE

    SELECT account.name, meetings.date_held
    FROM account, meetings

    It cant just go at the start can it as it will interrupt the LEFT OUTER JOIN code?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it isn't as simple as that

    first of all, you will want to use INNER JOIN syntax

    but second, how many meetings do you want to show? obviously, it won't have any with 'Held' status in the last 40 days, right? so which meetings do you want to show?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2012
    Posts
    19
    Oh ok, well one that I can think I will want to develop in the future would be along the same vein as the one you just helped me with, but I would want to display all meetings, where meeting.status = 'planned' in the next 40 days. But in the output I would need account.name, account.category from account and meetings.date_held from meetings.

    The introduction of the meetings.date_held from meetings is the thing I struggle with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •