SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT *
INTO dbo.YourTable
FROM (VALUES(CONVERT(date,'2039-12-28'),1),
            (CONVERT(date,'2039-12-27'),1),
            (CONVERT(date,'2039-12-26'),0),
            (CONVERT(date,'2039-12-25'),0),
            (CONVERT(date,'2039-12-24'),0),
            (CONVERT(date,'2039-12-23'),1),
            (CONVERT(date,'2039-12-22'),1),
            (CONVERT(date,'2039-12-21'),1))V(TheDate,ItalyWorkingDay);
8 rows affected

SELECT YT.TheDate,
       YT.ItalyWorkingDay,
       PD.TheDate AS PrevItWorkDay
FROM dbo.YourTable YT
     OUTER APPLY (SELECT TOP (1) oa.TheDate
                  FROM dbo.YourTable oa
                  WHERE oa.TheDate < YT.TheDate
                    AND oa.ItalyWorkingDay = 1
                  ORDER BY oa.TheDate DESC) PD
ORDER BY YT.TheDate DESC;


TheDate
ItalyWorkingDay
PrevItWorkDay


2039-12-28
1
2039-12-27

2039-12-27
1
2039-12-23

2039-12-26
0
2039-12-23

2039-12-25
0
2039-12-23

2039-12-24
0
2039-12-23

2039-12-23
1
2039-12-22

2039-12-22
1
2039-12-21

2039-12-21
1
null


--This is a 2022+ solution so can't be run on DB Fiddle (yet)
SELECT YT.TheDate,
       YT.ItalyWorkingDay,
       LAG(CASE YT.ItalyWorkingDay WHEN 1 THEN YT.TheDate END) IGNORE NULLS OVER (ORDER BY YT.TheDate) AS PrevItWorkDay
FROM dbo.YourTable YT
ORDER BY YT.TheDate DESC;
Msg 10753 Level 15 State 1 Line 4
The function 'LAG' must have an OVER clause.

WITH Grps AS(
    SELECT YT.TheDate,
           YT.ItalyWorkingDay,
           SUM(ItalyWorkingDay) OVER (ORDER BY TheDate
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM dbo.YourTable YT),
WorkingDates AS(
    SELECT G.TheDate,
           G.ItalyWorkingDay,
           MAX(CASE G.ItalyWorkingDay WHEN 1 THEN G.TheDate END) OVER (PARTITION BY G.Grp) AS WorkingDate
    FROM Grps G)
SELECT WD.TheDate,
       WD.ItalyWorkingDay,
       LAG(WD.WorkingDate) OVER (ORDER BY WD.TheDate) AS PrevItWorkDay
FROM WorkingDates WD;


TheDate
ItalyWorkingDay
PrevItWorkDay


2039-12-21
1
null

2039-12-22
1
2039-12-21

2039-12-23
1
2039-12-22

2039-12-24
0
2039-12-23

2039-12-25
0
2039-12-23

2039-12-26
0
2039-12-23

2039-12-27
1
2039-12-23

2039-12-28
1
2039-12-27


Warning: Null value is eliminated by an aggregate or other SET operation.


DROP TABLE dbo.YourTable;

TheDate	ItalyWorkingDay	PrevItWorkDay
2039-12-28	1	2039-12-27
2039-12-27	1	2039-12-23
2039-12-26	0	2039-12-23
2039-12-25	0	2039-12-23
2039-12-24	0	2039-12-23
2039-12-23	1	2039-12-22
2039-12-22	1	2039-12-21
2039-12-21	1	null