FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE IF NOT EXISTS `account_schedule` (
  `account_schedule_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `update_user_id` int DEFAULT NULL,
  `delete_user_id` int DEFAULT NULL,
  `account_id` int NOT NULL,
  `type` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `title` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `location` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `color` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `recurring` enum('yes','no') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `repeat_start` date NOT NULL,
  `repeat_type` enum('daily','weekly','monthly','yearly','specific_date') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `repeat_interval` int DEFAULT NULL,
  `repeat_day_of_month` int DEFAULT NULL,
  `repeat_month` int DEFAULT NULL,
  `repeat_day_of_week` int DEFAULT NULL,
  `status` enum('on','off') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`account_schedule_id`),
  KEY `type` (`type`),
  KEY `user_id` (`user_id`),
  KEY `account_id` (`account_id`),
  KEY `status` (`status`),
  KEY `recurring` (`recurring`),
  KEY `repeat_start` (`repeat_start`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `account_schedule` (`account_schedule_id`, `user_id`, `update_user_id`, `delete_user_id`, `account_id`, `type`, `title`, `content`, `location`, `color`, `recurring`, `repeat_start`, `repeat_type`, `repeat_interval`, `repeat_day_of_month`, `repeat_month`, `repeat_day_of_week`, `status`) VALUES(1, 1, NULL, NULL, 1, NULL, 'Monthly', NULL, NULL, NULL, 'yes', '2024-06-30', 'monthly', 1, NULL, NULL, NULL, 'on');
INSERT INTO `account_schedule` (`account_schedule_id`, `user_id`, `update_user_id`, `delete_user_id`, `account_id`, `type`, `title`, `content`, `location`, `color`, `recurring`, `repeat_start`, `repeat_type`, `repeat_interval`, `repeat_day_of_month`, `repeat_month`, `repeat_day_of_week`, `status`) VALUES(2, 1, NULL, NULL, 1, NULL, 'every 2 months', NULL, NULL, NULL, 'yes', '2024-06-30', 'monthly', 2, NULL, NULL, NULL, 'on');

SELECT `account_schedule_id`, `user_id`, `update_user_id`, `delete_user_id`, `account_id`, `type`, `title`, `content`, `location`, `color`, `recurring`, `repeat_start`, `repeat_type`, `repeat_interval`, `repeat_day_of_month`, `repeat_month`, `repeat_day_of_week`, `status`
            FROM `account_schedule`
            WHERE status = 'on'
              AND 
              (
                  recurring = 'yes'
              AND repeat_start <= '2025-09-08'
              AND (
                (repeat_type = 'daily' AND 
                 DATE_ADD(repeat_start, INTERVAL FLOOR(DATEDIFF('2023-07-28', repeat_start) / repeat_interval) * repeat_interval DAY) BETWEEN '2023-07-28' AND '2025-09-08')
                OR
                (repeat_type = 'weekly' AND 
                 DATE_ADD(repeat_start, INTERVAL FLOOR(DATEDIFF('2023-07-28', repeat_start) / (7 * repeat_interval)) * (7 * repeat_interval) DAY) BETWEEN '2023-07-28' AND '2025-09-08')
      
               OR     (repeat_type = 'monthly'
                       /*
                       AND 
 DATE_ADD(
    repeat_start, 
    INTERVAL (FLOOR(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM '2023-07-28'), EXTRACT(YEAR_MONTH FROM repeat_start)) / repeat_interval) * repeat_interval) MONTH
 ) BETWEEN '2023-07-28' AND '2025-09-08'
                       */
)
               
                OR
                (repeat_type = 'yearly' AND
                 DATE_ADD(repeat_start, INTERVAL FLOOR(YEAR('2023-07-28') - YEAR(repeat_start)) / repeat_interval * repeat_interval YEAR) BETWEEN '2023-07-28' AND '2025-09-08' AND MONTH(repeat_start) = repeat_month
                  )
                      
                 
                OR
                (repeat_type = 'specific_date' AND 
                 MONTH(repeat_start) = MONTH(DATE_ADD('2023-07-28', INTERVAL 1 DAY)) AND 
                 DAY(repeat_start) = DAY(DATE_ADD('2023-07-28', INTERVAL 1 DAY)) AND 
                 DATE_ADD(repeat_start, INTERVAL YEAR('2025-09-08') - YEAR(repeat_start) YEAR) BETWEEN '2023-07-28' AND '2025-09-08')
              )) OR (
                 recurring = 'no' AND repeat_start BETWEEN '2023-07-28' AND '2025-09-08'
              
              );
              
              
  select             `account_schedule_id`,
               DATE_ADD(
    repeat_start, 
    INTERVAL (FLOOR(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM '2023-07-28'), EXTRACT(YEAR_MONTH FROM repeat_start)) / repeat_interval) * repeat_interval) MONTH
 )
 FROM `account_schedule`

account_schedule_id	user_id	update_user_id	delete_user_id	account_id	type	title	content	location	color	recurring	repeat_start	repeat_type	repeat_interval	repeat_day_of_month	repeat_month	repeat_day_of_week	status
1	1	NULL	NULL	1	NULL	Monthly	NULL	NULL	NULL	yes	2024-06-30	monthly	1	NULL	NULL	NULL	on
2	1	NULL	NULL	1	NULL	every 2 months	NULL	NULL	NULL	yes	2024-06-30	monthly	2	NULL	NULL	NULL	on

+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| account_schedule_id | DATE_ADD(
repeat_start,
INTERVAL (FLOOR(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM '2023-07-28'), EXTRACT(YEAR_MONTH FROM repeat_start)) / repeat_interval) * repeat_interval) MONTH
) |
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 2023-07-30 |
| 2 | 2023-06-30 |
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+