251 lines
8.1 KiB
Transact-SQL
251 lines
8.1 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_BuildRaceMatrix] Script Date: 07/07/2016 6:08:35 PM ******/
|
|
DROP PROCEDURE [dbo].[sp_BuildRaceMatrix]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[sp_BuildRaceMatrix] Script Date: 07/07/2016 6:08:35 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF EXISTS(SELECT * FROM sys.procedures WHERE Name = N'sp_BuildRaceMatrix')
|
|
DROP PROCEDURE sp_BuildRaceMatrix
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_BuildRaceMatrix]
|
|
|
|
@TeamID uniqueidentifier,
|
|
@RaceStartDate datetime,
|
|
@RaceEndDate datetime,
|
|
@threshold int
|
|
|
|
as
|
|
begin
|
|
|
|
declare @Projects table(
|
|
Idx int NOT NULL identity(1,1),
|
|
Id uniqueidentifier,
|
|
StartDate datetime,
|
|
DurationInWeeks int,
|
|
[Priority] int,
|
|
Probability int,
|
|
deadline datetime,
|
|
RaceOrder int,
|
|
RaceStartDate datetime,
|
|
Score2 decimal(18,4),
|
|
EndDate datetime,
|
|
isPinned bit ,
|
|
Name nvarchar(200),
|
|
DeadLineMultiple decimal(4,2)
|
|
)
|
|
declare @MaxDeadline datetime
|
|
|
|
insert into @Projects
|
|
select p.Id, s.StartDate,s.Duration,p.[Priority] ,p.Probability*100,isnull(p.Deadline,@RaceEndDate),0,s.StartDate,(p.Probability/p.[Priority])*100,s.EndDate, CAST(CASE WHEN s.StartDate < getdate() THEN 1 ELSE 0 END AS bit),
|
|
p.Name, dbo.fn_GetProjectScoreWeight( @RaceStartDate,@RaceEndDate,isnull(p.Deadline,DateAdd(day,s.Duration*7,@RaceEndDate)),s.Duration)
|
|
from Project p
|
|
join Scenario s on s.ParentId=p.Id and s.Type=2 and s.Status=1
|
|
join Team2Project t2p on t2p.ProjectId=p.Id
|
|
where t2p.TeamId=@TeamID
|
|
|
|
|
|
|
|
update @Projects set isPinned =1, DeadLineMultiple=1.0 where DeadLineMultiple = 0.0
|
|
update @Projects set Score2=Score2*DeadLineMultiple+(DurationInWeeks/100)
|
|
|
|
select @MaxDeadline=Max(deadline) from @Projects
|
|
declare @MaxDuration int=0
|
|
select @MaxDuration=Max(DurationInWeeks) from @Projects
|
|
|
|
select * from @Projects
|
|
|
|
--team wide totals by EC not seperated by project
|
|
declare @RequiredCapacityByEC table(
|
|
RequiredCapacity decimal(18,6),
|
|
ExpenditureCategoryId uniqueidentifier,
|
|
WeekendingDate datetime
|
|
)
|
|
|
|
insert into @RequiredCapacityByEC
|
|
select sum(sd.Quantity ) as RequiredCapacity,ExpenditureCategoryId as ExpendtiureCategory ,sd.WeekEndingDate from ScenarioDetail sd
|
|
join Scenario s on s.Id=sd.ParentID and Type=2
|
|
join @Projects p on p.Id = s.ParentId
|
|
group by sd.ExpenditureCategoryId ,sd.WeekEndingDate
|
|
order by sd.WeekEndingDate
|
|
|
|
|
|
|
|
--project level actuals by Resource
|
|
declare @RequiredCapacityByRS table (
|
|
RequiredCapacity decimal(18,6),
|
|
PeopleResourceID uniqueidentifier,
|
|
ExpenditureCategoryId uniqueidentifier,
|
|
WeekEndingDate DateTime,
|
|
ProjectID uniqueidentifier,
|
|
WeekOrdinal int
|
|
)
|
|
|
|
insert into @RequiredCapacityByRS
|
|
select distinct u.UOMValue as RequiredCapacity,pra.PeopleResourceID, pra.ExpenditureCategoryId as ExpendtiureCategory,pra.WeekEndingDate , p.Id as ProjectId, sd.WeekOrdinal from PeopleResourceAllocation pra
|
|
join Scenario s on s.Id=pra.ScenarioID
|
|
join @Projects p on p.Id = s.ParentId
|
|
join ScenarioDetail sd on sd.ParentId=s.Id and sd.WeekEndingDate =pra.WeekEndingDate and sd.ExpenditureCategoryId=pra.ExpenditureCategoryId
|
|
join ExpenditureCategory ec on ec.Id= sd.ExpenditureCategoryId
|
|
join UOM u on u.Id=ec.UOMId
|
|
|
|
group by pra.ExpenditureCategoryId, pra.WeekEndingDate,p.Id,PeopleResourceID,WeekOrdinal,u.UOMValue
|
|
order by p.Id,pra.WeekEndingDate
|
|
|
|
|
|
|
|
update r set r.RequiredCapacity=ec.RequiredCapacity from @RequiredCapacityByRS r
|
|
join @RequiredCapacityByEC ec on ec.ExpenditureCategoryId = r.ExpenditureCategoryId and ec.WeekendingDate= r.WeekEndingDate
|
|
where ec.RequiredCapacity < r.RequiredCapacity
|
|
|
|
|
|
declare @ProjectId uniqueidentifier
|
|
declare @I int=0
|
|
declare @IMax int
|
|
select @IMax=max(Idx) from @Projects
|
|
|
|
while(@I <=@IMax)
|
|
begin
|
|
|
|
|
|
select @ProjectID =Id from @Projects where Idx=@I
|
|
|
|
IF OBJECT_ID('tempdb..#RequiredCapacityByRS') IS NOT NULL
|
|
drop table #RequiredCapacityByRS
|
|
|
|
|
|
create table #RequiredCapacityByRS (
|
|
RequiredCapacity decimal(18,6),
|
|
PeopleResourceID uniqueidentifier,
|
|
ProjectID uniqueidentifier,
|
|
WeekOrdinal int
|
|
)
|
|
|
|
insert into #RequiredCapacityByRS
|
|
select sum(RequiredCapacity) as RequiredCapacity,PeopleResourceID, ProjectID, WeekOrdinal from @RequiredCapacityByRS where ProjectID=@ProjectId group by ProjectID,PeopleResourceID, WeekOrdinal order by PeopleResourceID,WeekOrdinal
|
|
|
|
|
|
|
|
DECLARE @cols AS NVARCHAR(MAX),
|
|
@query AS NVARCHAR(MAX)
|
|
|
|
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(WeekOrdinal)
|
|
from #RequiredCapacityByRS
|
|
group by WeekOrdinal,PeopleResourceID,ProjectID
|
|
|
|
FOR XML PATH(''), TYPE
|
|
).value('.', 'NVARCHAR(MAX)')
|
|
,1,1,'')
|
|
|
|
set @query = N'SELECT ProjectID,PeopleResourceID,' + @cols + N' from
|
|
(
|
|
select ProjectID,PeopleResourceID,RequiredCapacity, WeekOrdinal
|
|
from #RequiredCapacityByRS
|
|
) x
|
|
pivot
|
|
(
|
|
max( RequiredCapacity)
|
|
for WeekOrdinal in (' + @cols + N')
|
|
) p '
|
|
|
|
exec sp_executesql @query;
|
|
set @I=@I+1
|
|
end
|
|
|
|
select @RaceEndDate=DateAdd(day,@MaxDuration*7,@RaceEndDate);
|
|
|
|
declare @AvailableCapacity table (
|
|
AvailableCapacity decimal(18,6),
|
|
WeekOrdinal int,
|
|
PeopleResourceId uniqueidentifier,
|
|
ExpenditureCategoryId uniqueidentifier,
|
|
WeekEndingDate DateTime,
|
|
MaxHours int,
|
|
isReal bit
|
|
)
|
|
insert into @AvailableCapacity
|
|
select MAX (isnull(cap.QuantityAdjusted, 0)) - SUM(isnull(npa.HoursOff, 0)) as AvailableCapacity,fc.PeriodInt, pr.Id, pr.ExpenditureCategoryId, fc.EndDate, cap.QuantityDirect as MaxHours,1
|
|
from FiscalCalendar fc
|
|
left join VW_TeamResource pr on pr.TeamId = @TeamID and fc.StartDate >= pr.TeamStartDate and (pr.TeamEndDate is null or fc.EndDate <= pr.TeamEndDate)
|
|
left join VW_ActualCapacityAdjusted cap on cap.PeopleResourceId = pr.Id and fc.EndDate = cap.WeekEndingDate and cap.TeamId = @TeamID
|
|
left join VW_NonProjectTimeAllocation npa on pr.Id=npa.PeopleResourceID and npa.TeamId = @TeamID and npa.WeekEndingDate = fc.EndDate
|
|
where fc.EndDate>= @RaceStartDate and fc.EndDate <= @RaceEndDate and fc.EndDate <= @MaxDeadline and fc.Type=0
|
|
group by pr.Id, pr.ExpenditureCategoryId, fc.EndDate, cap.QuantityDirect, fc.PeriodInt
|
|
|
|
declare @AvailableCapacityRR table (
|
|
AvailableCapacity decimal(18,6),
|
|
WeekOrdinal int,
|
|
PeopleResourceId uniqueidentifier,
|
|
ExpenditureCategoryId uniqueidentifier,
|
|
WeekEndingDate DateTime,
|
|
MaxHours int,
|
|
isReal bit
|
|
)
|
|
insert into @AvailableCapacityRR
|
|
select SUM(AvailableCapacity) AvailableCapacity,WeekOrdinal ,NewID(), ExpenditureCategoryId,WeekEndingDate,0 ,0 from @AvailableCapacity GROUP BY ExpenditureCategoryId,WeekEndingDate,WeekOrdinal order by WeekEndingDate
|
|
|
|
|
|
|
|
insert into @AvailableCapacity
|
|
select * from @AvailableCapacityRR
|
|
|
|
|
|
|
|
|
|
IF OBJECT_ID('tempdb..#AvailableCapacityRR') IS NOT NULL
|
|
drop table #AvailableCapacityRR
|
|
--project level actuals by Resource
|
|
|
|
create table #AvailableCapacityRR (
|
|
WeekOrdinal int ,
|
|
AvailableCapacity decimal(18,6),
|
|
PeopleResourceId uniqueidentifier,
|
|
isReal bit,
|
|
WeekEndingDate datetime
|
|
)
|
|
|
|
insert into #AvailableCapacityRR
|
|
select (datediff(day,@RaceStartDate,WeekEndingDate)/7 )+1, sum(AvailableCapacity) as AvailableCapacity,PeopleResourceID,isReal,WeekEndingDate from @AvailableCapacity where WeekEndingDate <= @RaceEndDate group by PeopleResourceID,WeekEndingDate,WeekOrdinal,isReal order by PeopleResourceID,WeekEndingDate
|
|
declare @coltbl table(
|
|
Weekordinal int
|
|
)
|
|
insert into @coltbl
|
|
select distinct WeekOrdinal from #AvailableCapacityRR
|
|
|
|
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(WeekOrdinal)
|
|
from @coltbl
|
|
FOR XML PATH(''), TYPE
|
|
).value('.', 'NVARCHAR(MAX)')
|
|
,1,1,'')
|
|
select @cols as ColumnNames
|
|
set @query = N'SELECT PeopleResourceID, isReal,' + @cols + N' from
|
|
(
|
|
select distinct PeopleResourceID ,isReal,AvailableCapacity, WeekOrdinal
|
|
from #AvailableCapacityRR
|
|
) x
|
|
pivot
|
|
(
|
|
sum(AvailableCapacity)
|
|
for WeekOrdinal in (' + @cols + N')
|
|
) p '
|
|
|
|
exec sp_executesql @query;
|
|
end
|
|
|
|
GO
|
|
|
|
|