I have a table that collects clocking transactions
tblTR(PK-int tagcode,Date trDate,Time trTime,In_Out)
INSERT #tblTR
VALUES
(1, '2019-01-01', '09:00:00', 'In'),
(1, '2019-01-01', '12:00:00', 'Out'),
(1, '2019-01-01', '13:00:00', 'In'),
(1, '2019-01-01', '17:00:00', 'Out'),
(2, '2019-01-02', '23:00:00', 'In'),
(2, '2019-01-01', '12:00:00', 'Out'),
(2, '2019-01-01', '13:00:00', 'In'),
(2, '2019-01-01', '17:00:00', 'Out'),
(2, '2019-01-02', '23:00:00', 'In'),
(2, '2019-01-03', '07:00:00', 'Out'),
(1, '2019-01-03', '07:00:00', 'Out');
How do i calculate the hours worked with a procedure or function in MSSQL?
I know i need the start and end dates then convert the time into 24hour, so i can transform or change the minutes into decimals, deduct the lunch hour and calculate the hours worked. A user can clock out and in at any given time.
What I have tried:
Create
FUNCTION [dbo].[fn_GetTotalWorkingDays]
(@DateFrom DATE,
@DateTo DATE
)
RETURNS INT
AS
BEGIN
DECLARE @TotWorkingDays INT= 0;
WHILE @DateFrom <= @DateTo
BEGIN
IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday','Saturday','Sunday')
BEGIN
SET @TotWorkingDays = @TotWorkingDays + 1;
END;
SET @DateFrom = DATEADD(DAY, 1, @DateFrom);
END;
RETURN @TotWorkingDays;
END;
and i am using
[dbo].[fn_GetTotalWorkingHours]
(
@DateFrom Datetime,
@DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
-(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
SET @TotalTimeDiff =
(
SELECT DATEDIFF(SECOND,
(
SELECT CONVERT(TIME, @DateFrom)
),
(
SELECT CONVERT(TIME, @DateTo)
)) / 3600.0
);
RETURN (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
END
but i am having trouble putting it all together...