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