238 lines
12 KiB
Transact-SQL
238 lines
12 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
ALTER FUNCTION [dbo].[resourceAvailability_f](@StartDate datetime, @EndDate datetime, @Per char(1), @AvailableHours float, @DisregardNPTime bit)
|
|
|
|
RETURNS @restable TABLE
|
|
(
|
|
FirstName nvarchar(1000) ,
|
|
LastName nvarchar(1000) ,
|
|
ExpenditureCategory nvarchar(800) ,
|
|
AvailableHours float ,
|
|
AllocatedHours float ,
|
|
NonProjectTimeHours float ,
|
|
ResourceAvailability float ,
|
|
TeamName nvarchar(400) ,
|
|
TeamId varchar(36) ,
|
|
ExpenditureCategoryId varchar(36)
|
|
)
|
|
AS
|
|
BEGIN
|
|
declare @FCStartDate datetime
|
|
declare @FCEndDate datetime
|
|
|
|
select
|
|
@FCStartDate = MIN(EndDate) ,
|
|
@FCEndDate = MAX(EndDate)
|
|
from dbo.FiscalCalendar
|
|
where Type = 0
|
|
and (StartDate <= @StartDate or StartDate <= @EndDate)
|
|
and (EndDate >= @StartDate or EndDate >= @EndDate)
|
|
and AdjustingPeriod = 0
|
|
and NonWorking = 0
|
|
|
|
if (@Per = 1)
|
|
begin
|
|
INSERT INTO @restable
|
|
select vw.FirstName, vw.LastName, vw.Name as ExpenditureCategory,
|
|
sum(vw.AvailableHours) as AvailableHours, sum(vw.AllocatedHours) as AllocatedHours,
|
|
sum(vw.NonProjectTimeHours ) as NonProjectTimeHours,
|
|
sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours) as ResourceAvailability,
|
|
vw.TeamName, vw.teamId, vw.expId
|
|
From(
|
|
select
|
|
pr.Id,
|
|
ec2.Id as expId,
|
|
t.Id as teamId,
|
|
pr.FirstName,
|
|
pr.LastName,
|
|
ec2.Name,
|
|
t.Name as TeamName,
|
|
fc.EndDate,
|
|
|
|
((select UOM.UOMValue
|
|
from UOM, ExpenditureCategory ec
|
|
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId)
|
|
) as AvailableHours,
|
|
|
|
ISNULL((select SUM (pa.Quantity)
|
|
From PeopleResourceAllocation pa
|
|
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
|
Where pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and pa.WeekEndingDate = fc.EndDate), 0) as AllocatedHours,
|
|
|
|
ISNULL((select SUM (pt.HoursOff)
|
|
From NonProjectTimeAllocation pt
|
|
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate = fc.EndDate), 0) as NonProjectTimeHours
|
|
|
|
from FiscalCalendar fc
|
|
join VW_TeamResource pr on fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) --(not(fc.StartDate > pr.EndDate) and not(fc.EndDate < pr.StartDate))
|
|
join ExpenditureCategory ec2 on ec2.Id=pr.ExpenditureCategoryId
|
|
join Team t on t.Id = pr.TeamId
|
|
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0 and
|
|
not pr.Id in (
|
|
select sub.Id from
|
|
(select r.Id,
|
|
isnull(((select sum(isnull(UOM.UOMValue, 0)) -- * count(fc2.Id)
|
|
from FiscalCalendar fc2
|
|
left join VW_TeamResource pr on pr.TeamStartDate <= fc2.StartDate
|
|
and (pr.TeamEndDate is null or pr.TeamEndDate >= fc2.EndDate) and pr.Id = r.Id-- in( '43E77FE8-8986-4B4B-A076-49E9ECA35946','B4CB1413-9F4B-4ACB-BF94-DD3696F36444') --= r.Id
|
|
left join ExpenditureCategory ec on ec.Id=pr.ExpenditureCategoryId
|
|
left join PeopleResource pr1 on pr1.Id = pr.Id
|
|
left join UOM on UOM.Id=ec.UOMId
|
|
where fc2.EndDate >= min(fc.EndDate) and fc2.EndDate <= max(fc.EndDate))
|
|
-
|
|
ISNULL((select SUM (isnull(pa.Quantity, 0))
|
|
From FiscalCalendar fc2
|
|
left join VW_TeamResource pr on pr.TeamStartDate <= fc2.StartDate
|
|
and (pr.TeamEndDate is null or pr.TeamEndDate >= fc2.EndDate) and pr.Id = r.Id
|
|
left join PeopleResource pr1 on pr1.Id = pr.Id
|
|
left join PeopleResourceAllocation pa on pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and
|
|
pa.WeekEndingDate <= fc2.StartDate and pa.WeekEndingDate >= fc2.EndDate and pr.Id = r.Id
|
|
left join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
|
where fc2.EndDate >= min(fc.EndDate) and fc2.EndDate <= max(fc.EndDate)), 0)
|
|
-
|
|
case when @DisregardNPTime = 0 then
|
|
ISNULL((select SUM (isnull(pt.HoursOff, 0))
|
|
From FiscalCalendar fc2
|
|
left join VW_TeamResource pr on pr.TeamStartDate <= fc2.StartDate
|
|
and (pr.TeamEndDate is null or pr.TeamEndDate >= fc2.EndDate) and pr.Id = r.Id
|
|
left join NonProjectTimeAllocation pt on pt.PeopleResourceId=pr.Id
|
|
and pt.WeekEndingDate >= min(fc.EndDate) and pt.WeekEndingDate <= max(fc.EndDate)
|
|
where fc2.EndDate <= fc2.StartDate and fc2.EndDate >= fc2.EndDate
|
|
), 0) else 0 end
|
|
), 0) as AvailableHours
|
|
|
|
from FiscalCalendar fc, PeopleResource r
|
|
where
|
|
fc.EndDate >= @FCStartDate
|
|
and fc.EndDate <= @FCEndDate
|
|
group by datepart(MONTH,fc.EndDate), datepart(YEAR,fc.EndDate), r.Id
|
|
)sub where sub.AvailableHours < @AvailableHours
|
|
)
|
|
) as vw
|
|
group by vw.Id, vw.expId, vw.teamId, vw.FirstName, vw.LastName, vw.Name, vw.TeamName--, vw.EndDate
|
|
end
|
|
else if(@Per= 2)
|
|
begin
|
|
INSERT INTO @restable
|
|
|
|
select
|
|
vw.FirstName, vw.LastName, vw.Name as ExpenditureCategory,
|
|
sum(vw.AvailableHours) as AvailableHours, sum(vw.AllocatedHours) as AllocatedHours,
|
|
sum(vw.NonProjectTimeHours ) as NonProjectTimeHours,
|
|
sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours) as ResourceAvailability,
|
|
vw.TeamName, vw.teamId, vw.expId
|
|
From(
|
|
select
|
|
pr.Id,
|
|
ec2.Id as expId,
|
|
t.Id as teamId,
|
|
pr.FirstName,
|
|
pr.LastName,
|
|
ec2.Name,
|
|
t.Name as TeamName,
|
|
fc.EndDate,
|
|
|
|
((select UOM.UOMValue
|
|
from UOM, ExpenditureCategory ec
|
|
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId)
|
|
) as AvailableHours,
|
|
|
|
ISNULL((select SUM (pa.Quantity)
|
|
From PeopleResourceAllocation pa
|
|
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
|
Where pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and pa.WeekEndingDate = fc.EndDate), 0) as AllocatedHours,
|
|
|
|
ISNULL((select SUM (pt.HoursOff)
|
|
From NonProjectTimeAllocation pt
|
|
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate = fc.EndDate), 0) as NonProjectTimeHours
|
|
|
|
from FiscalCalendar fc
|
|
join VW_TeamResource pr on fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) --(not(fc.StartDate > pr.EndDate) and not(fc.EndDate < pr.StartDate))
|
|
join ExpenditureCategory ec2 on ec2.Id=pr.ExpenditureCategoryId
|
|
join Team t on t.Id = pr.TeamId
|
|
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0 and
|
|
not pr.Id in (
|
|
select sub.Id from
|
|
(select
|
|
r.Id,
|
|
fc.EndDate,
|
|
isnull(((select UOM.UOMValue
|
|
from UOM, ExpenditureCategory ec, PeopleResource pr1
|
|
join VW_TeamResource pr on fc.StartDate >=pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) and r.Id = pr.Id
|
|
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId and pr1.Id = pr.Id)
|
|
-
|
|
ISNULL((select SUM (pa.Quantity)
|
|
From PeopleResourceAllocation pa
|
|
join VW_TeamResource pr on fc.StartDate >=pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) and r.Id = pr.Id
|
|
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
|
Where pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and pa.WeekEndingDate = fc.EndDate), 0)
|
|
-
|
|
case when @DisregardNPTime = 0 then
|
|
ISNULL((select SUM (pt.HoursOff)
|
|
From NonProjectTimeAllocation pt
|
|
join VW_TeamResource pr on fc.StartDate >=pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) and r.Id = pr.Id
|
|
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate =fc.EndDate), 0) else 0 end --as TrainingHours,
|
|
), 0) as AvailableHours
|
|
|
|
from FiscalCalendar fc, PeopleResource r
|
|
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0
|
|
group by fc.EndDate, fc.StartDate, r.Id--, pr.ExpenditureCategoryId, pr.TeamId
|
|
)sub where sub.AvailableHours < @AvailableHours
|
|
)
|
|
) as vw
|
|
group by vw.Id, vw.expId, vw.teamId, vw.FirstName, vw.LastName, vw.Name, vw.TeamName--, vw.EndDate
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO @restable
|
|
|
|
select
|
|
vw.FirstName, vw.LastName, vw.Name as ExpenditureCategory,
|
|
sum(vw.AvailableHours) as AvailableHours, sum(vw.AllocatedHours) as AllocatedHours,
|
|
sum(vw.NonProjectTimeHours ) as NonProjectTimeHours,
|
|
sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours ) as ResourceAvailability,
|
|
vw.TeamName, vw.teamId, vw.expId
|
|
|
|
From(
|
|
|
|
select
|
|
pr.Id,
|
|
ec2.Id as expId,
|
|
t.Id as teamId,
|
|
pr.FirstName,
|
|
pr.LastName,
|
|
ec2.Name,
|
|
t.Name as TeamName,
|
|
fc.EndDate,
|
|
|
|
((select UOM.UOMValue
|
|
from UOM, ExpenditureCategory ec
|
|
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId)
|
|
) as AvailableHours,
|
|
|
|
ISNULL((select SUM (pa.Quantity)
|
|
From PeopleResourceAllocation pa
|
|
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
|
|
Where pa.PeopleResourceId=pr.Id and pa.TeamId = pr.TeamId and pa.WeekEndingDate = fc.EndDate), 0) as AllocatedHours,
|
|
|
|
ISNULL((select SUM (pt.HoursOff)
|
|
From NonProjectTimeAllocation pt
|
|
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate = fc.EndDate), 0) as NonProjectTimeHours
|
|
|
|
from FiscalCalendar fc
|
|
join VW_TeamResource pr on fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate) --(not(fc.StartDate > pr.EndDate) and not(fc.EndDate < pr.StartDate))
|
|
join ExpenditureCategory ec2 on ec2.Id=pr.ExpenditureCategoryId
|
|
join Team t on t.Id = pr.TeamId
|
|
where fc.EndDate >= @FCStartDate and fc.EndDate <= @FCEndDate and fc.Type = 0
|
|
) as vw
|
|
group by vw.Id, vw.expId, vw.teamId, vw.FirstName, vw.LastName, vw.Name, vw.TeamName, vw.AvailableHours--, vw.EndDate
|
|
having
|
|
((@DisregardNPTime = 0 and ISNULL(@AvailableHours, -1000000) <= (sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.NonProjectTimeHours ))) or
|
|
(@DisregardNPTime = 1 and ISNULL(@AvailableHours, -1000000) <= (sum(vw.AvailableHours) - sum(vw.AllocatedHours))))
|
|
end
|
|
RETURN
|
|
END
|
|
|
|
GO
|