How to calculate timing hours and mints from datetime column

Feroz Khan

2y852SQL

Dear All,

Can anyone support me i have one colunm which datetime and another column like emp id and i need to calculate there emp total hours and mints date wise from below.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prasad Raveendran2y ago

take a look at the below code and see whether this would works for you

-- Drop table if it exists
DROP TABLE IF EXISTS EmployeeCheckTimes;

-- Create table
CREATE TABLE EmployeeCheckTimes (
    CHECKTIME DATETIME,
    BADGENUMBER INT
);

-- Insert data
INSERT INTO EmployeeCheckTimes (CHECKTIME, BADGENUMBER)
VALUES
    ('2024-05-01 00:00:09.000', 3001025),
    ('2024-05-02 00:09:10.000', 3001025),
    ('2024-05-01 00:00:09.000', 900511),
    ('2024-05-01 00:12:08.000', 900511),
    ('2024-05-01 00:00:21.000', 700677),
    ('2024-05-01 00:00:32.000', 700618),
    ('2024-05-01 00:00:45.000', 700447),
    ('2024-05-01 00:00:49.000', 101355),
    ('2024-05-01 00:00:52.000', 101534),
    ('2024-05-01 00:10:02.000', 101534),
    ('2024-05-01 00:01:09.000', 100066);

-- Create index for performance improvement
CREATE INDEX idx_badgenumber_checktime ON EmployeeCheckTimes (BADGENUMBER, CHECKTIME);

-- Select query with CTE
WITH CheckTimes AS (
    SELECT
        BADGENUMBER,
        MIN(CHECKTIME) AS MIN_CHECKTIME,
        MAX(CHECKTIME) AS MAX_CHECKTIME
    FROM EmployeeCheckTimes
    GROUP BY BADGENUMBER
)
SELECT 
    BADGENUMBER,
    MIN_CHECKTIME,
    MAX_CHECKTIME,
    DATEDIFF(HOUR, MIN_CHECKTIME, MAX_CHECKTIME) AS [Hours],
    DATEDIFF(MINUTE, MIN_CHECKTIME, MAX_CHECKTIME) AS [Minutes]
FROM CheckTimes;

Using a CTE can sometimes improve readability and performance, although the performance gains are more situation-dependent.

Naveen Kumar2y ago

Hello Feroz,

To calculate hours and minutes, we need two datetime values. However, I see only CHECKTIME in your provided data. Assuming BADGENUMBER is not a primary key and that the same BADGENUMBER can have multiple CHECKTIME entries, please see the query below to suit your requirement.

DROP TABLE IF EXISTS EmployeeCheckTimes
CREATE TABLE EmployeeCheckTimes (
    CHECKTIME DATETIME,
    BADGENUMBER INT
);

INSERT INTO EmployeeCheckTimes (CHECKTIME, BADGENUMBER) VALUES
('2024-05-01 00:00:09.000', 3001025),
('2024-05-02 00:09:10.000', 3001025),
('2024-05-01 00:00:09.000', 900511),
('2024-05-01 00:12:08.000', 900511),
('2024-05-01 00:00:21.000', 700677),
('2024-05-01 00:00:32.000', 700618),
('2024-05-01 00:00:45.000', 700447),
('2024-05-01 00:00:49.000', 101355),
('2024-05-01 00:00:52.000', 101534),
('2024-05-01 00:10:02.000', 101534),
('2024-05-01 00:01:09.000', 100066)

SELECT BADGENUMBER, 
	MIN(CHECKTIME) AS MIN_CHECKTIME, 
	MAX(CHECKTIME) AS MAX_CHECKTIME,
	DATEDIFF(HOUR,   MIN(CHECKTIME), MAX(CHECKTIME)) AS  [Hours],
    DATEDIFF(MINUTE,  MIN(CHECKTIME), MAX(CHECKTIME)) AS  [Minutes]
FROM EmployeeCheckTimes
GROUP BY BADGENUMBER