There is a table with a range of dates, there is also a table in which the "working" days are entered (the day of the week is indicated)

How can I count the number of "working" days for a range of dates?

For example, a person set the range from 07/13/2015 to 07/22/2015 (inclusive), indicated that he will work every Monday and Wednesday.

The result of working days should be 4 because Monday and Wednesday meet 2 times.

Tell me how to implement a query to the database?

  • You can, of course, when adding a working day, programmatically sign the date (generate php from the days of the week), but if a person specifies a range for 5499, it seems there will be a lot of data =) - Rostislav
  • Oddities are written. Maybe under update add the task description? - borodatych
  • A person can create an "event", indicate to him from what time and on what date it goes, choose the days of the week and the time in which this event is held. I need to make a conclusion of all events by day. I implement the "paginator" in the form of an "endless ribbon", for it you need to know the number of elements for each day. there is a paginator in a paginator =) - Rostislav
  • In this formulation, the task falls poorly in SQL. Now, if you come to the need to mark actual work shifts (ie, with any deviations from the "theory"), then you will have a table where each date is a separate entry and the number of working days will be calculated as SELECT COUNT(*) FROM sched WHERE wdate BETHWEEN :d1 AND :d2 - artoodetoo
  • here's a fun construct (not sure about its performance) stackoverflow.com/q/18460158/4827341 - aleksandr barakin

3 answers 3

in the dni table - selected days of the week (0 - Monday, 1 - Tuesday, etc.).

in the period table - 30 examples of time intervals (the starting day is included in the calculation, the final day is not included).

in the final query, the number of Mondays and Fridays between dates.

the function that performs the calculation can probably be further simplified:

SQL feeddle

MySQL 5.6 Schema Setup :

 create table dni (id int, d int); create table period (id int, s date, p date); insert into dni values (1, 0) # пн ,(1, 4) # пт ; insert into period values (1, '2015-01-01', '2015-01-02') ,(1, '2015-01-01', '2015-01-03') ,(1, '2015-01-01', '2015-01-04') ,(1, '2015-01-01', '2015-01-05') ,(1, '2015-01-01', '2015-01-06') ,(1, '2015-01-01', '2015-01-07') ,(1, '2015-01-01', '2015-01-08') ,(1, '2015-01-01', '2015-01-09') ,(1, '2015-01-01', '2015-01-10') ,(1, '2015-01-01', '2015-01-11') ,(1, '2015-01-01', '2015-01-12') ,(1, '2015-01-01', '2015-01-13') ,(1, '2015-01-01', '2015-01-14') ,(1, '2015-01-01', '2015-01-15') ,(1, '2015-01-01', '2015-01-16') ,(1, '2015-01-01', '2015-01-17') ,(1, '2015-01-01', '2015-01-18') ,(1, '2015-01-01', '2015-01-19') ,(1, '2015-01-01', '2015-01-20') ,(1, '2015-01-01', '2015-01-21') ,(1, '2015-01-01', '2015-01-22') ,(1, '2015-01-01', '2015-01-23') ,(1, '2015-01-01', '2015-01-24') ,(1, '2015-01-01', '2015-01-25') ,(1, '2015-01-01', '2015-01-26') ,(1, '2015-01-01', '2015-01-27') ,(1, '2015-01-01', '2015-01-28') ,(1, '2015-01-01', '2015-01-29') ,(1, '2015-01-01', '2015-01-30') ,(1, '2015-01-01', '2015-01-31') ;

Query 1 :

 select date_format(s,'%Y-%m-%d, %a') as 'начало', date_format(p,'%Y-%m-%d, %a') as 'конец', sum( ceil((ps)/7)-1+ if((weekday(p)>weekday(s) and (weekday(s)<=d and weekday(p)>d)) or (weekday(p)<=weekday(s) and (weekday(s)<=d or weekday(p)>d)), 1,0) ) as 'дней' from period, dni group by s, p order by s, p

Results :

 | начало | конец | дней | |-----------------|-----------------|------| | 2015-01-01, Thu | 2015-01-02, Fri | 0 | | 2015-01-01, Thu | 2015-01-03, Sat | 1 | | 2015-01-01, Thu | 2015-01-04, Sun | 1 | | 2015-01-01, Thu | 2015-01-05, Mon | 1 | | 2015-01-01, Thu | 2015-01-06, Tue | 2 | | 2015-01-01, Thu | 2015-01-07, Wed | 2 | | 2015-01-01, Thu | 2015-01-08, Thu | 2 | | 2015-01-01, Thu | 2015-01-09, Fri | 2 | | 2015-01-01, Thu | 2015-01-10, Sat | 3 | | 2015-01-01, Thu | 2015-01-11, Sun | 3 | | 2015-01-01, Thu | 2015-01-12, Mon | 3 | | 2015-01-01, Thu | 2015-01-13, Tue | 4 | | 2015-01-01, Thu | 2015-01-14, Wed | 4 | | 2015-01-01, Thu | 2015-01-15, Thu | 4 | | 2015-01-01, Thu | 2015-01-16, Fri | 4 | | 2015-01-01, Thu | 2015-01-17, Sat | 5 | | 2015-01-01, Thu | 2015-01-18, Sun | 5 | | 2015-01-01, Thu | 2015-01-19, Mon | 5 | | 2015-01-01, Thu | 2015-01-20, Tue | 6 | | 2015-01-01, Thu | 2015-01-21, Wed | 6 | | 2015-01-01, Thu | 2015-01-22, Thu | 6 | | 2015-01-01, Thu | 2015-01-23, Fri | 6 | | 2015-01-01, Thu | 2015-01-24, Sat | 7 | | 2015-01-01, Thu | 2015-01-25, Sun | 7 | | 2015-01-01, Thu | 2015-01-26, Mon | 7 | | 2015-01-01, Thu | 2015-01-27, Tue | 8 | | 2015-01-01, Thu | 2015-01-28, Wed | 8 | | 2015-01-01, Thu | 2015-01-29, Thu | 8 | | 2015-01-01, Thu | 2015-01-30, Fri | 8 | | 2015-01-01, Thu | 2015-01-31, Sat | 9 |

Disclaimer: I did not register the relationship between the tables as unnecessary to demonstrate the operation of the function.

  • | 2015-01-01, Thu | 2015-01-12, Mon | 3 |. This period contains 2 Fridays and 2 Mondays. Are the boundary conditions exactly correct? - Zufir 2:58 pm
  • @Zufir, the final date is not in the range, as written in the answer. is not included for purely technical reasons - the date is, generally zero hours zero minutes zero seconds of the specified day. and the day itself is not included in the "date". - aleksandr barakin
  • Yes, indeed, I looked. Excuse me. Just got used that "with-by" is inclusive. - Zufir

In two steps. In MSSQL, I would use a recursive CTE, with MySQL, I had very little to do, so the code will follow links from Google.
1. Expand the period to the list of dates: https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query
2. Go to the list of dates by DayOfWeek your table with working days.

    Real heroes always go around;) Try:

     <?php $d1 = new DateTime( '2015-07-13' ); $d2 = new DateTime( '2015-07-22' ); $dofw = array( 1, 3 ); // 0 - воскресенье $sum = 0; while ( $d1 <= $d2 ) { if ( in_array( $d1->format( 'w' ), $dofw ) ) $sum++; $d1->modify( '+1 day' ); } echo $sum, "\n";