Employee Productivity calculation in JRS

Total no of hours completed hours/allocated *100 against owner

Actual Effort: current date – creation date where resolved date is null

Planned Effort: (current date – creation date where resolved date is null) + (resolved date – creation date where resolved date is not null)    

JRS QUERY:

SELECT DISTINCT T1.PROJECT_NAME,

        T2.FULL_NAME,

        SUM(CAST(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END AS INT)) as Actual_Effort,

              SUM(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END) +    SUM(CASE WHEN (T1.RESOLVED_DATE IS NOT NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,T1.RESOLVED_DATE) ELSE NULL END) AS PLANNED_EFFORT,

           (CAST(SUM(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END) AS DECIMAL(4,2)) / CAST(SUM(CASE WHEN (T1.RESOLVED_DATE IS NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,GETDATE()) ELSE NULL END) +    SUM(CASE WHEN (T1.RESOLVED_DATE IS NOT NULL) THEN DATEDIFF(DAY,T1.CREATION_DATE,T1.RESOLVED_DATE) ELSE NULL END) AS DECIMAL(4,2)))*100 AS EMPLOYEE_PRODUCTIVITY

FROM RIDW.VW_REQUEST T1

LEFT OUTER JOIN RIDW.VW_RESOURCE T2

ON T2.RESOURCE_ID=T1.OWNER_ID

WHERE T1.PROJECT_ID = 0  AND

(  (T1.REQUEST_STATE = 'Accepted' OR T1.REQUEST_STATE = 'Approved' OR T1.REQUEST_STATE = 'Deferred' OR T1.REQUEST_STATE = 'Deployed' OR T1.REQUEST_STATE = 'Design' OR T1.REQUEST_STATE = 'Development' OR T1.REQUEST_STATE = 'Duplicated' OR T1.REQUEST_STATE = 'In Progress' OR T1.REQUEST_STATE = 'Mitigated' OR T1.REQUEST_STATE = 'New' OR T1.REQUEST_STATE = 'Open' OR T1.REQUEST_STATE = 'Planning' OR T1.REQUEST_STATE = 'Proposed' OR T1.REQUEST_STATE = 'Ready For Deployment' OR T1.REQUEST_STATE = 'Rejected' OR T1.REQUEST_STATE = 'Reopened' OR T1.REQUEST_STATE = 'Requirement Analysis' OR T1.REQUEST_STATE = 'Requirement Gathering' OR T1.REQUEST_STATE = 'Review' OR T1.REQUEST_STATE = 'SQA Review 1' OR T1.REQUEST_STATE = 'SQA Review 2' OR T1.REQUEST_STATE = 'SQA Review 3' OR T1.REQUEST_STATE = 'SQA Review 4' OR T1.REQUEST_STATE = 'SQA Review 5' OR T1.REQUEST_STATE = 'Testing' OR T1.REQUEST_STATE = 'To Be Validated' OR T1.REQUEST_STATE = 'Triaged' OR T1.REQUEST_STATE = 'Unconfirmed' OR T1.REQUEST_STATE = 'Verified' )

) AND

(T1.ISSOFTDELETED = 0) AND

(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) GROUP BY T1.PROJECT_NAME,

        T2.FULL_NAME

P.S Error Snapshot is also attached for reference.

Thanks!