Cycle Time - DATEDIFF()

[_spaz]

Hi all,

 

Can someone point me in the right direction with the below MYSQL statement? I'm trying to create buckets of "timeframes" that a specific item is received and when it is released (cycle time).  I want to be able to count the amount of items that fall within each time frame.  The below statement works but its not the best way to do since i do want to add a time period to it later such as sample set for each month.

 

"Select

(select count(*) from Table where DATEDIFF(Release, Arrival ) < 0) as less0,

(select count(*) from Table where DATEDIFF(Release, Arrival ) = 0) as 0day,

(select count(*) from Table where DATEDIFF(Release, Arrival ) BETWEEN 1 and 5) as 1to5,

(select count(*) from Table where DATEDIFF(Release, Arrival ) BETWEEN 6 and 10) as 6to10"

 

 

[JustLikeIcarus]

You could try something along the lines of this.  Which would enable you to come back and add in a where clause.  Also this only requires reading in the table once.

 

Select sum(if(DATEDIFF(Release, Arrival ) < 0, 1, 0)) as less0, sum(if(DATEDIFF(Release, Arrival ) = 0, 1, 0)) as 0day, sum(if(DATEDIFF(Release, Arrival ) BETWEEN 1 and 5, 1, 0)) as 1to5, sum(if(DATEDIFF(Release, Arrival ) BETWEEN 6 and 10, 1, 0)) as 6to10
FROM table

[_spaz]

Thank you very much, i was able to get it working with you're syntax.