How many times in the last year ...

[sKunKbad]

I need help with a query.

 

I've got a table that stores references to calls that come in to our office. It has a datetime field, and for the purposes of the query, it's the only thing in this table that is important.

 

I've got another table that stores appointment information. The appointment start time is also a datetime field. For this query, this start time is the only thing in this table that is important.

 

I need to know how many times in the last year there was a day when we had at least 150 calls come in and made at least 1 appointment.

SELECT COUNT(*)
FROM appointments a
LEFT JOIN (
  SELECT DATE(time)
  FROM calls
  WHERE <there are at least 150 calls on a day>
) c 
ON DATE(a.start_time) = c.time
WHERE a.start_time >= CURDATE() - INTERVAL 1 YEAR 

To make things difficult, I guess the time would have to be converted to a day, because I'm looking for all days where there was an appointment, and the datetime field is too specific.

 

I've been looking online for 30 mins or so, and was hoping somebody here could point me in the right direction. I'm not making much progress by myself.

Edited October 6, 2014 by sKunKbad

[Barand]

An inner join to the appointments will select only  dates where an appointment exists and is faster than left join

SELECT DATE(start_time), totcalls
FROM appointments a
    INNER JOIN
        ( SELECT 
            DATE(time) as call_day
            , COUNT(*) as totcalls
            FROM calls
            GROUP BY call_day
            HAVING totcalls >= 150
        ) as tot ON tot.call_day = DATE(a.start_time)
GROUP BY DATE(start_time)

Edited October 6, 2014 by Barand

[sKunKbad]

Barand, It turns out that I actually needed the query to be slightly different, but with your help I was able to do what I needed to do. THANKS!

[sKunKbad]

For the benefit of all (as well as my future reference), I wanted to show the query that I ended up using:

SELECT COUNT(DISTINCT(DATE(TIME))) AS COUNT
FROM appointments a
    INNER JOIN
        ( SELECT 
            DATE(TIME) AS call_day
            , COUNT(*) AS totcalls
            FROM calls 
            WHERE user_id = ? 
            GROUP BY call_day
            HAVING totcalls >= 150
        ) AS tot ON tot.call_day = DATE(a.time)
WHERE a.user_id = ?

Again, this is just counting the number of days in the last year where there were more than 150 calls and at least one appointment was made.

 

I guess what I didn't know, which made the query work, was that a selected field alias could be used as a condition, and also that the ON part of the join could use the alias as well. My logic on how to retrieve the count was flawed too. I guess I need more experience. In the past I would have just done simple queries and processed everything in PHP, but I've been trying to make MySQL do as much work as possible (aka doing things the right way), hence the stupid questions in the last few weeks/months.

 

Thanks again Barand!

[Barand]

 

... was that a selected field alias could be used as a condition

 

FYI, You can use an alias in the HAVING clause, as the data has been retrieved by then and the alias allocated to the field, but you cannot use an alias in a WHERE clause condition

[sKunKbad]

Barand, I have a question about the subquery. In another instance I need a count of the rows that the subquery returns. Is this my best option:

SELECT
  COUNT(*) AS count
FROM
  (SELECT
    DATE(time) AS call_day,
    COUNT(*) AS totcalls
  FROM
    calls
  WHERE user_id = 256457173
    AND TIME >= CURDATE() - INTERVAL 1 YEAR
  GROUP BY call_day
  HAVING totcalls >= 100) AS whatever

It works, but I figured you would have a better way, yes?

[Barand]

Sorry to disappoint you but nothing better springs to mind