Find Total Mins Between Multiple Swipe In/Swipe Out Timestamps – Select multiple records from vertical row and transform horizontal row

I am working on a SQL Server query to fetch the Badge Out Time - Bade In Time of the list of users who will be working on multiple task codes.

task_mgmt - tablename

Table Columns as follows

Task_Sn UserName Task_Code Action Badge_IN_OUT_TIME
1 Andy BLOG START 2021-07-20 08:11:45.000
2 Andy BLOG END 2021-07-20 10:11:45.000
3 Becky ACCTS START 2021-07-20 11:11:45.000
4 Becky ACCTS END 2021-07-20 12:11:45.000
5 Andy BLOG START 2021-07-20 12:15:45.000
6 Andy BLOG END 2021-07-20 12:25:45.000
7 Andy BLOG START 2021-07-20 12:25:00.000
8 Becky ACCTS START 2021-07-20 13:00:00.000
9 Becky ACCTS END 2021-07-20 13:30:00.000

I am excepting the result

UserName Task_Code Badge_IN_TIME Badge_OUT_TIME TOTAL_MINS_SPENT
Andy BLOG 2021-07-20 12:25:55.000 135
Becky ACCTS 2021-07-20 13:00:00.000 2021-07-20 13:30:00.000 90

I really couldn't come up with any query for this. I am a beginner in SQL and I know CRUD operations. This seems to be super complex for me.

Select UserName,
       Task_Code,
       Badge_In_Time,
       Badge_Out_Time,
       (Badge_Out_Time - Badge_In_Time) as TOTAL_TIME_SPENT
from task_mgmt
order by Badge_IN_OUT_TIME desc

Since Andy is currently badge-in and not badge-out yet, so the latest badge-out timestamp is being calculated as [ getdate() - badge-in ] as LastTransaction Mins (5 mins)

enter image description here



Read more here: https://stackoverflow.com/questions/68461316/find-total-mins-between-multiple-swipe-in-swipe-out-timestamps-select-multiple

Content Attribution

This content was originally published by JSLover at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: