INT comparison without WHERE statement

Question

Im trying to write a MySQL statement that will bring me back these results:

## Name | Day 0 | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 |
##Jeff  |   0   |    3  |     1 |     2 |    1  |   1   |
##Larry |   1   |    1  |     4 |     4 |    1  |   0   |

Based on how many tasks each employee performed on each day.

My database tables are as follows:

Employee

id (INT), number (VARCHAR), name (VARCHAR), dateStarted (VARCHAR),

Project

id (INT), number (VARCHAR), dateEnded (DATETIME)

Right now I'm using this statement:

SELECT 
a.name AS "Name",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 0",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 1",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 2",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 3",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 4",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 5"
FROM employee a, project b
WHERE b.number=a.number 
AND "Day 0" = 0
AND "Day 1" = 1
AND "Day 2" = 2
AND "Day 3" = 3
AND "Day 4" = 4
AND "Day 5" >= 5

Current Output

The above statement works but for some reason it does not provide the desired result mentioned in the requirement above. Any ideas on how I can fix/change it?

edit

If I take out:

AND "Day 0" = 0
AND "Day 1" = 1
AND "Day 2" = 2
AND "Day 3" = 3
AND "Day 4" = 4
AND "Day 5" >= 5

Then it prints out:

## Name | Day 0 | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 |
##Jeff  |   9   |    9  |     9 |     9 |    9  |   9   |

Answer 1

The previous solution could be improved by doing the following join statement or where..

Make sure to have an index ( unique ? ) on project (number,dateEnded) You could also try to add index on employee (number, dateStarted)

NULL values are usually not part of any indexes (as they are null), you could try updating the default value of that field to '0000-00-00', this way it would get indexed and potentially add speed.

Try this:

SELECT a.name AS "Name", 
       SUM(noOfDays = 0) AS "Day 0", SUM(noOfDays = 1) AS "Day 1", 
       SUM(noOfDays = 2) AS "Day 2", SUM(noOfDays = 3) AS "Day 3", 
       SUM(noOfDays = 4) AS "Day 4", SUM(noOfDays >= 5) AS "Day 5" 
FROM (SELECT a.number, a.name, DATEDIFF(DATE(b.dateEnded), DATE(a.dateStarted)) noOfDays
      FROM employee a INNER JOIN project b ON (b.number = a.number and b.dateEnded>a.dateStarted)
    ) AS a
GROUP BY a.name

Check the SQL FIDDLE DEMO

|    NAME | DAY 0 | DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 |
|---------|-------|-------|-------|-------|-------|-------|
|  ##Jeff |     0 |     3 |     1 |     2 |     1 |     1 |
| ##Larry |     1 |     1 |     4 |     4 |     1 |     0 |

Answer 2

Using the SQL fiddle data from one of the previous answers, I have tried this:-

SELECT Sub0.name AS "Name", 
    SUM(IF(DayDesc = 0 AND DaysCount IS NOT NULL, 1, 0)) AS "Day 0",
    SUM(IF(DayDesc = 1 AND DaysCount IS NOT NULL, 1, 0)) AS "Day 1",
    SUM(IF(DayDesc = 2 AND DaysCount IS NOT NULL, 1, 0)) AS "Day 2",
    SUM(IF(DayDesc = 3 AND DaysCount IS NOT NULL, 1, 0)) AS "Day 3",
    SUM(IF(DayDesc = 4 AND DaysCount IS NOT NULL, 1, 0)) AS "Day 4",
    SUM(IF(DayDesc = 5 AND DaysCount IS NOT NULL, 1, 0)) AS "Day 5"
FROM
(
    SELECT DISTINCT name FROM employee
)Sub0
CROSS JOIN
(
    SELECT 0 AS DayMin, 0 AS DayMax, 0 AS DayDesc
    UNION
    SELECT 1 AS DayMin, 1 AS DayMax, 1 AS DayDesc
    UNION
    SELECT 2 AS DayMin, 2 AS DayMax, 2 AS DayDesc
    UNION
    SELECT 3 AS DayMin, 3 AS DayMax, 3 AS DayDesc
    UNION
    SELECT 4 AS DayMin, 4 AS DayMax, 4 AS DayDesc
    UNION
    SELECT 5 AS DayMin, 999999999 AS DayMax, 5 AS DayDesc
) Sub1
LEFT OUTER JOIN 
(
    SELECT a.name, ABS(DATEDIFF(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded)) DaysCount
    FROM employee a
    INNER JOIN project b
    ON b.number = a.number 
) Sub2
ON Sub2.DaysCount BETWEEN Sub1.DayMin AND Sub1.DayMax
AND Sub2.name = Sub0.name
GROUP BY Sub0.name

