This is the first time I've ever posted in StackOverflow. I've used it for years and it's been a reliable source, but I think I've finally encountered a complex enough issue worth posting about.
Here's what I have.
We need to calculate the time difference between the Completion Date of one Task, and the Completion Date of a completely separate Task on the same file. The tasks are listed in the same table but have different ID's and Names. However, the Log Table that tracks the Start/Complete dates has columns for ID, Name, TaskStart and CompletedDate.
I can get the TIMEDIFF on a single Task, but I'm struggling with how to call out the tasks by their respective ID's in the SELECT Statement so I can get the correct TIMEDIFF.
, [Time to Complete hh:mm:ss] = (SELECT CAST((FSL.CompletedDate-FSL.TaskStart) as TIME(0)))
Yields Screenshot
Here's what I'm struggling to figure out:
Example:
Order Entry Task | ID: 1 | TaskStart: 2023-08-01 12:00:00.000 | CompletedDate: 2023-08-02 10:14:52.235 | GFNo: 23-1234567
Title Work Complete Task | ID:50 | TaskStart: 2023-08-02 08:05:42.235 | CompletedDate: 2023-08-02 15:31:18.153 | GFNo: 23-1234567
Need to know the Time Difference between ID:1 Completed Date and ID:50 Completed Date in the SELECT Statement.
Would appreciate any help on this! Ya'll are the best!
I've tried the TIMEDIFF/DATEDIFF Calculations, but couldn't find any guidance on how to incorporate WHERE conditions into the calculation to specify by specific item ID.
