USE [EnVisage]
GO
/****** Object:  UserDefinedFunction [dbo].[GetProjectScoreWeight]    Script Date: 06/28/2016 5:40:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[fn_GetProjectScoreWeight]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
  DROP FUNCTION [dbo].[fn_GetProjectScoreWeight]
GO


create FUNCTION [dbo].[fn_GetProjectScoreWeight] (@SW datetime,@EW datetime,@DL datetime,@D int)
RETURNS decimal(3,2)
AS
BEGIN
	 declare @M decimal(3,2)
	 declare @MD int
	 declare @DO INT
	 declare @W int
	 
	 select @MD =DATEDIFF(week, @SW,@DL);
     select  @W=@MD/4 -- break duration into quarters

	set @DO = DATEDIFF(week, @SW,@DL);
 
	if @DO <=  @D
		set @M=0.0;
	else IF @DO <= @W+@D --and  @DO <  (2*@W)+@D
		set @M=1
	else IF @DO <= (2*@W)+@D --and  @DO < (3*@W)+@D
		set @M=0.9
	else IF @DO <= (3*@W)+@D --and @DO < (4*@W)+@D
		set @M=0.8
	else 
		set @M=0.7 


	 return @M 
END