How to calculate employees working hours in sql

My company business hour is 7:30 to 18 and 13:00 to 15:00 is lunch time. Important part is about lunch time that should not calculate as working time of employee at all.

• So imagine employee start working at 8:30 and exit at 15:00 so the time of 4:30 hours should be calculate for him. actually I want to remove lunch time from attendance time of employee in different situations:

My fields are all in Time(7) format in Sql.

Answer

This is one approach in SQL Server. It fundamentally splits each person’s shift into two – Pre-Lunch and Post-Lunch. When a shift goes into (or past) lunch, it uses the lunch times as the bounds instead.

I’ve also written it in bits (e.g., CTE, sub-query, etc) so you can run those individually to see what they do. It is likely you’ll need to update it for your own database structures etc.

-- Data setup

CREATE TABLE #WorkLog (WorkDate date, StartTime time, EndTime time, StartLunch time, EndLunch time)

INSERT INTO #WorkLog (WorkDate, StartTime, EndTime, StartLunch, EndLunch) VALUES
('2020-09-01', '07:30', '18:00', '13:00', '15:00'),
('2020-09-02', '12:00', '15:00', '13:00', '15:00'),
('2020-09-03', '15:00', '18:00', '13:00', '15:00'),
('2020-09-04', '08:30', '15:00', '13:00', '15:00')

SELECT * FROM #WorkLog

------

-- Find times worked

; WITH PreLunchTimes AS
        (SELECT WorkDate,
                StartTime AS StartTime,
                CASE WHEN EndTime < StartLunch THEN EndTime ELSE StartLunch END AS EndTime
        FROM    #WorkLog
        WHERE   StartTime < StartLunch
        ),
    PostLunchTimes AS
        (SELECT WorkDate,
                CASE WHEN StartTime > EndLunch THEN StartTime ELSE EndLunch END AS StartTime,
                EndTime AS EndTime
        FROM    #WorkLog
        WHERE   EndTime > EndLunch
        )
SELECT  WorkDate, SUM(Elapsed_Mins) AS Total_Work_Mins, CAST(DATEADD(minute, SUM(Elapsed_Mins), 0) AS time) AS Total_work_time
FROM   (SELECT  WorkDate, DATEDIFF(minute, StartTime, EndTime) AS Elapsed_Mins
        FROM    PreLunchTimes
            UNION ALL
        SELECT  WorkDate, DATEDIFF(minute, StartTime, EndTime) AS Elapsed_Mins
        FROM    PostLunchTimes
        ) AS A
GROUP BY WorkDate
ORDER BY WorkDate

Here’s a db<>fiddle

Issues:

• If you have shifts go past midnight, you’ll need to add in appropriate code to deal with that.
• If all lunchtimes are 13:00 to 15:00, then you can just set these as variables (e.g., @LunchStart and @LunchEnd) rather than storing them in the data.