I have a single line in MySQL table: volunteers

user_id | start_date | end_date
11122   | 2017-04-20 | 2018-02-17

How can I find how many times the 3rd day or 24th day of a month appears? (i.e. 2017-05-03, 2017-06-03, 2017-12-24, 2018-01-24) I'm trying to get to the following count:

Sample Output:

user_id | number_of_third_day | number_of_twenty_fourth_day
11122   |        10           |              10

I look at the documentation online to see if there is a way I can say (pseudo):

SELECT 
day, COUNT(*)
FROM volunteers
WHERE day(between(start_date, end_date)) in (3,24)

I tried to create a calendar table to no avail, but I would try to get the days, GROUP BY day, and COUNT(*) times that day appears in the range

  WITH calendar AS (

  SELECT start_date AS date
  FROM volunteers
  UNION ALL 
  SELECT DATE_ADD(start_date,  INTERVAL 1 DAY) as date
  FROM volunteers
  WHERE DATE_ADD(start_date,  INTERVAL 1 DAY) <= end_date

)

SELECT date FROM calendar;

Thanks for any help!

CodePudding user response：

A dynamic approach is.

but creating the dateranges, takes a lot of time, so you should have a date table to get the dates

CREATE TABLE table1
    (`user_id` int, `start_date` varchar(10), `end_date` varchar(10))
;
    
INSERT INTO table1
    (`user_id`, `start_date`, `end_date`)
VALUES
    (11122, '2017-04-20', '2018-02-17')
  ,(11123, '2019-04-20', '2020-02-17')
;

Records: 2  Duplicates: 0  Warnings: 0

WITH RECURSIVE cte AS (
    SELECT
       user_id,
       `start_date` as date_run ,
       `end_date` 
       FROM table1
    UNION ALL
    SELECT 
        user_id,
        DATE_ADD(cte.date_run, INTERVAL 1 DAY),
        end_date        
    FROM cte 
    WHERE DATE_ADD(date_run, INTERVAL 1 DAY) <= end_date
)SELECT user_id,
  SUM(DAYOFMONTH(date_run) = 3) as day_3th,
  SUM(DAYOFMONTH(date_run) = 24) as day_24th
  FROM cte
GROUP BY user_id

user_id	day_3th	day_24th
11122	10	10
11123	10	10

fiddle

CodePudding user response：

In last MySQL version you can use recursion:

-- get list of all dates in interval
with recursive dates(d) as (
    select '2017-04-20'
    union all
    select date_add(d, interval 1 day) from dates where d < '2018-02-17'
) select 
    -- calculate
    sum(day(d) = 10) days_10,
    sum(day(d) = 24) days_24
from dates
-- filter 10 & 24 days
where day(d) = 10 or day(d) = 24;

https://sqlize.online/sql/mysql80/c00eb7de69d011a85502fa538d64d22c/

CodePudding user response：

This one is more optimized since I generate date range by months not days as other questions, so its faster

WITH RECURSIVE cte AS
(
  SELECT user_id, DATE_FORMAT(start_date, '%Y-%m-03') as third_day,
   DATE_FORMAT(start_date, '%Y-%m-24') as twenty_fourth_day,
   start_date, end_date
   FROM table1
  UNION ALL
  SELECT user_id, 
   DATE_FORMAT(third_day   INTERVAL 1 MONTH, '%Y-%m-03') as third_day,
   DATE_FORMAT(twenty_fourth_day   INTERVAL 1 MONTH, '%Y-%m-24') as twenty_fourth_day,
   start_date, end_date
  FROM cte
  WHERE third_day   INTERVAL 1 MONTH <= end_date
)
SELECT user_id,
 SUM(CASE WHEN third_day BETWEEN start_date AND end_date THEN 1 ELSE 0 END) AS number_of_third_day,
 SUM(CASE WHEN twenty_fourth_day BETWEEN start_date AND end_date THEN 1 ELSE 0 END) AS number_of_twenty_fourth_day
FROM cte
GROUP BY user_id;

Demo here

CodePudding user response：

As long as you are looking for days that occur in every month (so not the 29th or beyond), this is just straightforward math. The number of whole calendar months between two dates (exclusive) is:

timestampdiff(month,start_date,end_date) - (day(start_date) <= day(end_date))

Then add one if the start month includes the target day and one if the end month includes it:

timestampdiff(month,start_date,end_date) - (day(start_date) <= day(end_date))
      (day(start_date) <= 3)   (day(end_date) >= 3)