Get number of monday in a rangedate mysql

Question

Get number of monday in a rangedate MySQL, I run this code but it give me result 0:

select count(*) from tarif where weekday(`end_tarif`<= '2019-02-21'AND `start_tarif`>='2019-02-05') = 0;

my table:

CREATE TABLE `tarif` (                  
    `tarif_id` int(11) NOT NULL AUTO_INCREMENT,
    `start_tarif` date NOT NULL,
    `end_tarif` date NOT NULL,
    `day_tarif` varchar(50) NOT NULL,
    PRIMARY KEY (`tarif_id`)
);

INSERT INTO `tarif` VALUES (1, '2019-02-01', '2019-02-10', '10'),
    (2, '2019-02-11', '2019-02-20', '20'),
    (3, '2019-02-21', '2019-02-28', '10'),
    (4, '2019-03-01', '2019-02-10', '15');

im having confusion understanding the question. You want to list mondays which lie in the range? — Farhan Qasim, Feb 07 '19 at 09:24
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek — jarlh, Feb 07 '19 at 09:27
i want the number of monday includes in the range '2019-02-5' -'2019-02-21' — user3623368, Feb 07 '19 at 09:36
You mean you want to know how many Mondays there are between those dates? — Strawberry, Feb 07 '19 at 09:50
@ Strawberry,i wont get the result only if thedaterange is include between start_tarif andend_tarif in the table — user3623368, Feb 07 '19 at 10:54

score 0 · Answer 1 · answered Feb 07 '19 at 10:11

Below mentioned query is for sundays count. you can modify it as per your requirement

select ROUND((
      (unix_timestamp(`end_tarif`) - unix_timestamp(`start_tarif`) )/(24*60*60)
      -7+WEEKDAY(`start_tarif`)-WEEKDAY(`end_tarif`)
    )/7)

 + if(WEEKDAY(`start_tarif`) <= 6, 1, 0)
 + if(WEEKDAY(`end_tarif`) >= 6, 1, 0) as Sunday
from tarif 
where  `end_tarif`<= '2019-02-21' AND `start_tarif`>='2019-02-05'  ;

Sebastian Brosch · Accepted Answer · 2019-02-07T19:40:19.940

You can use a solution using a calendar table. So you can use a solution like the following:

1. create a table with calendar data

-- create the table "calendar"
CREATE TABLE `calendar` (
  `dateValue` DATE
);

-- insert the days to the table "calendar"
INSERT INTO calendar
SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
    (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
HAVING gen_date BETWEEN '2019-01-01' AND '2019-12-31'

You can find the script to generate the calendar data on StackOverflow:
How to populate a table with a range of dates?

2. create the table with your data (with monday tarif)

-- create the table "tarif"
CREATE TABLE tarif (
  tarif_id INT(11) NOT NULL AUTO_INCREMENT,
  start_tarif DATE NOT NULL,
  end_tarif DATE NOT NULL,
  day_tarif VARCHAR(50) NOT NULL,
  monday_tarif VARCHAR(50) NOT NULL,
  PRIMARY KEY (tarif_id)
);

-- insert the tarif information
INSERT INTO tarif VALUES 
  (1, '2019-02-01', '2019-02-10', '10', '5'),
  (2, '2019-02-11', '2019-02-20', '20', '5'),
  (3, '2019-02-21', '2019-02-28', '10', '5'),
  (4, '2019-03-01', '2019-02-10', '15', '5');

Note: To create a useful example I added the column monday_tarif and insert the value 5 on every date range.

3. get the result

Now you can get all days of your needed range (between 2019-02-05 and 2019-02-21) from the calendar table. With a LEFT JOIN you add your tarif table to all days of date range.

With a CASE WHEN and the condition DAYOFWEEK = 2 or DAYNAME = 'Monday' you can check if the current date is a Monday or not, to get the correct tarif value of the day.

SELECT SUM(CASE WHEN DAYOFWEEK(cal.dateValue) = 2 THEN tarif.monday_tarif ELSE tarif.day_tarif END) AS sumWithMondayTarif 
FROM calendar cal
    LEFT JOIN tarif ON cal.dateValue BETWEEN start_tarif AND end_tarif
WHERE cal.dateValue BETWEEN '2019-02-05' AND '2019-02-21';

You can also use a SELECT with a sub select of the calendar:

SELECT SUM(CASE WHEN DAYOFWEEK(cal.dateValue) = 2 THEN tarif.monday_tarif ELSE tarif.day_tarif END) AS sumWithMondayTarif FROM (
  SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) dateValue FROM
    (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
  HAVING dateValue BETWEEN '2019-02-05' AND '2019-02-21'
) cal LEFT JOIN tarif ON cal.dateValue BETWEEN start_tarif AND end_tarif

demo on dbfiddle.uk

Get number of monday in a rangedate mysql

2 Answers2

Linked