https://www.youtube.com/watch?v=0w5mx6lfx1Y&list=PLavw5C92dz9Hxz0YhttDniNgKejQlPoAn&index=7

STR_TO_DATE()

-- Drop the table if it exists
DROP TABLE IF EXISTS Day_Indicator;

-- Create the Day_Indicator table
CREATE TABLE Day_Indicator (
    Product_ID VARCHAR(10),	
    Day_Indicator VARCHAR(7),
    Dates DATE
);

-- Insert data into the Day_Indicator table
INSERT INTO Day_Indicator VALUES 
('AP755', '1010101', STR_TO_DATE('04-Mar-2024','%d-%b-%Y')),
('AP755', '1010101', STR_TO_DATE('05-Mar-2024','%d-%b-%Y')),
('AP755', '1010101', STR_TO_DATE('06-Mar-2024','%d-%b-%Y')),
('AP755', '1010101', STR_TO_DATE('07-Mar-2024','%d-%b-%Y')),
('AP755', '1010101', STR_TO_DATE('08-Mar-2024','%d-%b-%Y')),
('AP755', '1010101', STR_TO_DATE('09-Mar-2024','%d-%b-%Y')),
('AP755', '1010101', STR_TO_DATE('10-Mar-2024','%d-%b-%Y')),
('XQ802', '1000110', STR_TO_DATE('04-Mar-2024','%d-%b-%Y')),
('XQ802', '1000110', STR_TO_DATE('05-Mar-2024','%d-%b-%Y')),
('XQ802', '1000110', STR_TO_DATE('06-Mar-2024','%d-%b-%Y')),
('XQ802', '1000110', STR_TO_DATE('07-Mar-2024','%d-%b-%Y')),
('XQ802', '1000110', STR_TO_DATE('08-Mar-2024','%d-%b-%Y')),
('XQ802', '1000110', STR_TO_DATE('09-Mar-2024','%d-%b-%Y')),
('XQ802', '1000110', STR_TO_DATE('10-Mar-2024','%d-%b-%Y'));

-- Select all data from the Day_Indicator table
SELECT * FROM Day_Indicator;

SUBQUERY
SUBSTRING()
WEEKDAY()/DAYOFWEEK()
FLAG

SELECT 
    Product_ID, Day_Indicator, Dates
FROM
    (SELECT 
        *,
            WEEKDAY(Dates) + 1 AS Day,
            CASE
                WHEN SUBSTRING(Day_Indicator, WEEKDAY(Dates) + 1, 1) = '1' THEN 1
                ELSE 0
            END AS Flag
    FROM
        Day_Indicator) d
WHERE
    Flag = 1;