CODEWITHSUNDEEP
phpmysql
BN83
BN83

Reputation: 902

List results by "x Days Old"

I have a table with applications, when they've been contacted the "contacted" column is marked with a "1". What I want to do if possible is count how many of the results are up to 3 days old, how many of them are 4-6 days old and how many of them are 7 or more days old. Is this possible in a single query?

 __________________________________
| Name | Contacted |    Date       |
 ----------------------------------
| Bob  |      1    |  2016-09-16   |
| Ben  |      1    |  2016-10-03   |
| Sam  |      1    |  2016-10-03   |

EDIT:

Using the following:

SELECT 
case  
    when datediff( CURDATE(), `submission_date`)  = 3 then '3 Days'
    when datediff( CURDATE(), `submission_date`)  between 4 and 6 then '4-6 Days'
    when datediff( CURDATE(), `submission_date`)  > 6 then  '7 or more days'
end as `days`,
sum( case  
    when datediff( CURDATE(), `submission_date`)  = 3 then 1 
    when datediff( CURDATE(), `submission_date`)  between 4 and 6 then 1
    when datediff( CURDATE(), `submission_date`)  > 6 then 1
else 0 
end  ) as tot 
FROM my_table 
GROUP BY
case  
    when datediff( CURDATE(), `submission_date`)  = 3 then '3 Days'
    when datediff( CURDATE(), `submission_date`)  between 4 and 6 then '4-6 Days'
    when datediff( CURDATE(),`submission_date`) > 6 then  '7 or more days'
end ;

Then

echo '<p>'.$row['tot'].'</p>';

I'm getting:

0
1

Upvotes: 0

Views: 34

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a select case and group by

 select 
     case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(), `date`)  > 6 then  '7 or more days'
      end  as `days`,
    sum( case  
        when datediff( CURDATE(), `date`)  <= 3 then 1 
        when datediff( CURDATE(), `date`)  between 4 and 6 then 1
        when datediff( CURDATE(), `date`)  > 6 then 1
        else 0 
    end  ) as tot 
 from my_table 
 where contacted = 1
 group by 
      case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(),`date`) > 6 then  '7 or more days'
      end ;

In the column my_result you should find the string you need 

   select  concat( 'You have *** ',  sum( case  
        when datediff( CURDATE(), `date`)  <= 3 then 1 
        when datediff( CURDATE(), `date`)  between 4 and 6 then 1
        when datediff( CURDATE(), `date`)  > 6 then 1
        else 0 
    end  ), ' *** New Items more than ' , 
     case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days old'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(), `date`)  > 6 then  '7 or more days'
      end ) as my_result 
 from my_table 
 where contacted = 1
 group by 
      case  
        when datediff( CURDATE(), `date`)  <= 3 then '3 Days'
        when datediff( CURDATE(), `date`)  between 4 and 6 then '4-6 Days'
        when datediff( CURDATE(),`date`) > 6 then  '7 or more days'
      end ;

Upvotes: 1

Related Questions