TimeClock SQL View

Difficulty Level

WARNING: I AM NOT A SQL EXPERT

This is probably rather sloppy and could be done better if I knew enough about SQL, but here goes…

I’ve been writing an inventory program that every employee will be using all day and logging in and out multiple times a day. I have the program record all the logins and most logouts. No, I wont explain why it’s most logouts, It doesn’t matter why for this to work. The table that records logins and outs has 5 Columns, ID; Username; DataType; LogDate and LogTime. What I want to do is have a SQL view that grabs the first Login of the day and the last Logout of the day for every User, then add up the total hours Worked and Poof! Built in TimeClock.

Here is an example of the Table:

Here is the SQL statement I used to create the View:

_{Create View vw_TimeClock AS
SELECT Username, LogDate as ‘Date’, min(LogTime) AS ‘IN’, max(LogTime) As ‘OUT’,
(SELECT CONCAT_WS(‘:’, (SELECT ((DateDiff(MINUTE, ‘1970-01-01’, max(LogTime)) – DATEDIFF(MINUTE, ‘1970-01-01’, min(LogTime))) % (86400)) % (3600) / 60 ),
(SELECT ((DateDiff(MINUTE, ‘1970-01-01’, max(LogTime)) – DateDiff(MINUTE, ‘1970-01-01’, min(LogTime))) % (60))))) as Hours
FROM WorkRecords Group by username, LogDate}

Here is what I get when I open up the View that I created.

You are then able to write a SQL statement that pulls this view and only displays a specific users hours, or all hours worked for a specific day or whatever you want.

I have written 1000’s of SQL statements over the years, and that isn’t an exaggeration, but this one gave me a really hard time. Getting the first login and last logout of the day as well as a column that had the hours and a column that had the minutes was really easy and took me maybe 2 minutes. Combining the Hours and Minutes columns and adding a colon gave me hell and took about 3 hours. I could have kept them separate and written the function in c# to display it how I want it, but I knew it could be done in a SQL statement and it gave me a chance to learn something new. I couldn’t find an example of this anywhere online, so I decided to post this. I hope it saves somebody 3 hours of their lives.