CODEWITHSUNDEEP
mysqlsql
Bap mop
Bap mop

Reputation: 320

How to get each day time in and out and count it in query between date range?

This is the query i'm using right now:

Select *
from attendance
where (in_time BETWEEN '2019-06-20 00:00:01' and '2019-06-20 23:59:59')
and (out_time BETWEEN '2019-06-28 00:00:01' and '2019-06-28 23:59:59')

Sample Database Image

and i need an a output like this.

Sample Output

or any ideas how do i count each date range base on my sample database

Upvotes: 2

Views: 131

Answers (2)

Bap mop
Bap mop

Reputation: 320

I used this query to collect data in_time and count then remove the time using substring.
SELECT substring(in_time,1,10), COUNT(DISTINCT employees_id) FROM attendance WHERE (in_time BETWEEN '2019-06-20 00:00:01' and '2019-06-28 23:59:59') GROUP BY substring(in_time ,1,10)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you need to start with a list of dates and then use left join or a correlated subquery:

select d.dte,
       (select count(*)
        from attendance a
        where a.in_time <= dte and a.out_time >= dte
       ) as cnt
from (select date('2019-06-20') as dte union all
      select date('2019-06-21') as dte union all
      select date('2019-06-22') as dte union all
      select date('2019-06-23') as dte union all
      select date('2019-06-24') as dte union all
      select date('2019-06-25') as dte union all
      select date('2019-06-26') as dte union all
      select date('2019-06-27') as dte union all
      select date('2019-06-28') as dte
     ) d;

Upvotes: 1

Related Questions