Want To Find Total Full Busy Minutes (for a given day)

Transact-SQL
1

Hi

create table dbo.tblUser
(UserID int,
UserName nvarchar(50)
)

insert into dbo.tblUser values (1,'abc')
insert into dbo.tblUser values (2,'efg')
insert into dbo.tblUser values (3,'klm')
insert into dbo.tblUser values (4,'pqr')
insert into dbo.tblUser values (5,'xyz')

Create table dbo.tblShift
(UserID int,
StartTime datetime,
EndTime datetime
)

insert into dbo.tblShift values (1,'2015-05-18 11:46:54.027','2015-05-18 17:46:54.027')
insert into dbo.tblShift values (1,'2015-05-18 18:46:54.027','2015-05-18 20:46:54.027')
insert into dbo.tblShift values (1,'2015-05-18 21:46:54.027','2015-05-18 22:46:54.027')
insert into dbo.tblShift values (2,'2015-05-18 11:46:54.027','2015-05-18 17:46:54.027')
insert into dbo.tblShift values (3,'2015-05-18 11:46:54.027','2015-05-18 17:46:54.027')
insert into dbo.tblShift values (4,'2015-05-18 11:46:54.027','2015-05-18 17:46:54.027')
insert into dbo.tblShift values (5,'2015-05-18 11:46:54.027','2015-05-18 17:46:54.027')
insert into dbo.tblShift values (5,'2015-05-18 18:46:54.027','2015-05-18 19:46:54.027')

select * from dbo.tblShift

From the above structure I want to find the total minutes for a given day when all users are busy i.e. not the total time user worked but only the minutes when all users are busy and no one is available to work.

Thanks

2

When total users and No. of users are same that times all users busy

select *
from (    select distinct sc.StartTime, sc.EndTime, datediff(Minute,sc.StartTime, sc.EndTime) as TotalMinutes
        from tblShift sc ) as s
cross apply (
        select count(distinct UserID) TotalUsers
        from tblShift sc        
        where sc.StartTime between s.StartTime and s.EndTime
        and sc.EndTime between s.StartTime and s.EndTime
        ) as d
1 Like
3

select
StartTime,EndTime,sum(times)Total_Minutes,count(userid)Users_Cnt
from
(
select userid,StartTime,EndTime,datediff(MINUTE,StartTime,EndTime)Times from tblShift
) a
group by StartTime,EndTime