27 lines
1.5 KiB
Transact-SQL
27 lines
1.5 KiB
Transact-SQL
Use EnVisage
|
||
|
||
if exists (select 1 from sys.views where name like 'VW_Holiday')
|
||
BEGIN
|
||
DROP VIEW VW_Holiday
|
||
END
|
||
GO
|
||
|
||
if exists (select 1 from sys.views where name like 'VW_HolidayGroupItems')
|
||
BEGIN
|
||
DROP VIEW VW_HolidayGroupItems
|
||
END
|
||
GO
|
||
|
||
CREATE VIEW VW_HolidayGroupItems AS
|
||
SELECT ROW_NUMBER ()
|
||
OVER (PARTITION BY HolidayGroupId ORDER BY EffectiveChangeDate) AS RN, *
|
||
FROM Holiday
|
||
GO
|
||
|
||
|
||
CREATE VIEW VW_Holiday AS
|
||
SELECT A.Id, A.Name, A.WorkingDays, A.HolidayGroupId, A.EffectiveChangeDate, A.CreatedAt,
|
||
A.Options, A.StartDate, A.EndDate, A.CompanyImpact, A.IsInclude, DATEADD(DAY, -1, B.EffectiveChangeDate) AS ValidTo FROM VW_HolidayGroupItems A
|
||
LEFT JOIN VW_HolidayGroupItems B ON ((B.HolidayGroupId = A.HolidayGroupId) AND (B.RN = A.RN + 1))
|
||
GO
|