CODEWITHSUNDEEP
mysqlsql
Kenny
Kenny

Reputation: 83

How to count consecutive number of 10 days

I have table with columns: id, name, date, present Column present have values 0 or 1 or 2 and ... more I need to count how many 0 valous is in current month 2013-07-01 - 2013-07-31 but count only when there are or more than 10 times.

for example if i have 2013-07-01 to 2013-07-10 valoues 0 it should count it and let me know that is 10 or more consecutives days like 11, 12 or more, but if it was less than 10 should count nothing.

I was trying some examples from stack... but they are different problems... so i need little help with that mysql query.

i have smth like this but need consecutives 10 days like >= 10

$sql = mysql_query("SELECT COUNT(name) as count FROM `table` WHERE (`present` = 0) AND (`date` BETWEEN '2013-07-01' AND '2013-07-31')");

while($row = mysql_fetch_array($sql)){
    $result = $row['count'];
}

It counts me every 0 values in date between 2013-07-01 and 2013-07-31 but i need count how many days start from 10 or more consecutives days

column present have 0 and other numbers like 1, 2, 3... so i need count only 0 with 10 or more consecutives days

here is SqlFiddle i was trying to make warking from answer http://sqlfiddle.com/#!2/1bde8/2

best regards m.

Upvotes: 0

Views: 1607

Answers (3)

Kickstart
Kickstart

Reputation: 21533

Not tested, but you could use user variables like this:-

SELECT SUM(if(ConsCounter=10, 1, 0))
FROM
(
    SELECT id, name, date, present, @Counter := IF(@PrevPresent = present AND present = 0, @Counter + 1, 0) AS ConsCounter, @PrevPresent = present
    FROM
    (
        SELECT id, name, date, present
        FROM `table`
        ORDER BY date
    ) Sub1
    CROSS JOIN (SELECT @PrevPresent:=-99999, @Counter:=0) Sub2
) Sub4

Get all the records in date order and add a sequence number for the count since the present was first 0. Then count the number of times that counter is 10.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This approach uses correlated subqueries to calculate two values.

The first value is the date of the previous record where Present = 1. This allows you to get the number of days in a row where Present = 0 by using datediff().

The second is the Present value of tomorrow, which will be NULL on the last day of the month. When today has Present = 0 and tomorrow is either 1 or NULL, then we can use this record. It is the end of a sequence of 0s.

From there is it just a question of adding up the values according to the conditions that you set. The following query assumes that you want to do this for each name:

select name, sum(case when datediff(date, lastPresentDate) >= 10
                      then datediff(date, lastPresentDate)
                 else 0 end) as DaysCounted
from (select t.*,
             (select coalesce(max(date), '2013-06-30')
              from t t2
              where t2.name = t.name and
                    t2.present <> 0 and
                    t2.date <= t.date and
                    t2.date between '2013-07-01' and '2013-07-31'
             ) as lastPresentDate,
             (select t2.present
              from t t2
              where t2.name = t.name and
                    t2.date = adddate(t.date, 1)
              order by t2.date
              limit 1
             ) as TomorrowPresent
      from t
      where date between '2013-07-01' and '2013-07-31'
     ) t
where Present = 0 and (TomorrowPresent = 1 and TomorrowPresent is null)
group by name

Upvotes: 2

Abhay
Abhay

Reputation: 6645

This query should give you a count only when it is 10 or greater than 10.

SELECT COUNT(`name`) as `count`
FROM `table`
WHERE (`present` = 0)
AND (`date` BETWEEN '2013-07-01' AND '2013-07-31')
HAVING `count` >= 10;

Hope it helps!

Upvotes: 0

Related Questions