select list of date between date range with condition

Question

hi i have a goals want to generate list of date between 2 date but i also have some condition like this :

Table_A : Maximum Delivery Time and Week off

Code      Max_delivery_time      Week_off
 01               4                  6

Note : Maximum_delivery_time it contains the number of day that we can deliver an item week_off it contains the day in every week that deliver can't be process values 1-7 1 = monday, 2 = tuesday etc

Table_B : Table Day off

Code       Date_day_off 
 01         2021-26-05
 02         2021-28-05

Table B contains date day off that must be exclude if the day is on range

so i would like to select list of the date inside the date range with condition : date start must be +1 day from today date finish is (sysdate+1) + "Max_delivery_time". But if the day inside is contains a day in Week_off and date from table b date_day_off it will change to next day sample : Today is : monday, 2021-05-24

the result will be

List_of_day
2021-05-25
2021-05-27
2021-05-30
2021-05-31

is it possible to generate it with a query ?

what is the type of Date_day_off? is it really year day month? — ysth
– ysth, Commented May 24, 2021 at 13:36
2 questions: 1) your sample output just has the four dates for your sample Table_A record, but uses all the dates from Table_B, even the one with a different Code; does Code in Table_A not have anything to do with Code in Table_B? 2) what does the output look like if there are multiple rows in Table_A? — ysth
– ysth, Commented May 24, 2021 at 13:40
Table_A - does it contains 1 row only? 1 = monday, 2 = tuesday etc Why so strange? Why not like for WEEKDAY() function (0 = Monday, 1 = Tuesday, … 6 = Sunday). Table_B - incorrect date literals. the result will be Why '2021-05-31' is skipped? — Akina
– Akina, Commented May 24, 2021 at 13:53
Welcome to SO. Please see: Why should I provide an MCRE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented May 24, 2021 at 13:59

eshirvana · Accepted Answer · 2021-05-24 18:56:38Z

0

one way is to use recursive cte :

with recursive cte as (
    select 
        CURRENT_DATE() List_of_day 
        , Max_delivery_time 
        , Week_off
        , 0  as counter 
        , DAYOFWEEK(CURRENT_DATE()) - 1 DAYno
        , 1 as flag 
    from Table_A ta 
    union all 
    select ADDDATE(List_of_day, 1 ) List_of_day 
        , Max_delivery_time + case when DAYOFWEEK(ADDDATE(List_of_day, 1 )) -1 = Week_off then 1 when b.code is not null then 1  else 0 end  Max_delivery_time 
        , Week_off
        , counter +1 counter
        , DAYOFWEEK(ADDDATE(List_of_day, 1 )) - 1 DAYno
        , case when DAYOFWEEK(ADDDATE(List_of_day, 1 )) - 1 = Week_off then 1 when b.code is not null then 1  else 0 end as flag 
    from cte 
    left join Table_B b on  ADDDATE(List_of_day, 1 ) = b.Date_day_off
    where counter +1 <= Max_delivery_time
)
select List_of_day from cte
where flag = 0

db<>fiddle here

answered May 24, 2021 at 18:56

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Deaaa Over a year ago

this is work in my sql 8.0 and it's correct what i means. but could i use recursive in mysql under 8.0 ? i think it doesn't work :(

eshirvana Over a year ago

@Deaaa mysql as of February 1, 2021 mysql 5.6 is not even supported anymore , I recommend upgrade it to latest version MySQL Product Support EOL Announcements

Collectives™ on Stack Overflow

select list of date between date range with condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related