240 lines
17 KiB
Transact-SQL
240 lines
17 KiB
Transact-SQL
USE [EnVisage]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[sp_BuildRaceMatrix] Script Date: 06/29/2016 12:23:03 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.procedures WHERE Name = N'sp_BuildRaceMatrixTeams')
|
||
DROP PROCEDURE sp_BuildRaceMatrixTeams
|
||
GO
|
||
|
||
-- =============================================
|
||
-- Author: <Author,,Name>
|
||
-- Create date: <Create Date,,>
|
||
-- Description: <Description,,>
|
||
-- =============================================
|
||
CREATE PROCEDURE [dbo].[sp_BuildRaceMatrixTeams]
|
||
|
||
@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,
|
||
ProjectId uniqueidentifier,
|
||
WeekOrdinal int
|
||
)
|
||
|
||
insert into @RequiredCapacityByEC
|
||
select sum(sd.Quantity ) as RequiredCapacity,ExpenditureCategoryId as ExpendtiureCategory ,sd.WeekEndingDate, p.Id, sd.WeekOrdinal 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 ,p.Id,sd.WeekOrdinal
|
||
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..#RequiredCapacityByEC') IS NOT NULL
|
||
drop table #RequiredCapacityByEC
|
||
|
||
|
||
create table #RequiredCapacityByEC (
|
||
RequiredCapacity decimal(18,6),
|
||
ExpenditureCategoryId uniqueidentifier,
|
||
ProjectID uniqueidentifier,
|
||
WeekOrdinal int
|
||
)
|
||
|
||
insert into #RequiredCapacityByEC
|
||
select sum(RequiredCapacity) as RequiredCapacity,ExpenditureCategoryId, ProjectID, WeekOrdinal from @RequiredCapacityByEC where ProjectID=@ProjectId group by ProjectID,ExpenditureCategoryId, WeekOrdinal order by ExpenditureCategoryId,WeekOrdinal
|
||
|
||
|
||
|
||
DECLARE @cols AS NVARCHAR(MAX),
|
||
@query AS NVARCHAR(MAX)
|
||
|
||
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(WeekOrdinal)
|
||
from #RequiredCapacityByEC
|
||
group by WeekOrdinal,ExpenditureCategoryId,ProjectID
|
||
|
||
FOR XML PATH(''), TYPE
|
||
).value('.', 'NVARCHAR(MAX)')
|
||
,1,1,'')
|
||
|
||
set @query = N'SELECT ProjectID,ExpenditureCategoryId,' + @cols + N' from
|
||
(
|
||
select ProjectID,ExpenditureCategoryId,RequiredCapacity, WeekOrdinal
|
||
from #RequiredCapacityByEC
|
||
) 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,
|
||
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.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,
|
||
ExpenditureCategoryId uniqueidentifier,
|
||
WeekEndingDate DateTime,
|
||
MaxHours int,
|
||
isReal bit
|
||
)
|
||
insert into @AvailableCapacityRR
|
||
select SUM(AvailableCapacity) AvailableCapacity,WeekOrdinal , ExpenditureCategoryId,WeekEndingDate,0 ,1 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),
|
||
ExpenditureCategoryId uniqueidentifier,
|
||
isReal bit,
|
||
WeekEndingDate datetime
|
||
)
|
||
|
||
insert into #AvailableCapacityRR
|
||
select (datediff(day,@RaceStartDate,WeekEndingDate)/7 )+1, sum(AvailableCapacity) as AvailableCapacity,ExpenditureCategoryId,isReal,WeekEndingDate from @AvailableCapacity where WeekEndingDate <= @RaceEndDate group by ExpenditureCategoryId,WeekEndingDate,WeekOrdinal,isReal order by ExpenditureCategoryId,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 ExpenditureCategoryId, isReal,' + @cols + N' from
|
||
(
|
||
select distinct ExpenditureCategoryId ,isReal,AvailableCapacity, WeekOrdinal
|
||
from #AvailableCapacityRR
|
||
) x
|
||
pivot
|
||
(
|
||
sum(AvailableCapacity)
|
||
for WeekOrdinal in (' + @cols + N')
|
||
) p '
|
||
|
||
exec sp_executesql @query;end
|