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 " );