日期:2014-05-17 浏览次数:20513 次
Drop table [Test]
Create table [Test](
Id [int] Identity(1,1),
CDate DateTime,
bc varchar(50),
WorkTime Float
)
insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','晚班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-02 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','晚班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','晚班',8)
--Drop table [Test]
--Create table [Test](
--Id INT Identity(1,1),
--CDate DateTime,
--bc varchar(50),
--WorkTime Float
--)
--insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','晚班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-02 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','晚班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','晚班',8)
SELECT cdate,CASE WHEN bc='白班' THEN '晚班' WHEN bc='晚班' THEN '白班' ELSE '白班晚班均缺失' END '缺失班次' FROM Test
WHERE CONVERT(DATE,cdate) IN (
SELECT CONVERT(DATE,cdate)cdate
FROM test a
GROUP BY CONVERT(DATE,cdate)
HAVING COUNT(1)<>2)
/*
cdate 缺失班次
----------------------- --------------
2014-01-02 01:01:01.000 晚班
*/
Drop table [Test]
go
Create table [Test](
Id [int] Identity(1,1),
CDate DateTime,
bc varchar(50),
WorkTime Float
)
insert