mysql - how to shift back the date by a certain number of days v2

Question

I have the following problem: I have a table in which in the first column there are dates, in the second information whether a day is a working day or not (1 or 0) and in the third, the number of working days by which the value from the first column should be shift back. Anyone have maybe think how to get this fourth column?

The table looks something like this:

date	workday	days back
10.01.2021	1	1
10.01.2021	1	2
10.01.2021	1	3
10.01.2021	1	4
11.01.2021	0	1
11.01.2021	0	2
11.01.2021	0	3
11.01.2021	0	4
12.01.2021	1	1
12.01.2021	1	2
12.01.2021	1	3
12.01.2021	1	4

Answer 1

You either need every working day in your table or you need a separate table with a every day and whether it is a working day or not.

I'm not familiar with mysql, the below is my attempt to translate something that works in MS SQL Server, I'm hoping it will work in mysql.

Below I've assumed your only table is called Table1 and it has every working day in and is the table from your question.

First you need to create a function

CREATE FUNCTION [OffsetDate] 
(
    @StartDate DATE NULL,
    @OffsetDays INT = 0
)
RETURNS DATE
AS
BEGIN
    DECLARE @Result DATE

    SET @Result = @StartDate

    BEGIN
        SELECT @Result = distinctDates.Date
        FROM (SELECT DISTINCT Table1.Date
        FROM Table1
        WHERE Table1.Date <= @StartDate
        AND Table1.WorkDay = 1) AS distinctDates
        ORDER BY distinctDates.Date DESC
        LIMIT 1 OFFSET @OffsetDays
    END

    RETURN @Result
END

Then use this in a query to get your 4th column:

SELECT Table1.Date, Table1.WorkDay, Table1.[Days Back], OffsetDate(Table1.Date, Table1.[Days Back]) AS StartDate
FROM Table1