MySQL select rows based on a result between two date columns

Question

I have a table like this:

id | Type |   Date     |  DocDate
------------------------------
1  |  A   | 2014-04-05 | 2014-04-05
2  |  A   | 2014-05-06 | 2014-05-08
3  |  B   | 2014-06-06 | 2014-06-06

I need to create a stacked bar char. But to do this I need to transform my table into:

Type |  Green  |  Red
-----------------------
 A   |   1     |   1
 B   |   1     |   0

Where Green and Red represent the result of the following condition:

IF (DocDate - Date) <= 1 THEN Green
IF (DocDate - Date) > 1  THEN Red

I tried this query:

SELECT CASE 
    WHEN (DocDate - Date) <= 1 THEN 'Green'
    WHEN (DocDate - Date) > 1 THEN 'Red'
END AS X, Count(OccurrenceID) AS Total
FROM tbloccurrence
GROUP BY CASE
    WHEN (DocDate - Date) <= 1 THEN 'Green'
    WHEN (DocDate - Date) > 1 THEN 'Red'
END;

But the result is not what I needed. Can anybody help me?

Answer 1

I started by writing a query that pulled each row from your table, and assigned the green or red columns. To do that, you can use the IF() statement. This takes 3 params: a condition, what to do if that condition is true, and what to do if that condition is false. That gives us this:

SELECT type, IF(DATEDIFF(docdate, dateCol) <= 1, 1, 0) AS green, IF(DATEDIFF(docdate, dateCol) > 1, 1, 0) AS red
FROM myTable;

I also used the DATEDIFF() function. That returns an integer amount of the days in between the two parameters (it takes DATE or DATETIME params).

Then, I just summed the red and green columns and grouped by type to match your result set:

SELECT type, SUM(IF(DATEDIFF(docdate, dateCol) <= 1, 1, 0)) AS green, SUM(IF(DATEDIFF(docdate, dateCol) > 1, 1, 0)) AS red
FROM myTable
GROUP BY type;

Here is your complimentary SQL Fiddle.