34,593
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[TEST](
[编码] [int] NOT NULL,
[人员姓名] [varchar](20) NOT NULL,
[开始时刻] [datetime] NOT NULL,
[结束时刻] [datetime] NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[编码] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
[/quote]
结束时刻为可空类型?那如果结束时刻为null时,怎么算工作时间,是算到开始时间后的第一个10点或19点吗
CREATE TABLE [dbo].[TEST](
[编码] [int] NOT NULL,
[人员姓名] [varchar](20) NOT NULL,
[开始时刻] [datetime] NOT NULL,
[结束时刻] [datetime] NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[编码] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(1,'测试1','2013-12-19 11:58:10','2013-12-19 12:59:26')
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(2,'测试2','2013-12-19 18:58:10','2013-12-19 18:59:26')
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(3,'测试1','2013-12-19 20:58:10','2013-12-19 20:59:26')
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(4,'测试1','2013-12-20 06:58:10','2013-12-20 07:59:26')
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(5,'测试1','2013-12-20 18:58:10','2013-12-20 18:59:26')
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(6,'测试2','2013-12-21 21:58:10','2013-12-21 22:59:26')
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(7,'测试1','2013-12-21 09:58:10','2013-12-21 10:59:26')
insert into TEST(编码,人员姓名,开始时刻,结束时刻) values(8,'测试1','2013-12-21 13:58:10','2013-12-21 14:59:26')
select *
from 表
where 字段 >= convert(varchar(10),GETDATE(),120)+' 19:00:00' --每天晚上19点
and 字段 <= convert(varchar(10),dateadd(day,2,getdate()),120)+' 10:00:00' --第二天10点的工作时长
还是这样:
select convert(varchar(10),GETDATE(),120)+' 19:00:00', --每天晚上19点
convert(varchar(10),dateadd(day,2,getdate()),120)+' 10:00:00', --第二天10点的工作时长
--时长
DATEDIFF(hour,convert(varchar(10),GETDATE(),120)+' 19:00:00',convert(varchar(10),dateadd(day,2,getdate()),120)+' 10:00:00')
DECLARE @begin DATETIME
DECLARE @end DATETIME
SET @begin='2013-12-19 08:30:00'
SET @end='2013-12-21 08:30:00'
;WITH cte AS (
SELECT * FROM TEST
WHERE 开始时刻<=@end AND 结束时刻>=@begin)
SELECT 编码,人员姓名,CASE WHEN DATEPART(HOUR,开始时刻) >=10 AND DATEDIFF(dd,开始时刻,结束时刻)=0 AND DATEPART(HOUR,结束时刻) <=19 THEN DATEDIFF(hh,开始时刻,结束时刻) --不跨天不加班
WHEN (DATEPART(HOUR,开始时刻) >=10 OR DATEPART(HOUR,结束时刻) >19) AND DATEDIFF(dd,开始时刻,结束时刻)=0 THEN ABS(19-DATEPART(HOUR,开始时刻)) END [10:00-19:00 的工作时长],--不跨天加班
case WHEN DATEPART(HOUR,开始时刻) >=19 AND DATEDIFF(dd,开始时刻,结束时刻)>0 AND DATEPART(HOUR,结束时刻)<=10 THEN 24-DATEPART(HOUR,开始时刻)+DATEPART(HOUR,结束时刻) --跨天不加班
WHEN dATEPART(HOUR,结束时刻) >10 AND DATEDIFF(dd,开始时刻,结束时刻)>0 AND DATEPART(HOUR,开始时刻) <=19 THEN 24-DATEPART(HOUR,开始时刻)+DATEPART(HOUR,结束时刻) END [19:00-第二天10:00的工作时长]
FROM cte