Sample SQL Queries Used to Generate Project and Workflow Request Reports

AdminStudio 2023 | 25.0 | Reports

Edition:This feature is available in Workflow Manager only.

The following queries are used to generate the built-in Project and Workflow Requests reports. These sample queries might be helpful to refer to when you are creating your own custom reports.

Note:Note that DateTimeHelper.GetUniversalDateTime() is used in some of the queries for demonstration purposes only, it is not valid SQL syntax.

Projects Completed On-Time

The following is a sample query to generate data on projects that were completed on time.

SELECT ApplicationID FROM AMS_Application A, AMS_ApplicationStatus AST WHERE A.AppStatusID= AST.StatusID AND AST.IsActive = 1 AND A.StatusSummary =90 AND A.DueDate >= A.ApplicationEndDate AND A.ContractID = 'd135b5ae-8ac0-42b4-a5bc-e105c11b5e13'

Projects Completed Late

The following is a sample query to generate data on projects that were completed late.

SELECT ApplicationID FROM AMS_Application A, AMS_ApplicationStatus AST WHERE A.AppStatusID= AST.StatusID AND AST.IsActive = 1 AND A.StatusSummary =90 AND A.DueDate < A.ApplicationEndDate AND A.ContractID = 'd135b5ae-8ac0-42b4-a5bc-e105c11b5e13'

On Time Workflow Requests

The following is a sample query to generate data on workflow requests that were completed on time.

GetOnTimeActiveApplications

SELECT ApplicationID FROM AMS_Application A, AMS_ApplicationStatus AST WHERE A.AppStatusID = AST.StatusID AND AST.IsActive = 1 AND A.StatusSummary <> 90 AND DateTimeHelper.GetUniversalDateTime() < A.RiskDate AND A.ContractID = 'd135b5ae-8ac0-42b4-a5bc-e105c11b5e13'

At Risk Workflow Requests

The following is a sample query to generate data on workflow requests that at risk of being completed late.

GetAtRiskApplicationCount

SELECT Count(*) FROM AMS_Application WHERE StatusSummary <> 90 AND DateTimeHelper.GetUniversalDateTime() > RiskDate AND DateTimeHelper.GetUniversalDateTime() < DueDate AND ContractID = 'd135b5ae-8ac0-42b4-a5bc-e105c11b5e13'