Need Help in Calculating Working Hours

Question

The way i designed my working sheet doesn't help me to calculate working hours easily.

The output in snapshot has been gathered from multiple tables.

Don't worry regarding setDate and timeEntered formatting.

SetDate represents working day. tsTypeTitle represents type of shift, is it lunch time, etc. timeEntered represents the actual time.

setDate will have to be trimmed to have only Date and timeEntered should only show time - This will be handled later, don't worry about it.

I need to grab the difference between Shift Started and Shift Ended so i can calculate the wage.

Here is my query in case you want to look at it:

SELECT TimeSheet.setDate, TimeSheetType.tsTypeTitle, TimeSheetDetail.timeEntered
FROM TimeSheet 
    INNER JOIN TimeSheetDetail 
        ON TimeSheet.timeSheetID = TimeSheetDetail.timeSheetID
    INNER JOIN TimeSheetType 
        ON TimeSheetType.timeSheetTypeID = TimeSheetDetail.timeSheetTypeID

Answer 1

Assuming everyone has the same 4 events (shift start/end, lunch start/end) every day, you could do something like this. Obviously I've made assumptions for your TimeSheetType ID values. Substitute the appropriate IDs in your version.

SELECT t.TimeSheetID, DATEDIFF(HOUR, tsd1.timeEntered, tsd4.timeEntered) - DATEDIFF(HOUR, tsd2.timeEntered, tsd3.timeEntered) as WorkingHours
    FROM TimeSheet t
        INNER JOIN TimeSheetDetail tsd1
            ON t.timeSheetID = tsd1.timeSheetID
                and tsd1.timeSheetTypeID = 1 /* Shift Started */
        INNER JOIN TimeSheetDetail tsd2
            ON t.timeSheetID = tsd2.timeSheetID
                and tsd2.timeSheetTypeID = 2 /* Lunch Started */
        INNER JOIN TimeSheetDetail tsd3
            ON t.timeSheetID = tsd3.timeSheetID
                and tsd3.timeSheetTypeID = 3 /* Lunch Ended */
        INNER JOIN TimeSheetDetail tsd4
            ON t.timeSheetID = tsd4.timeSheetID
                and tsd4.timeSheetTypeID = 4 /* Shift Ended */

Answer 2

If you must do this in the database, I suggest writing a stored procedure to calculate the hours worked. Otherwise, I think it's wiser to handle this kind of business logic outside of the database, like in the application connecting to your database.

Looking at the design you provided, you seem to be missing a unique identifier for a user or person that corresponds to the time sheet data being stored. A unique identifier is necessary, even if you are presently only storing data for a single user/person. If you ever need to store data for more users/people, having a unique identifier will make this possible.

Answer 3

Timesheet detail tables will normally include a person ID. I guess that this has been omitted, for simplicity's sake.

Assuming that for any given day there will always be one and only one event for each of Shift Started, Lunch Started, Lunch Ended and Shift Ended (in that order), that lunch should be excluded from total hours and that there are no other event types, try:

with
(SELECT TimeSheet.setDate, 
       TimeSheetType.tsTypeTitle, 
       TimeSheetDetail.timeEntered,
       convert(datetime,convert(varchar(10),TimeSheetDetail.timeEntered,112),112) dayEntered
FROM TimeSheet 
    INNER JOIN TimeSheetDetail 
        ON TimeSheet.timeSheetID = TimeSheetDetail.timeSheetID
    INNER JOIN TimeSheetType 
        ON TimeSheetType.timeSheetTypeID = TimeSheetDetail.timeSheetTypeID)
as TS
select dayEntered, 
       datediff(ss,max(case when tsTypeTitle = 'Shift Started' then timeEntered end),
                   max(case when tsTypeTitle = 'Shift Ended' then timeEntered end)) -
       datediff(ss,max(case when tsTypeTitle = 'Lunch Started' then timeEntered end),
                   max(case when tsTypeTitle = 'Lunch Ended' then timeEntered end)) daysSeconds
from TS
group by dayEntered