54 lines
1.9 KiB
Transact-SQL
54 lines
1.9 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_Holidays2Resources] Script Date: 3/15/2016 5:50:05 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
ALTER VIEW [dbo].[VW_Holidays2Resources] AS
|
|
-- Holidays for specified resources, Teams and ECs
|
|
(SELECT distinct H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
|
|
FROM Holiday H
|
|
LEFT JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
|
|
LEFT JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
|
|
LEFT JOIN Holiday2PeopleResource H2P ON (H2P.HolidayId = H.Id)
|
|
LEFT JOIN VW_TeamResource PR ON
|
|
(PR.TeamId = H2T.TeamId) OR (PR.ExpenditureCategoryId = H2E.ExpenditureCategoryId) OR (H2P.ResourceId = PR.Id)
|
|
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 1) AND (H.WorkingDays = 0)
|
|
|
|
UNION
|
|
|
|
-- Holidays for all company resources
|
|
SELECT distinct H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
|
|
FROM Holiday H
|
|
LEFT JOIN VW_TeamResource PR ON (1 = 1)
|
|
WHERE (H.CompanyImpact = 1) AND (H.WorkingDays = 0)
|
|
|
|
UNION
|
|
|
|
-- Holidays for all resources, except specified resources, teams and ECs
|
|
SELECT distinct H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
|
|
FROM Holiday H
|
|
LEFT JOIN VW_TeamResource PR ON (1 = 1)
|
|
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
|
|
)
|
|
|
|
EXCEPT
|
|
|
|
SELECT distinct H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
|
|
FROM Holiday H
|
|
LEFT JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
|
|
LEFT JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
|
|
LEFT JOIN Holiday2PeopleResource H2P ON (H2P.HolidayId = H.Id)
|
|
LEFT JOIN VW_TeamResource PR ON
|
|
(PR.TeamId = H2T.TeamId) OR (PR.ExpenditureCategoryId = H2E.ExpenditureCategoryId) OR (H2P.ResourceId = PR.Id)
|
|
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
|
|
|
|
GO
|
|
|
|
|