# Time Difference in Minutes for the Given Dates

• Hi friends,

We have a table as below:

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');

• 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).

• 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 AvailableMinutesFROM dbo.emp_availability AS EAINNER JOIN DATETIME_VALUES AS DV	ON EA.day_of_week = DV.DayOfTheWeekGROUP BY EA.emp_noORDER BY EA.emp_no;`

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Make Guaranteed Income

• @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.

• This reply was modified 9 months, 2 weeks ago by  iniyavan.
###### Attachments:
You must be logged in to view attached files.
• 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!

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

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