Sample SQL Queries Used to Generate Project and Workflow Request Reports

AdminStudio 2025 | 29.0 | Reports

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'