ClearlyClueless
ClearlyClueless

Reputation: 762

Calculate rolling adjusted date based on date from parent row - lag / recursion

Given the data set below, I need to calculate a rolling AdjustedHireByDate. Each AdjustedHireByDate should be @NumWeeks weeks from the previous row's AdjustedHireByDate (if they have the same parent) unless the PreviousHireDate on the current row is not null. If PreviousHireDate is not null, use the PreviousHireDate to calculate the AdjustedHireByDate for the given row. This all stems from an initial parent record who's HireByDate does not change (unless it has a HireDate of course).

Sql Fiddle

-- INIT database
CREATE TABLE Vacancy 
(
    ParentID INT NOT NULL,
    PreviousID INT NOT NULL,
    CurrentID INT NOT NULL,
    PreviousHireByDate DATE NOT NULL,
    PreviousHireDate DATE NULL
);

INSERT INTO Vacancy (ParentID, PreviousID, CurrentID, PreviousHireByDate, PreviousHireDate) 
VALUES (3752, 3752, 3753, '2024-11-18', NULL),
       (3752, 3753, 3754, '2025-02-24', NULL),
       (3752, 3754, 3973, '2025-06-02', NULL),
       (3752, 3973, 4391, '2025-09-08', NULL),
       (3752, 4391, 4650, '2025-12-15', NULL),
       (3957, 3957, 3958, '2024-10-21', '2024-10-28'),
       (3957, 3958, 3959, '2025-02-02', NULL),
       (3957, 3959, 3960, '2025-06-23', NULL),
       (3957, 3960, 4146, '2025-09-15', '2025-08-04'),
       (3957, 4146, 4623, '2025-12-22', NULL);
ParentID PreviousId CurrentId PreviousHireByDate PreviousHireDate
3752 3752 3753 2024-11-18 NULL
3752 3753 3754 2025-02-24 NULL
3752 3754 3973 2025-06-02 NULL
3752 3973 4391 2025-09-08 NULL
3752 4391 4650 2025-12-15 NULL
3957 3957 3958 2024-10-28 2024-10-28
3957 3958 3959 2025-02-02 NULL
3957 3959 3960 2025-06-23 NULL
3957 3960 4146 2025-09-15 2025-08-04
3957 4146 4623 2025-12-22 NULL

Desired output given the above data set and using @NumWeeks = 2 (note this will usually be 8-24 weeks, but for simpler math/validation using a simpler number so the dates may seem to be getting adjusted more significantly than they will in practice.):

ParentID PreviousId CurrentId PreviousHireByDate PreviousHireDate AdjustedHireByDate
3752 3752 3753 2024-11-18 NULL 2024-12-02
3752 3753 3754 2025-02-24 NULL 2024-12-16
3752 3754 3973 2025-06-02 NULL 2024-12-30
3752 3973 4391 2025-09-08 NULL 2025-01-13
3752 4391 4650 2025-12-15 NULL 2025-01-27
3957 3957 3958 2024-10-21 2024-10-28 2024-11-11
3957 3958 3959 2025-02-02 NULL 2024-11-25
3957 3959 3960 2025-06-23 NULL 2024-12-09
3957 3960 4146 2025-09-15 2025-08-04 2025-08-18
3957 4146 4623 2025-12-22 NULL 2025-09-01

Current select is

DECLARE @NumWeeks SMALLINT = 2;

WITH InitialHireByDate AS 
(
    SELECT 
        *, 
        CASE 
            WHEN ParentID = PreviousID 
                THEN COALESCE(PreviousHireDate, PreviousHireByDate) 
            ELSE NULL
        END AS AdjustedHireByDate 
    FROM 
        Vacancy
),
LaggedHireByDate AS 
(
    SELECT 
        ParentID,
        PreviousID,
        CurrentID,
        PreviousHireDate,
        PreviousHireByDate,
        DATEADD(WEEK, @NumWeeks, 
                COALESCE(PreviousHireDate,   
                         LAG(AdjustedHireByDate) OVER (PARTITION BY ParentID ORDER BY PreviousID ASC)
                        )) AS AdjustedHireByDate
    FROM 
        InitialHireByDate
)
SELECT * 
FROM LaggedHireByDate

Upvotes: 0

Views: 56

Answers (0)

Related Questions