[SOLVED] Date-range based query problem

[AdamB]

Hello,

 

I'm having a problem with a MySQL query I'm trying to write. I know logically what I'm trying to achieve, but I can't get the results I need from the database.

 

I've got a list of cases in one table, and a list of caseworkers in another table. The caseworkers are assigned to each case using their unique ID. The cases table also records the date (Y-m-d) the caseworker was assigned to the case. I need to select all of the caseworkers who have not taken a case in the last 90 days. Caseworkers can have more than one case assigned to them at a time.

 

I want to be able to iterate the results through a PHP while() loop to output all of the caseworkers, and the date they last took a case if it's over the 90 days, but it's the query bit thats got me stuck..

 

The table structures look like:

 

TBLCASES

- case_id

- caseworker_id

- caseworker_allocated

etc. etc.

 

TBLCASEWORKERS

- caseworker_id

- caseworker_name

- caseworker_address_line_1

etc etc.

 

If I can provide any more information to help, a prod in the right direction would be really appreciated!!

[aschk]

SQL for all cases taken in the last 90 days:

SELECT case_id
      ,caseworker_id
      ,caseworker_allocated as 'date_allocated'
FROM TBLCASES c
WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY)

 

SQL for all caseworker ids for cases in the last 90 days:

SELECT caseworker_id
FROM TBLCASES c
WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY)
GROUP BY caseworker_id

 

SQL for all caseworker ids for cases NOT in the last 90 days:

SELECT caseworker_id
FROM TBLCASES c
LEFT JOIN
(SELECT caseworker_id
FROM TBLCASES
WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY)
GROUP BY caseworker_id) deriv1 ON c.caseworker_id = deriv1.caseworker_id
WHERE dervi1.caseworker_id IS NULL

 

 

SQL for all caseworker ids for cases NOT in the last 90 days + last worked case date:

SELECT caseworker_id
      ,MAX(caseworker_allocated) as 'last_worked'
FROM TBLCASES c
LEFT JOIN
(SELECT caseworker_id
FROM TBLCASES
WHERE caseworker_allocated > (NOW() - INTERVAL 90 DAY)
GROUP BY caseworker_id) deriv1 ON c.caseworker_id = deriv1.caseworker_id
WHERE dervi1.caseworker_id IS NULL
GROUP BY caseworker_id

 

 

n.b. I have not tested the above (because i'm too lazy to create a test db to do it) but with any luck it should provide you with what you need + the steps i took to get to the final solution.

 

[AdamB]

That worked straight away (changed a spelling), thanks very much!

 

I was getting confused using GROUP BY() and MAX() and didn't think of using multiple GROUP BY() and nested SELECT() statements... The only bit I'm not sure about is:

 

WHERE deriv1.caseworker_id IS NULL

 

Purely for my knowledge more than anything, what does that bit actually mean and why does the statement need it?

[aschk]

Ah right, i'll explain.

When using a LEFT JOIN for each row in the primary table you'll join a row in the secondary table EVEN IF NO MATCHING RECORD EXISTS. If no record exists you'll just get all the columns with NULL in them.

 

for example, create 2 tables:

 

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL
);
CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL
);

 

Insert some random data (so you have mismatching rows):

INSERT INTO t1(id) VALUES(1),(2),(3),(4),(5);
INSERT INTO t2(id) VALUES(1),(3),(5);

 

As you can see from the above there are 3 matching records in the 2 tables.

So now perform the statement:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;

 

You'll see that for each row in t1 you get a corresponding row in t2, UNLESS there is no row, in case all the t2 columns will just have NULL in them.

 

Therefore going back to the original statement, by doing "WHERE deriv1.caseworker_id IS NULL" grabs all the rows from the JOIN'ed tables that have NULL values from the JOIN. i.e. giving you the ones that DON'T have a corresponding record.

 

Hope that helps clear things up a little.

[AdamB]

It did, thank you! Can I mark this question as solved or will one of the mods?

[aschk]

Feel free to mark it yourself if you can.