40 lines
3.0 KiB
Transact-SQL
40 lines
3.0 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
if exists (select 1 from sys.views where name like 'VW_HolidayAllocation')
|
|
begin
|
|
DROP VIEW [dbo].[VW_HolidayAllocation]
|
|
END
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE VIEW [dbo].[VW_HolidayAllocation]
|
|
AS
|
|
SELECT H.Name as HolidayName, PR.Id as PeopleResourceId, PR.ExpenditureCategoryId, PR.TeamId, HA.Id, HA.HolidayId, HA.HolidayGroupId, HA.WeekEndingDate,
|
|
--cast (Wk.Sunday as decimal)+cast (Wk.Monday as decimal)+cast (Wk.Tuesday as decimal)+cast (Wk.Wednesday as decimal)+cast (Wk.Thursday as decimal)+cast (Wk.Friday as decimal)+cast (Wk.Saturday as decimal) as PRWorkDays,
|
|
--cast (Wk.Sunday & HA.Sunday as decimal)+cast (Wk.Monday & HA.Monday as decimal)+cast (Wk.Tuesday & HA.Tuesday as decimal)+cast (Wk.Wednesday & HA.Wednesday as decimal)+cast (Wk.Thursday & HA.Thursday as decimal)+cast (Wk.Friday & HA.Friday as decimal)+cast (Wk.Saturday & HA.Saturday as decimal) as PRAdjustedWorkDays,
|
|
ISNULL (case (cast (Wk.Sunday as decimal)+cast (Wk.Monday as decimal)+cast (Wk.Tuesday as decimal)+cast (Wk.Wednesday as decimal)+cast (Wk.Thursday as decimal)+cast (Wk.Friday as decimal)+cast (Wk.Saturday as decimal))
|
|
when 0 then 0
|
|
else (cast (Wk.Sunday & HA.Sunday as decimal)+cast (Wk.Monday & HA.Monday as decimal)+cast (Wk.Tuesday & HA.Tuesday as decimal)+cast (Wk.Wednesday & HA.Wednesday as decimal)+cast (Wk.Thursday & HA.Thursday as decimal)+cast (Wk.Friday & HA.Friday as decimal)+cast (Wk.Saturday & HA.Saturday as decimal)) / (cast (Wk.Sunday as decimal)+cast (Wk.Monday as decimal)+cast (Wk.Tuesday as decimal)+cast (Wk.Wednesday as decimal)+cast (Wk.Thursday as decimal)+cast (Wk.Friday as decimal)+cast (Wk.Saturday as decimal))
|
|
end, 0) as AdjustmentKoeff
|
|
FROM HolidayAllocation AS HA
|
|
inner join Holiday H on HA.HolidayId = H.Id
|
|
left join Holiday2Team H2T on H.CompanyImpact = 0 and H.Id = H2T.HolidayId
|
|
left join Holiday2PeopleResource H2PR on H.CompanyImpact = 0 and H.Id = H2PR.HolidayId
|
|
left join Holiday2ExpenditureCategory H2E on H.CompanyImpact = 0 and H.Id = H2E.HolidayId
|
|
left join PeopleResource PR on
|
|
(H.CompanyImpact = 1) -- all resources in case Company Impact is set to 'All Resources'
|
|
OR (H.CompanyImpact = 0 AND H.IsInclude = 1 AND (PR.Id = H2PR.ResourceId OR PR.TeamId = H2T.TeamId OR PR.ExpenditureCategoryId = H2E.ExpenditureCategoryId)) -- resources from 'Resources' dropdown and resources of teams from 'Teams' dropdown and resources of expenditures from 'Expenditures' dropdown if company impact is 'Some Resources' and Include mode
|
|
OR (H.CompanyImpact = 0 AND H.IsInclude = 0 AND PR.Id <> H2PR.ResourceId AND PR.TeamId <> H2T.TeamId AND PR.ExpenditureCategoryId <> H2E.ExpenditureCategoryId) -- resources from 'Resources' dropdown and resources of teams from 'Teams' dropdown and resources of expenditures from 'Expenditures' dropdown if company impact is 'Some Resources' and Exclude mode
|
|
left join ExpenditureCategory EC on PR.ExpenditureCategoryId = EC.Id
|
|
left join UOM on UOM.Id = EC.UOMId
|
|
left join WorkWeek Wk on Wk.Id = PR.WorkWeekId
|
|
where H.WorkingDays = 0
|
|
GO
|