Advanced MYSQL Query

[bdmovies]

I am needing to run a very specific report and have been working for a while and just can't get it.

 

I have a table with employeeID, date_created, project_name, status, etc.,

 

I need to run a query that returns the total number of open projects for each employee. However - I need that report to display the total # of projects he's had open for 7 days, 8-14 days, and anything over 14 days.

 

Similar to:

 

Employee NameCurrentOver 1 wk.Over 2 wks

Billy Bob2110

Sally Sue503

 

I'm using Dreamweaver CS3 so if there's an easy way to do a database binding or something.

 

This is holding up the entire project, because I need to to this for Employees, Clients, and Attorneys and I've been working for a long time and just can't get it.

[beebum]

You will need multiple queries.

 

sql_1 = ...WHERE date_created BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND CURDATE();

sql_2 = ...WHERE date_created BETWEEN DATE_SUB(CURDATE(), INTERVAL 13 DAY) AND DATE_SUB(CURDATE(), INTERVAL 7 DAY);

sql_3 = ...WHERE date_created < DATE_SUB(CURDATE(), INTERVAL 14 DAY);

[aschk]

Another pivot table by the looks of it as you're creating the columns (7 days, 8-14, 14+).

 

I did the following using some example entries that I created. I'm assuming the '1' denoted status means still open.

It probably needs some tweaking to be honest, but from what I can see it works ok given my own data.

 

SELECT name
      ,SUM(IF(DATEDIFF(NOW(), date_created) <=7, 1, 0)) as 'Current'
      ,SUM(IF(DATEDIFF(NOW(), date_created) >7 AND DATEDIFF(NOW(), date_created) <= 14, 1, 0)) as 'Over 1Wk'
      ,SUM(IF(DATEDIFF(NOW(), date_created) > 14, 1, 0)) as 'Over 2Wks'
FROM employees e
WHERE `status` = 1
GROUP BY name

 

p.s. you don't need multiple queries.

[beebum]

nice 

[bdmovies]

Ok, I just can't wrap my mind around this one, I guess I did work on it for so long so many ways, I'm wo crossed up I don't know if I'm coming or going....

 

There was a little more that I left out from my prior post.

 

Table 1 contains (among other things) employeeID, date_created, status

 

Table 2 contains employeeID, First Name, Last Name

 

I need to run through all of the servers with open projects and display the pivot table, I'm getting lost on how to run the query and then instead of inserting the employees ID, inserting their name.....This has turned into a complete brain lock for me, it's all just mush.....

[aschk]

Shouldn't be anything more complex than performing a JOIN to get the name and doing the same pivot as before, e.g.

 

SELECT ei.first_name
      ,ei.last_name
      ,SUM(IF(DATEDIFF(NOW(), date_created) <=7, 1, 0)) as 'Current'
      ,SUM(IF(DATEDIFF(NOW(), date_created) >7 AND DATEDIFF(NOW(), date_created) <= 14, 1, 0)) as 'Over 1Wk'
      ,SUM(IF(DATEDIFF(NOW(), date_created) > 14, 1, 0)) as 'Over 2Wks'
FROM employees e
JOIN employee_information ei ON e.employeeID = ei.employeeID
WHERE `status` = 1
GROUP BY ei.employeeID

 

Note in the above i've guessed the table name as "employee_information".