Post reply

Time Difference in Minutes for the Given Dates

  • July 31, 2020 at 4:05 pm

    #3776343

    Hi friends,

    We have a table as below:

    Table_emp_avail

    DAY_OF_WEEK: 0 - Monday, 1 - Tuesday,... 6 - Sunday

    TIME columns: HH24:MI format

    Need to calculate total available minutes for the given date range in SQL query. For example,

    Case 1: From 27-Jul-2020 (Monday) to 02-Aug-2020 (Sunday), the employee 1001's total available hours should be 2700 (i.e., (8 hr * 5 days) + (5 hr * 1 day) = 2400 + 300 = 2700 minutes)

    Case 2: From 27-Jul-2020 (Monday) to 28-Jul-2020 (Tuesday), the employee 1001's total available hours should be 960 (i.e., (8 hr * 2 days) =  960 minutes)

    Help would be appreciated! Thanks in advance!

    Table script:

    create table emp_availablity

    (emp_no INT,

    day_of_week INT,

    from_time VARCHAR(20),

    to_time VARCHAR(20))

    INSERT INTO emp_availablity VALUES (1001,0,'09:00','17:00'),(1001,1,'09:00','17:00'),(1001,2,'09:00','17:00'),

    (1001,3,'09:00','17:00'),(1001,4,'09:00','17:00'),(1001,5,'12:00','17:00'),(1001,6,'00:00','00:00');

  • July 31, 2020 at 6:05 pm

    #3776378

    Store the dates as datetime, not VARCHAR. Then you can just use DATEDIFF(minute,startdate, enddate)

    Otherwise you have to cast the VARCHAR columns, and that'll cause the query to be really slow because of the implicit conversion (if it works at all).

  • July 31, 2020 at 6:25 pm

    #3776384

    Try the following:

    CREATE TABLE dbo.emp_availability (
    emp_no INT,
        day_of_week INT,
        from_time VARCHAR(20),
        to_time VARCHAR(20)
    );
    INSERT INTO dbo.emp_availability (emp_no, day_of_week, from_time, to_time)
    VALUES(1001, 0, '09:00', '17:00'),
    (1001, 1, '09:00', '17:00'),
    (1001, 2, '09:00', '17:00'),
    (1001, 3, '09:00', '17:00'),
    (1001, 4, '09:00', '17:00'),
    (1001, 5, '12:00', '17:00'),
    (1001, 6, '00:00', '00:00');

    DECLARE @StartDate AS date= '2019-07-27',
    @EndDate AS date= '2019-08-02';

    WITH DATETIME_VALUES AS (

    SELECT TOP (DATEDIFF(day, @StartDate, @EndDate) + 1)
    DATEADD(day, N.N, @StartDate) AS THE_DATE,
    N.N AS DayOfTheWeek
    FROM (
    SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    ) AS N
    )
    SELECT
    EA.emp_no,
    SUM(DATEDIFF(minute, CONVERT(time, EA.from_time), CONVERT(time, EA.to_time))) AS AvailableMinutes
    FROM dbo.emp_availability AS EA
    INNER JOIN DATETIME_VALUES AS DV
    ON EA.day_of_week = DV.DayOfTheWeek
    GROUP BY EA.emp_no
    ORDER BY EA.emp_no;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • August 1, 2020 at 2:15 pm

    #3776536

    @sgmunson, that was excellent!

    But when StartDate and EndDate are given as 01-Aug-2020 (Saturday), the result is 480 minutes (screenshot attached). It should be 300 minutes (5 hr x 60 min).

    Similarly, for 02-Aug-2020 (Sunday), the result is same 480. But it should be 0.

    Thanks in advance!

    • This reply was modified 3 years, 8 months ago by  iniyavan.

    Attachments:
    You must be logged in to view attached files.

  • August 1, 2020 at 4:37 pm

    #3776555

    Hi @sgmunson,

    I guess the inner table "N" should be dynamic based on the given dates. Then this will be good.

    Could you please refer the attached dynamic result using CONNECT BY (01-Jul-20 to 15-Jul-20)? It is in Oracle syntax. Is it possible to use the same for the "N" table?

    It would be greatly appreciated. Thanks in advance!

    inner_table

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply