SQL Server Lag () with condition to calculate previous working day-CodePudding

I have looked around StackOverflow but no-one offer the solution I need.

I have a date table

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

and I have to calculate the previous working date for every day:

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   2039-12-20

I spent the whole working day trying to figure it out both in dax and in SQL but I could not.

A solution in dax or in T-Sql would be great!

CodePudding user response：

One method would be to use APPLY to get the previous row:

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);
GO


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;

On SQL Server 2022 you could use LAG and its IGNORE NULLS feature:

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;

Or you could use a gaps an island solution, which works on SQL Server 2012 :

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;

Note all solutions here return NULL for 2039-12-21 as the date 2039-12-20 does not exist in your data, and thus it is unknown if it is a working day or not. I assume your actual (calendar) table has the date in it.

Clean up:

DROP TABLE dbo.YourTable;

db<>fiddle