73 lines
5.6 KiB
Transact-SQL
73 lines
5.6 KiB
Transact-SQL
Use [EnVisage]
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_ActualCapacityByTeamsAdjusted')
|
||
BEGIN
|
||
DROP VIEW VW_ActualCapacityByTeamsAdjusted
|
||
PRINT 'VW_ActualCapacityByTeamsAdjusted dropped'
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_ActualCapacityAdjusted')
|
||
BEGIN
|
||
DROP VIEW VW_ActualCapacityAdjusted
|
||
PRINT 'VW_ActualCapacityAdjusted dropped'
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_ActualCapacityByTeamsDirect')
|
||
BEGIN
|
||
DROP VIEW VW_ActualCapacityByTeamsDirect
|
||
PRINT 'VW_ActualCapacityByTeamsDirect dropped'
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.views WHERE Name = N'VW_ActualCapacityDirect')
|
||
BEGIN
|
||
DROP VIEW VW_ActualCapacityDirect
|
||
PRINT 'VW_ActualCapacityDirect dropped'
|
||
END
|
||
GO
|
||
|
||
CREATE VIEW [dbo].[VW_ActualCapacityDirect] AS
|
||
SELECT TR.Id as PeopleResourceId, TR.TeamId, TR.ExpenditureCategoryId, FC.EndDate as WeekEndingDate,
|
||
UOM.UOMValue as Quantity, ISNULL(UOM.UOMValue * RTE.Rate, 0) as Cost, ISNULL(RTE.Rate, 0) as Rate
|
||
FROM VW_TeamResource TR
|
||
INNER JOIN ExpenditureCategory EC ON (EC.Id = TR.ExpenditureCategoryId)
|
||
INNER JOIN UOM UOM ON (UOM.Id = EC.UOMId)
|
||
INNER JOIN FiscalCalendar FC ON (FC.EndDate > TR.TeamStartDate) AND ((TR.TeamEndDate IS NULL) OR (FC.EndDate <= TR.TeamEndDate))
|
||
LEFT JOIN Rate RTE ON (RTE.ExpenditureCategoryId = EC.Id) AND (FC.EndDate >= RTE.StartDate) AND (FC.EndDate <= RTE.EndDate)
|
||
AND (RTE.Type = 0) AND (RTE.ParentId = RTE.ExpenditureCategoryId)
|
||
WHERE (FC.Type = 0) AND (FC.AdjustingPeriod = 0)
|
||
GO
|
||
|
||
CREATE VIEW [dbo].[VW_ActualCapacityByTeamsDirect] AS
|
||
SELECT TeamId, ExpenditureCategoryId, WeekEndingDate,
|
||
SUM(Quantity) as Quantity, SUM(Cost) as Cost,
|
||
COUNT(1) as Resources
|
||
FROM VW_ActualCapacityDirect
|
||
GROUP BY TeamId, ExpenditureCategoryId, WeekEndingDate
|
||
GO
|
||
|
||
CREATE VIEW [dbo].[VW_ActualCapacityAdjusted] AS
|
||
SELECT ACD.PeopleResourceId, ACD.TeamId, ACD.ExpenditureCategoryId, ACD.WeekEndingDate,
|
||
ACD.Quantity as QuantityDirect, ACD.Cost as CostDirect,
|
||
(ACD.Quantity * ISNULL(HA.AdjustmentKoeff, 1)) as QuantityAdjusted,
|
||
(ACD.Cost * ISNULL(HA.AdjustmentKoeff, 1)) as CostAdjusted,
|
||
ISNULL(HA.AdjustmentKoeff, 1) as AdjustmentKoeff
|
||
FROM VW_ActualCapacityDirect ACD
|
||
LEFT JOIN VW_HolidayAllocation HA ON (HA.PeopleResourceId = ACD.PeopleResourceId) AND
|
||
(HA.ExpenditureCategoryId = ACD.ExpenditureCategoryId) AND
|
||
(HA.TeamId = ACD.TeamId) AND (HA.WeekEndingDate = ACD.WeekEndingDate)
|
||
GO
|
||
|
||
CREATE VIEW [dbo].[VW_ActualCapacityByTeamsAdjusted] AS
|
||
SELECT TeamId, ExpenditureCategoryId, WeekEndingDate,
|
||
SUM(QuantityDirect) as QuantityDirect, SUM(CostDirect) as CostDirect,
|
||
SUM(QuantityAdjusted) as QuantityAdjusted, SUM(CostAdjusted) as CostAdjusted,
|
||
COUNT(1) as ResourcesDirect, SUM(AdjustmentKoeff) as ResourcesAdjusted
|
||
FROM VW_ActualCapacityAdjusted
|
||
GROUP BY TeamId, ExpenditureCategoryId, WeekEndingDate
|
||
GO
|
||
|
||
|