利用SQL统计时间时怎么去除周末的时间（非工作日时间）,我需要的是工作时间．

最新推荐文章于 2024-09-22 13:48:15 发布

jackeyabc

最新推荐文章于 2024-09-22 13:48:15 发布

阅读量3.1k

点赞数

分类专栏： SQL数据库开发文章标签：工作 sql insert table object go

本文链接：https://blog.csdn.net/jackeyabc/article/details/1528070

版权

SQL数据库开发专栏收录该内容

42 篇文章 0 订阅

订阅专栏

将所有周六/周日/节假日放入表二中.

if object_id('pubs..tb') is not null
drop table tb
go

create table tb(
Dept_ID varchar(10),
EmpNo   varchar(10),
Start   datetime,
tEnd    datetime)

insert into tb(Dept_ID,EmpNo,Start,tEnd) values('A1000','C0701006','2006-10-10','2006-11-01')
insert into tb(Dept_ID,EmpNo,Start,tEnd) values('B2000','C0607003','2006-12-13','2006-12-29')
insert into tb(Dept_ID,EmpNo,Start,tEnd) values('C3000','C0501007','2007-02-02','2007-03-01')
go

if object_id('pubs..tb2') is not null
drop table tb2
go

create table tb2(
DateOfThis datetime,
EventType_FK int,
WorkingDay_Flag int)

insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-10',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-11',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-12',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-13',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-14',           82,              0)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-15',           82,              0)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-16',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-17',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-18',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-19',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-20',           85,              1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-21',           82,              0)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-22',           82,              0)
go

select t1.* , datediff(day,t1.Start,t1.tEnd) - t2.days as days from tb t1,
(
select a.dept_id ,
sum(case when b.DateOfThis >= a.Start and b.DateOfThis <= a.tEnd and b.WorkingDay_Flag = 0 then 1 else 0 end) as days
from tb a , tb2 b
group by a.dept_id
) t2
where t1.dept_id = t2.dept_id

--datediff(day,Start,tEnd) as days from tb

drop table tb,tb2

/*
Dept_ID    EmpNo      Start                                                  tEnd                                                   days
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A1000      C0701006   2006-10-10 00:00:00.000                                2006-11-01 00:00:00.000                                18
B2000      C0607003   2006-12-13 00:00:00.000                                2006-12-29 00:00:00.000                                16
C3000      C0501007   2007-02-02 00:00:00.000                                2007-03-01 00:00:00.000                                27

（所影响的行数为 3 行）