Как создать столбец ValidTo в SQL (тип SCD 2)

Question

У меня есть эта задача: Для этой задачи используйте следующие таблицы в предоставленном наборе данных:  [Employee] (содержит имя и дату начала работы для каждой должности всех сотрудников, бизнес-ключ однозначно идентифицирует каждого сотрудника)  [Position] (содержит имя уровня для каждой позиции) Создайте оператор SQL, который возвращает следующие столбцы

   [EmployeeKey]
   [EmployeeBK]
   [Name]
   [Level]
   [ValidFrom]
   [ValidTo]

Столбец [ValidTo] должен соответствовать следующим правилам: 1) Значение должно быть за день до даты начала следующей позиции (без наложения, без "дырок" в истории позиций) 2) Последняя позиция должна считаться открытой и иметь дата окончания по умолчанию

'2222-12-31' (YYYY-MM-DD)

Дополнительный вопрос: определите проблемы качества данных в наборе данных

[dbo].[Employee](
    [EmployeeKey] [int] NOT NULL,
    [EmployeeBK] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [ValidFrom] [date] NULL
)

[dbo].[Position](
    [EmployeeKey] [int] NOT NULL,
    [Level] [varchar](9) NULL
)

Некоторые записи:

1. EmployeeKey EmployeeBK Name Level ValidFrom 
     15 2 Kayla Level 1 2013-01-01 
     54 2 Kayla Level 2 2013-03-15 
     63 2 Kayla Level 3 2013-04-19 
     81 2 Kayla Level 4 2013-07-28 
     116 2 Kayla Level 5 2014-05-31 
     143 2 Kayla Level 6 2015-01-15 
     171 2 Kayla Level 7 2016-03-25 
     193 2 Kayla Level 8 2016-05-31 
     194 2 Kayla Level 9 2016-05-31 
     11 3 Hudson Level 1 2013-01-01 
     38 3 Hudson Level 2 2013-01-30 
     49 3 Hudson Level 3 2013-02-20 
     80 3 Hudson Level 4 2013-07-26 
     91 3 Hudson Level 5 2013-11-05 
     101 3 Hudson Level 6 2013-12-28 
    105 3 Hudson Level 7 2014-03-24

---

Заранее спасибо!

Answer 1

with ValidDate as
(select e.EmployeeKey, LEAD(e.ValidFrom, 1, '2222-12-31') over (partition by e.EmployeeBK order by p.[Level])   as ValidTo from 
dbo.Employee e
left join dbo.Position p on p.EmployeeKey = e.EmployeeKey)

select  distinct e.EmployeeKey
      , e.EmployeeBK
      , e.Name
      , p.[Level]
      , e.ValidFrom
      , dateadd(day, -1, v.ValidTo) as ValidTo
      --, v.ValidTo as ValidTest

from dbo.Employee e
join dbo.Position p on p.EmployeeKey = e.EmployeeKey

join ValidDate v on v.EmployeeKey = e.EmployeeKey

group by e.EmployeeKey, e.EmployeeBK, e.Name, p.[Level], e.ValidFrom, v.ValidTo
order by e.EmployeeBK