获得空闲时间 [英] get available free time

问题描述

我正在sqlserver 2005中工作.

I am working in sqlserver 2005.

-- Table contains Person Login and logout times i.e.avaialble time in office
create table #available
(
duty_roster_id	bigint identity(1,1),
Work_date	datetime not null,
start_time	datetime not null,
end_time	datetime not null
)
insert into #available values('17-02-2010 00:00:00','17-02-2010 09:00:00','17-02-2010 11:00:00')
insert into #available values('17-02-2010 00:00:00','17-02-2010 11:30:00','17-02-2010 20:00:00')
insert into #available values('17-02-2010 00:00:00','17-02-2010 20:30:00','17-02-2010 23:00:00')
-- Table contains break times 
create table #off_time
(
off_id		bigint	        identity(1,1),	 
work_date	datetime        not null,
off_time_desc	nvarchar(50)	not null,
off_start_time	datetime	null,
off_end_time	datetime	null,
)
insert into #off_time values('17-02-2010 00:00:00','Tea Break','17-02-2010 11:00:00','17-02-2010 11:30:00')
insert into #off_time values('17-02-2010 00:00:00','Lunch'	,'17-02-2010 14:00:00','17-02-2010 15:00:00')
insert into #off_time values('17-02-2010 00:00:00','Tea Break','17-02-2010 17:00:00','17-02-2010 17:30:00')
insert into #off_time values('17-02-2010 00:00:00','Tea Break','17-02-2010 20:45:00','17-02-2010 21:00:00')

在两个表中都可能有多个记录.
start_time和end_time的输出将为-

In both the tables there could be multiple records.
output of start_time and end_time will be as--

'17-02-2010 09:00:00','17-02-2010 11:00:00'
'17-02-2010 11:30:00','17-02-2010 14:00:00'
'17-02-2010 15:00:00','17-02-2010 17:00:00'
'17-02-2010 17:30:00','17-02-2010 20:00:00'
'17-02-2010 20:30:00','17-02-2010 20:45:00'
'17-02-2010 21:00:00','17-02-2010 23:00:00'

推荐答案

那么您是否想查找没有人登录的时间?我想我不明白您要问的问题，因为您的问题中没有问题.

您希望我们为您提供什么帮助?

So are you wanting to find out when no one was logged in? I guess I don''t understand what you''re asking as there wasn''t a question in your question.

What are you wanting us to help you with?

在这里只是猜测，因为这个问题很难解释，但请查看SQL Server联机丛书并查找日期和时间函数.应该涵盖了我想您要问的问题.

Just guessing here as the question was hard to interpret but check SQL Server Books Online and look up Date and Time functions. Should cover what I think you are asking.

输出我已经提到根据提供的输入显示的开始时间和结束时间.这意味着我们需要在删除提供的休息时间后获得办公室中的可用时间间隔.我尝试了是否仅提供start_time和end_times的一个间隔，但是如果#available表中给出了多个间隔，则无法获得逻辑.

谢谢您的答复.

Output i have mentioned that the start time and end time to be displayed according to the input provided. which mean we need to get the available interval of time in the office after removing break times provided. i tried if only one interval of start_time and end_times is provided but unable to get the logic if more than one interval is given in #available table.

Thank you for your reply.

这篇关于获得空闲时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！