Reputation: 762
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).
-- 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