Good day all,
I am trying to create a dynamic report to produce the time it take between starting an activity and ending an activity. I can get the information I am looking for by using SQL, but it will not limit the returns based on dates. I have tried a few different ways to do this, but nothing is working for me. Thanks for any help you can provide.
StringBuilder sql = new StringBuilder();
sql.AppendLine(
"Select " +
"WP.name AS change, " +
"WP.state AS status, " +
"Act.name AS activity, " +
"ClosedBy.keyed_name AS closed_by, " +
"Act_Assign.created_on, " +
"Act_Assign.closed_on, " +
"Act_Assign.modified_on, " +
"GETDATE() AS today, " +
"DATEDIFF (d, [Act_Assign].created_on, [Act_Assign].closed_on) + 1 AS date_diff " +
"FROM [Workflow_Process] AS WP " +
"INNER JOIN [Workflow_Process_Activity] AS WPA ON WPA.source_id = WP.id " +
"INNER JOIN [Activity] AS Act ON Act.id = WPA.related_id " +
"INNER JOIN [Activity_Assignment] AS Act_Assign ON Act_Assign.source_id = Act.id " +
"INNER JOIN [sm_Change] as Change ON Change.item_number = WP.name " +
"LEFT OUTER JOIN [User] AS ClosedBy ON ClosedBy.id = Act_Assign.closed_by " +
"WHERE WP.name = 'CR-1091' OR WP.name = 'CR-1169' " + // AND Act.name = 'ECR Approval'
"AND Act_Assign.modified_on BETWEEN '2023-08-01' AND '2023-08-02' " +
"ORDER BY WP.name, Act_Assign.created_on ASC, Act_Assign.closed_on DESC "
);
