243 lines
8.4 KiB
Transact-SQL
243 lines
8.4 KiB
Transact-SQL
Use [EnVisage]
|
|
|
|
IF EXISTS (select 1 from sys.views where name like 'VW_HolidayAllocation')
|
|
BEGIN
|
|
DROP VIEW VW_HolidayAllocation
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (select 1 from sys.views where name like 'VW_ResourceWorkingDays')
|
|
BEGIN
|
|
DROP VIEW VW_ResourceWorkingDays
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (select 1 from sys.views where name like 'VW_WorkWeek')
|
|
BEGIN
|
|
DROP VIEW VW_WorkWeek
|
|
END
|
|
GO
|
|
|
|
IF EXISTS (select 1 from sys.views where name like 'VW_Holidays2Resources')
|
|
BEGIN
|
|
DROP VIEW VW_Holidays2Resources
|
|
END
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[VW_Holidays2Resources] AS
|
|
-- Holidays for specified resources, Teams and ECs
|
|
(SELECT 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 PeopleResource 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 H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
|
|
FROM Holiday H
|
|
LEFT JOIN PeopleResource PR ON (1 = 1)
|
|
WHERE (H.CompanyImpact = 1) AND (H.WorkingDays = 0)
|
|
|
|
UNION
|
|
|
|
-- Holidays for all resources, except specified resources, teams and ECs
|
|
SELECT H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
|
|
FROM Holiday H
|
|
LEFT JOIN PeopleResource PR ON (1 = 1)
|
|
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
|
|
)
|
|
|
|
EXCEPT
|
|
|
|
SELECT 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 PeopleResource 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
|
|
|
|
CREATE VIEW VW_WorkWeek AS
|
|
SELECT WW.*,
|
|
(CAST(WW.Sunday as decimal) + CAST(WW.Monday as decimal) + CAST(WW.Tuesday as decimal) +
|
|
CAST(WW.Wednesday as decimal) + CAST(WW.Thursday as decimal) + CAST(WW.Friday as decimal) +
|
|
CAST(WW.Saturday as decimal)) AS WorkDaysAmount
|
|
FROM WorkWeek WW
|
|
GO
|
|
|
|
CREATE VIEW VW_ResourceWorkingDays AS
|
|
select A.ResourceId, B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount,
|
|
MIN(CAST((B.Sunday & WW.Sunday) as decimal)) AS Sunday,
|
|
MIN(CAST((B.Monday & WW.Monday) as decimal)) AS Monday,
|
|
MIN(CAST((B.Tuesday & WW.Tuesday) as decimal)) AS Tuesday,
|
|
MIN(CAST((B.Wednesday & WW.Wednesday) as decimal)) AS Wednesday,
|
|
MIN(CAST((B.Thursday & WW.Thursday) as decimal)) AS Thursday,
|
|
MIN(CAST((B.Friday & WW.Friday) as decimal)) AS Friday,
|
|
MIN(CAST((B.Saturday & WW.Saturday) as decimal)) AS Saturday
|
|
from VW_Holidays2Resources A
|
|
INNER JOIN HolidayAllocation B ON (B.HolidayGroupId = A.HolidayGroupId)
|
|
INNER JOIN VW_WorkWeek WW ON (WW.Id = A.WorkWeekId)
|
|
GROUP BY A.ResourceId, B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount
|
|
GO
|
|
|
|
CREATE VIEW VW_HolidayAllocation AS
|
|
SELECT WD.ResourceId AS PeopleResourceId, WD.ExpenditureCategoryId, WD.TeamId, WD.WeekEndingDate,
|
|
(CASE (WD.WorkDaysAmount)
|
|
WHEN 0 THEN 0
|
|
ELSE
|
|
(ISNULL(WD.Sunday + WD.Monday + WD.Tuesday + WD.Wednesday + WD.Thursday + WD.Friday + WD.Saturday, 0) /
|
|
WD.WorkDaysAmount)
|
|
END) AS AdjustmentKoeff
|
|
FROM VW_ResourceWorkingDays WD
|
|
--INNER JOIN ExpenditureCategory EC ON (EC.Id = WD.ExpenditureCategoryId)
|
|
--INNER JOIN UOM ON (UOM.Id = EC.UOMId)
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_Holiday_3' AND
|
|
object_id = OBJECT_ID('Holiday'))
|
|
BEGIN
|
|
DROP INDEX [IX_Holiday_3] ON [dbo].[Holiday]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Holiday_3] ON [dbo].[Holiday]
|
|
(
|
|
[CompanyImpact] ASC,
|
|
[IsInclude] ASC,
|
|
[WorkingDays] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_Holiday2Team' AND
|
|
object_id = OBJECT_ID('Holiday2Team'))
|
|
BEGIN
|
|
DROP INDEX [IX_Holiday2Team] ON [dbo].[Holiday2Team]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Holiday2Team] ON [dbo].[Holiday2Team]
|
|
(
|
|
[HolidayId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_Holiday2Team_1' AND
|
|
object_id = OBJECT_ID('Holiday2Team'))
|
|
BEGIN
|
|
DROP INDEX [IX_Holiday2Team_1] ON [dbo].[Holiday2Team]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Holiday2Team_1] ON [dbo].[Holiday2Team]
|
|
(
|
|
[TeamId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_Holiday2ExpenditureCategory' AND
|
|
object_id = OBJECT_ID('Holiday2ExpenditureCategory'))
|
|
BEGIN
|
|
DROP INDEX [IX_Holiday2ExpenditureCategory] ON [dbo].[Holiday2ExpenditureCategory]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Holiday2ExpenditureCategory] ON [dbo].[Holiday2ExpenditureCategory]
|
|
(
|
|
[HolidayId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_Holiday2ExpenditureCategory_1' AND
|
|
object_id = OBJECT_ID('Holiday2ExpenditureCategory'))
|
|
BEGIN
|
|
DROP INDEX [IX_Holiday2ExpenditureCategory_1] ON [dbo].[Holiday2ExpenditureCategory]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Holiday2ExpenditureCategory_1] ON [dbo].[Holiday2ExpenditureCategory]
|
|
(
|
|
[ExpenditureCategoryId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_Holiday2PeopleResource' AND
|
|
object_id = OBJECT_ID('Holiday2PeopleResource'))
|
|
BEGIN
|
|
DROP INDEX [IX_Holiday2PeopleResource] ON [dbo].[Holiday2PeopleResource]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Holiday2PeopleResource] ON [dbo].[Holiday2PeopleResource]
|
|
(
|
|
[HolidayId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_Holiday2PeopleResource_1' AND
|
|
object_id = OBJECT_ID('Holiday2PeopleResource'))
|
|
BEGIN
|
|
DROP INDEX [IX_Holiday2PeopleResource_1] ON [dbo].[Holiday2PeopleResource]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Holiday2PeopleResource_1] ON [dbo].[Holiday2PeopleResource]
|
|
(
|
|
[ResourceId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_PeopleResource_1' AND
|
|
object_id = OBJECT_ID('PeopleResource'))
|
|
BEGIN
|
|
DROP INDEX [IX_PeopleResource_1] ON [dbo].[PeopleResource]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_PeopleResource_1] ON [dbo].[PeopleResource]
|
|
(
|
|
[TeamId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_PeopleResource_2' AND
|
|
object_id = OBJECT_ID('PeopleResource'))
|
|
BEGIN
|
|
DROP INDEX [IX_PeopleResource_2] ON [dbo].[PeopleResource]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_PeopleResource_2] ON [dbo].[PeopleResource]
|
|
(
|
|
[ExpenditureCategoryId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.indexes
|
|
WHERE name='IX_PeopleResource_3' AND
|
|
object_id = OBJECT_ID('PeopleResource'))
|
|
BEGIN
|
|
DROP INDEX [IX_PeopleResource_3] ON [dbo].[PeopleResource]
|
|
END
|
|
GO
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_PeopleResource_3] ON [dbo].[PeopleResource]
|
|
(
|
|
[WorkWeekId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
GO |