Seems to give the right results and be quite fast.

SQL fiddle here:-

http://www.sqlfiddle.com/#!2/db498/84

Bit more efficient (but doesn't cope with employees who have no projects):-

SELECT Sub0.name AS "Name", 
    SUM(IF(DayDesc = 0, 1, 0)) AS "Day 0",
    SUM(IF(DayDesc = 1, 1, 0)) AS "Day 1",
    SUM(IF(DayDesc = 2, 1, 0)) AS "Day 2",
    SUM(IF(DayDesc = 3, 1, 0)) AS "Day 3",
    SUM(IF(DayDesc = 4, 1, 0)) AS "Day 4",
    SUM(IF(DayDesc = 5, 1, 0)) AS "Day 5"
FROM
(
    SELECT a.name, 
        CASE ABS(DATEDIFF(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))
            WHEN 0 THEN 0
            WHEN 1 THEN 1
            WHEN 2 THEN 2
            WHEN 3 THEN 3
            WHEN 4 THEN 4
            ELSE 5
        END AS DayDesc
    FROM employee a
    INNER JOIN project b
    ON b.number = a.number 
) Sub0
GROUP BY Sub0.name

SQL fiddle here:-

http://www.sqlfiddle.com/#!2/db498/89

Answer 3

Try this:

SELECT a.name AS "Name", 
       SUM(noOfDays = 0) AS "Day 0", SUM(noOfDays = 1) AS "Day 1", 
       SUM(noOfDays = 2) AS "Day 2", SUM(noOfDays = 3) AS "Day 3", 
       SUM(noOfDays = 4) AS "Day 4", SUM(noOfDays >= 5) AS "Day 5", 
       COUNT(1) AS "Total Days"
FROM (SELECT a.name, DATEDIFF(DATE(b.dateEnded), DATE(a.dateStarted)) noOfDays
      FROM employee a INNER JOIN project b ON b.number = a.number 
      WHERE b.dateEnded IS NOT NULL
    ) AS A
GROUP BY a.name;

Check the SQL FIDDLE DEMO

|    NAME | DAY 0 | DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 | TOTAL DAYS |
|---------|-------|-------|-------|-------|-------|-------|------------|
|  ##Jeff |     0 |     3 |     1 |     2 |     1 |     1 |          8 |
| ##Larry |     1 |     1 |     4 |     4 |     1 |     0 |         11 |

Answer 4

Try this:

SELECT
  emp.name AS '## Name',
  (SELECT COUNT(*)     
   FROM project p JOIN employee e ON p.number = e.number
   WHERE e.name = emp.name 
     AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 0
   ) AS 'Day 0'
  ,(SELECT COUNT(*)
    FROM project p JOIN employee e ON p.number = e.number
    WHERE e.name = emp.name 
      AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 1
   ) AS 'Day 1'
  ,(SELECT COUNT(*)
    FROM project p JOIN employee e ON p.number = e.number
    WHERE e.name = emp.name 
      AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 2
   ) AS 'Day 2'
  ,(SELECT COUNT(*)
    FROM project p JOIN employee e ON p.number = e.number
    WHERE e.name = emp.name 
      AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 3
   ) AS 'Day 3'
  ,(SELECT COUNT(*)
    FROM project p JOIN employee e ON p.number = e.number
    WHERE e.name = emp.name 
      AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 4
   ) AS 'Day 4'
  ,(SELECT COUNT(*)
    FROM project p JOIN employee e ON p.number = e.number
    WHERE e.name = emp.name 
      AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) >= 5
   ) AS 'Day 5'
FROM employee emp
GROUP BY emp.name

See SQL Fiddle Demo (- have made some assumptions on your data based on the info provided).