I have a SQL table that has rows of populated data from: punched in times, tasks completed during the day, followed by punched out time. I need come up with a PHP MSSQL query to get the elapsed time between each row. Preferably using DATEDIFF(second, [Date Completed], [Date Completed]) / 3600.0 AS DiffDate.
How do I get it to retain the Date Completed from the first result so that I can use it in the next result, to obtain the elapsed time between the two dates?
My query looks like the following:
SELECT PDT_Mill_Capacity_Labor_Detail.[id],
users.alias,
PDT_Mill_Capacity_Labor_Detail.[Order Number],
PDT_Mill_Capacity_Labor_Detail.[Job Name],
PDT_Mill_Capacity_Labor_Detail.[Door Number],
PDT_Mill_Capacity_Labor_Detail.[Qty Display],
PDT_Mill_Capacity_Labor_Detail.[Door Counter],
PDT_Mill_Capacity_Labor_Detail.[Jamb Counter],
PDT_Mill_Capacity_Labor_Detail.[Work Center],
PDT_Mill_Capacity_Labor_Detail.[Completed By],
PDT_Mill_Capacity_Labor_Detail.[Date Completed],
PDT_Mill_Capacity_Labor_Detail.[Hours Earned],
PDT_Mill_Capacity_Labor_Detail.[Elapsed Time],
PDT_Mill_Capacity_Labor_Detail.[Applied Time],
PDT_Mill_Capacity_Labor_Detail.[ID]
FROM users INNER JOIN PDT_Mill_Capacity_Labor_Detail
ON users.[Employee Number] = PDT_Mill_Capacity_Labor_Detail.[Completed By]
WHERE [Completed By]='081'
AND ([Date Completed] >= '10/21/2015 00:00:00'
AND [Date Completed] <= '10/21/2015 23:59:59')
GROUP BY
PDT_Mill_Capacity_Labor_Detail.[id], users.alias,
PDT_Mill_Capacity_Labor_Detail.[Order Number],
PDT_Mill_Capacity_Labor_Detail.[Job Name],
PDT_Mill_Capacity_Labor_Detail.[Door Number],
PDT_Mill_Capacity_Labor_Detail.[Qty Display],
PDT_Mill_Capacity_Labor_Detail.[Door Counter],
PDT_Mill_Capacity_Labor_Detail.[Jamb Counter],
PDT_Mill_Capacity_Labor_Detail.[Work Center],
PDT_Mill_Capacity_Labor_Detail.[Completed By],
PDT_Mill_Capacity_Labor_Detail.[Date Completed],
PDT_Mill_Capacity_Labor_Detail.[Hours Earned],
PDT_Mill_Capacity_Labor_Detail.[Elapsed Time],
PDT_Mill_Capacity_Labor_Detail.[Applied Time],
PDT_Mill_Capacity_Labor_Detail.[ID]
ORDER BY [Completed By], [Date Completed]
The results looks like the following ...
Work Station | Date Completed | Elapsed Time | ID
---------------------------------------------------------------
Punched In | 2015-10-21 05:58:00.000 | 0 | 6816
Task1 | 2015-10-21 11:27:11.000 | 0 | 6212
Task2 | 2015-10-21 11:27:33.000 | 0 | 6219
Task3 | 2015-10-21 11:28:22.000 | 0 | 6651
Punched Out | 2015-10-21 14:42:00.000 | 0 | 7318
After a decent nights sleep I managed to rewrite the process to calculate the elapsed time between each task using the following query and managed to get the results needed ...
SELECT b.[id], a.[Completed By],
a.[Date Completed] AS newDateCompleted,
b.[Date Completed] AS oldDateCompleted,
DATEDIFF(second, a.[Date Completed],b.[Date Completed]) / 3600.0 AS diff
FROM(
SELECT id, [Completed By], [Date Completed],
(
SELECT count(*)
FROM PDT_Mill_Capacity_Labor_Detail
WHERE [Completed By]=T.[Completed By] AND [Date Completed]<T.[Date Completed]
)+1
AS rank1
FROM PDT_Mill_Capacity_Labor_Detail
AS T
)
AS a INNER JOIN
(
SELECT id, [Completed By], [Date Completed],
(
SELECT count(*)
FROM PDT_Mill_Capacity_Labor_Detail
WHERE [Completed By]=T1.[Completed By] AND [Date Completed]<T1.[Date Completed]
)
AS rank2
FROM PDT_Mill_Capacity_Labor_Detail
AS T1
)
AS b ON (a.rank1=b.rank2) AND (a.[Completed By] = b.[Completed By])
WHERE a.[Completed By]='084' AND (a.[Date Completed] >='11/4/2015' AND a.[Date Completed] <='11/4/2015 23:59:59')
ORDER BY a.[Completed By], a.[Date Completed] ASC