Query:
SELECT
count(distinct Activity_ID) as dueactivity
FROM
(
select
RA.Regulation_ID,
RA.Activity_ID,
RA.Second_Owner,
RA.Third_Owner,
RA.Frequency_ID,
RM.Compliance_Type_ID,
RA.First_Owner,
CASE
WHEN CA.Due_Date = [synprod].[Udf_CAS_CalFutureDuedt1](F.[datepart],
F.[Days],
RA.Test_Date) THEN synprod.Udf_CAS_CalDuedt1(F.[datepart],
F.[Days],
CA.Due_Date,
RA.Test_Date)
ELSE synprod.Udf_CAS_CalFutureDuedt1(F.[datepart],
F.[Days],
RA.Test_Date)
END AS Due_Date,
synprod.Udf_CAS_CalRAGNoOfDays(CASE
WHEN CA.Due_Date = synprod.Udf_CAS_CalFutureDuedt1(F.[datepart],
F.[Days],
RA.Test_Date) THEN synprod.Udf_CAS_CalDuedt1(F.[datepart],
F.[Days],
CA.Due_Date,
RA.Test_Date)
ELSE synprod.Udf_CAS_CalFutureDuedt1(F.[datepart],
F.[Days],
RA.Test_Date)
END) AS RAG
FROM
synprod.MST_CAS_Compliance_Master RA
INNER JOIN
synprod.MST_Cas_RegulationMaster RM
ON RA.Regulation_ID = RM.Regulation_ID
INNER JOIN
synprod.MST_CAS_Frequency_Testing F
ON F.Frequency_ID = RA.Frequency_ID
LEFT JOIN
synprod.Vw_CAS_Transdata CA
ON CA.Regulation_ID = RA.Regulation_ID
AND CA.Activity_ID = RA.Activity_ID
INNER JOIN
synprod.MST_Cas_TypeOfCompliance TC
ON TC.Compliance_ID = RM.Compliance_Type_ID
WHERE
RA.Active_Status = 1
AND RM.Active_Status = 1
) D
WHERE
Due_Date NOT IN (
SELECT
synprod.CAS_Compliance_Assurance_Trans.Due_Date
FROM
synprod.CAS_Compliance_Assurance_Trans
WHERE
synprod.CAS_Compliance_Assurance_Trans.Regulation_ID = D.Regulation_ID
AND synprod.CAS_Compliance_Assurance_Trans.Activity_ID = D.Activity_ID
AND synprod.CAS_Compliance_Assurance_Trans.Active_Status = 1
)
and ( RAG >= 0 and (D.RAG <= 15) )
Scalar value function:
ALTER FUNCTION [synprod].[Udf_CAS_CalDuedt1]
(
@chrInterval VARCHAR(11),
@intIncrement INT,
@sdtDate SMALLDATETIME,
@sdttest_date SMALLDATETIME
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @NewDate DATETIME
DECLARE @Day varchar(10)
-- Add the T-SQL statements to compute the return value here
SELECT @NewDate = CASE
WHEN @chrInterval IN ('year', 'yy', 'yyyy') THEN DATEADD(YEAR, @intIncrement, @sdtDate)
WHEN @chrInterval IN ('quarter', 'qq', 'q') THEN DATEADD(QUARTER, @intIncrement, @sdtDate)
WHEN @chrInterval IN ('month', 'mm', 'm') THEN DATEADD(MONTH, @intIncrement, @sdtDate)
WHEN @chrInterval IN ('dayofyear', 'dy', '') THEN DATEADD(DAYOFYEAR, @intIncrement, @sdtDate)
WHEN @chrInterval IN ('day', 'dd', 'd') THEN DATEADD(DAY, @intIncrement, @sdtDate)
WHEN @chrInterval IN ('week', 'wk', 'ww') THEN DATEADD(WEEK, @intIncrement, @sdtDate)
WHEN @chrInterval IN ('weekday', 'dw', 'w') THEN DATEADD(WEEKDAY, @intIncrement, @sdtDate)
END
-- If testing date is 31
if DATEPART(DD, @sdttest_date)='31'
BEGIN
SELECT @NewDate =DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@NewDate)+1,0))
END
else
SET @NewDate=@NewDate
RETURN @NewDate
END
Scalar value function:
ALTER FUNCTION [synprod].[Udf_CAS_CalRAGNoOfDays]
( -- Add the parameters for the function here
@sdtDate SMALLDATETIME
)
RETURNS CHAR(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @RAGDays CHAR(10)
SELECT @RAGDays= DATEDIFF(DAY,GETDATE(),@sdtDate)
-- Return the result of the function
RETURN @RAGDays
END
Scalar value function:
ALTER FUNCTION [synprod].[Udf_CAS_CalFutureDuedt1]
(
-- Add the parameters for the function here
@chrInterval VARCHAR(11),
@intIncrement INT,
@sdtDate SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @FutureDuedt smalldatetime
DECLARE @NewDuedt smalldatetime
DECLARE @RAGDays char(10)
SELECT @RAGDays= DATEDIFF(DAY,GETDATE(),@sdtDate)
DECLARE @CountDays INT
DECLARE @autoIncrement INT
SET @autoIncrement=1
IF @chrInterval='wk' and @intIncrement=1
SET @CountDays=10
ELSE
SET @CountDays=15
SET @NewDuedt = @sdtDate
WHILE (@RAGDays<=@CountDays)
BEGIN
SET @NewDuedt=@FutureDuedt
SET @FutureDuedt=[synprod].[Udf_CAS_CalDuedt1](@chrInterval,@intIncrement*@autoIncrement, @sdtDate,@sdtDate)
SET @RAGDays= DATEDIFF(DAY,GETDATE(),@FutureDuedt)
SET @autoIncrement=@autoIncrement+1
END;
-- Return the result of the function
IF @NewDuedt is null
SET @NewDuedt=@sdtDate
RETURN @NewDuedt
END