CODEWITHSUNDEEP
phpmysqlmysqli
Marc
Marc

Reputation: 41

PHP MYSQLI count users with two consecutive dates

How can I count users with two consecutive absences only? I have to create a chart to show frequency absences by employee.

My table name = incidencias 

id | name | dateA     | description
1  | al   |2017-08-01| absence
2  | al   |2017-08-02| absence
3  | alex |2017-08-01| absence
4  | alex |2017-08-02| absence
5  | alex |2017-08-03| absence
6  | al2  |2017-08-01| absence
7  | al2  |2017-08-02| absence

I want the result to be 2, only al and al2 have two consecutive dates where description = absence.

I´m using php to run the query, i did try this code i found but and I tested it in sqlfiddle and works great,. but not in my host.I think this is for PostgreSQL.

$query2 = mysqli_query($conn, "SELECT name,
                sum(diff) as days, 
                (dateA) as work_start, 
                (dateA) as work_end 
            FROM (SELECT name,
                    dateA, 
                    diff 
            FROM (select name, 
                    dateA,
                    nvl(dateA- lag(dateA) over (partition by name order by dateA),1) as diff 
            from incidencias 
            where description = 'absence'
            ) t1 
            where diff = 1 
            ) t2 
            group by name
            having sum(diff) = 2");

$row_cnt = mysqli_num_rows($query2);

printf("Result set has %d rows.\n", $row_cnt);

I would really appreciate it.

Upvotes: 1

Views: 140

Answers (2)

cwallenpoole
cwallenpoole

Reputation: 81998

So, this is normally done through JOINing on to the same table.

SELECT oinc.* 
FROM incidencias oinc
LEFT JOIN 
     incidencias iinc
     ON (oinc.name = iinc.name AND oinc.description = iinc.description)
WHERE description = 'absence' 
     AND oinc.dateA = DATE_ADD( iinc.dateA, 'INTERVAL 1 DAY');

So, line by line:

SELECT oinc.* -- grab me everything from the oinc table

FROM incidencias oinc -- We're going to call incidencias "oinc" in this query
                      -- "oinc" is now an alias for "incidencias"

LEFT JOIN -- I want a result whether or not the result is duplicated.
          -- (Technically, by including the condition that it not be duplicated
          -- this is the same thing as an "INNER JOIN".)

incidencias iinc -- We're looking at the same table, better call it something else

ON (oinc.name = iinc.name AND oinc.description = iinc.description) 
   -- We're matching the name and the description between the two
   -- aliases of the table (oinc, iicn)

WHERE description = 'absence' -- Should be obvious
    AND oinc.dateA = DATE_ADD( iinc.dateA, 'INTERVAL 1 DAY'); -- the iinc alias
           -- has a date which is one day less than the oinc alias

Some side notes:

  • I used left join so that you can omit the AND ... later.
  • You should experiment with moving that AND query from the WHERE into the ON clause. Then you can use an INNER join. You'll get the same results, but knowing both will help you more later.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

Here's one way (there may be a simpler solution, but this should be fast anyway)...

SELECT COUNT(*)
  FROM 
     ( SELECT name 
            , MAX(i) i
         FROM 
            ( SELECT x.*
                   , CASE WHEN @prev_name = name THEN 
                       CASE WHEN @prev_date = datea - INTERVAL 1 DAY THEN @i:=@i+1 ELSE @i:=1 END 
                       ELSE @i:=1 END i
                   , @prev_name := name
                   , @prev_date := datea
                FROM my_table x
                   , ( SELECT @prev_name:=null,@prev_date:=null, @i:=1) vars 
               WHERE x.description = 'absence'
               ORDER 
                  BY name
                   , datea
            ) b
        GROUP
           BY name
       HAVING i = 2
     ) p;

Upvotes: 0

Related Questions