我的表名为:SCTJ_SC数据如下:
YGBH YGXM RQ
00001 张三 20080226
00001 张三 20080302
00002 李四 20080228
00002 李四 20080301
00003 王五 20080228
00003 王五 20080301
00003 王五 20080306
我现在要如下结果,得到2008年02月26日起至2008年03月06日至这段期间内连续缺勤5天(包括5天)以上的人:
张三和王五,这期间2月份可能是28天,也可能我选取的日期是其他的时间段.在这只给出比较特殊的一段时间.
请各位大侠帮忙.
另:如果有解另开200分!谢谢!!!!!!!!!!!!!!!
YGBH YGXM RQ
00001 张三 20080226
00001 张三 20080302
00002 李四 20080228
00002 李四 20080301
00003 王五 20080228
00003 王五 20080301
00003 王五 20080306
我现在要如下结果,得到2008年02月26日起至2008年03月06日至这段期间内连续缺勤5天(包括5天)以上的人:
张三和王五,这期间2月份可能是28天,也可能我选取的日期是其他的时间段.在这只给出比较特殊的一段时间.
请各位大侠帮忙.
另:如果有解另开200分!谢谢!!!!!!!!!!!!!!!
declare @a table (d datetime,id int,flag bit)
insert @a select '2004-01-01',1,1
union all select '2004-01-02',1,1
union all select '2004-01-03',1,1
union all select '2004-01-04',1,1
union all select '2004-01-05',1,1
union all select '2004-01-06',1,1
union all select '2004-01-07',1,1
union all select '2004-01-08',1,1
union all select '2004-01-09',1,1
union all select '2004-01-01',2,1
union all select '2004-01-02',2,1
union all select '2004-01-03',2,1
union all select '2004-01-04',2,1
union all select '2004-01-05',2,1
union all select '2004-01-06',2,1
union all select '2004-07-08',2,1
union all select '2004-01-09',2,1
union all select '2004-01-10',2,1--1:
select distinct A.id
from @a A,@a B
where A.d between '2004-01-01' and '2004-02-01'
and B.d between '2004-01-01' and '2004-02-01'
and datediff(day,A.d,B.d)=6
and a.id=B.id
and (select count(*) from @a where d between A.d and B.d and id=A.id and flag=1)=7--2:
select distinct A.id from @a A where d between '2004-01-01' and '2004-02-01'
and exists(select 1 from @a B
where datediff(day,A.d,B.d)=6 and A.id=B.id and d between '2004-01-01' and '2004-02-01'
and (select count(*) from @a where d between A.d and B.d and id=A.id and flag=1)=7 )--3:
select distinct A.id from @a A,@a B,@a C
where A.id=B.id and A.id=C.id
and A.d>='2004-01-01' and A.d <='2004-02-01'
and B.d>='2004-01-01' and B.d <='2004-02-01'
and datediff(d,A.d,B.d)=6 and C.d >=A.d and C.d <=B.d
and A.flag=1 and B.flag=1 and C.flag=1
group by A.id,A.d
having(count(*)>=7)
--4
select distinct A.id from @a A,@a B,
(select 1 as N
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 0
) C
where A.id=B.id
and A.d>='2004-01-01' and A.d <='2004-02-01'
and B.d>='2004-01-01' and B.d <='2004-02-01'
and B.d=dateadd(d,N,A.d)
and A.flag=1 and B.flag=1
group by A.id,A.d
having(count(*)>=7)
declare @SCTJ_SC table ([YGBH] int,[YGXM] nvarchar(2),[RQ] Datetime)
Insert into @SCTJ_SC
select 00001,'张三','20080226' union all
select 00001,'张三','20080302' union all
select 00002,'李四','20080228' union all
select 00002,'李四','20080301' union all
select 00003,'王五','20080228' union all
select 00003,'王五','20080301' union all
select 00003,'王五','20080306'
--Select * from @SCTJ_SC
SELECT * , datediff(day,t.rq,isnull((select min(rq) from @SCTJ_SC where rq >t.rq and [YGXM] = t.[YGXM]),t.rq) ) ,
isnull((select min(rq) from @SCTJ_SC where rq >t.rq and [YGXM] = t.[YGXM]),t.rq) as diff
from @SCTJ_SC t
where datediff(day,t.rq,isnull((select min(rq) from @SCTJ_SC where rq >t.rq and [YGXM] = t.[YGXM]),t.rq) ) >=5
/*
YGBH YGXM RQ diff
----------- ---- ----------------------- ----------- -----------------------
1 张三 2008-02-26 00:00:00.000 5 2008-03-02 00:00:00.000
3 王五 2008-03-01 00:00:00.000 5 2008-03-06 00:00:00.000(2 row(s) affected)*/
insert into @a(YGBH,YGXM,t1) select YGBH,YGXM,cast(RQ as datetime) from SCTJ_SC order by YGXM,RQ select aaa.YGBH,aaa.YGXM,aaa.t1,bbb.YGBH,bbb.YGXM,bbb.t1
from @a as aaa
inner join(select tid,YGBH,YGXM,t1 from @a ) as bbb
on aaa.tid=bbb.tid-1
where abs(datediff(mi,aaa.t1,bbb.t1)>5 )
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (YGBH varchar(11),YGXM varchar(11),RQ datetime)
insert into #T
select '00001','张三','20080226' union all
select '00001','张三','20080302' union all
select '00002','李四','20080228' union all
select '00002','李四','20080301' union all
select '00003','王五','20080228' union all
select '00003','王五','20080301' union all
select '00003','王五','20080306'select * from #T t where datediff(day,RQ,(select min(RQ) from #T where YGBH=t.YGBH and RQ>t.RQ))>=5
/*
YGBH YGXM RQ
----------- ----------- -----------------------
00001 张三 2008-02-26 00:00:00.000
00003 王五 2008-03-01 00:00:00.000
*/
insert into @a(YGBH,YGXM,t1)
select YGBH,YGXM,cast(RQ as datetime)
from SCTJ_SC where cast(RQ as datetime) between 时间1 and 时间2 order by YGXM,RQ
--注意,时间1 < 时间2select aaa.YGBH,aaa.YGXM,aaa.t1,bbb.YGBH,bbb.YGXM,bbb.t1
from @a as aaa
inner join(select tid,YGBH,YGXM,t1 from @a ) as bbb
on aaa.tid=bbb.tid-1
where abs(datediff(mi,aaa.t1,bbb.t1)>5 )
insert into SCTJ_SC
select '00001','张三','20080226' union all
select '00001','张三','20080302' union all
select '00001','张三','20080308' union all
select '00002','李四','20080228' union all
select '00002','李四','20080301' union all
select '00002','李四','20080307' union all
select '00003','王五','20080228' union all
select '00003','王五','20080301' union all
select '00003','王五','20080306'select a.*,datediff(day,a.RQ,b.RQ) from SCTJ_SC as a inner join SCTJ_SC as b on a.YGBH=b.YGBH
and b.RQ=(select min(RQ) from SCTJ_SC where RQ>A.RQ and YGBH=a.YGBH )
and datediff(day,a.RQ,b.RQ)>5
/*回复:20080527001总:00051 */
/*主题:查找缺失数据 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table [SCTJ_SC] ([YGBH] varchar(5),[YGXM] varchar(4),[RQ] datetime)
insert into [SCTJ_SC] select '00001','张三','20080226'
insert into [SCTJ_SC] select '00001','张三','20080302'
insert into [SCTJ_SC] select '00002','李四','20080228'
insert into [SCTJ_SC] select '00002','李四','20080301'
insert into [SCTJ_SC] select '00003','王五','20080228'
insert into [SCTJ_SC] select '00003','王五','20080301'
insert into [SCTJ_SC] select '00003','王五','20080306'
go--代码--------------------------------------------------------------------------
--如果不考虑节假日的情况,可以如下:
select * from sctj_sc a where datediff(day,rq,
(select min(rq) from sctj_sc where ygbh=a.ygbh and rq>a.rq))>=5
go /*结果--------------------------------------------------------------------------
YGBH YGXM RQ
----- ---- ------------------------------------------------------
00001 张三 2008-02-26 00:00:00.000
00003 王五 2008-03-01 00:00:00.000--清除------------------------------------------------------------------------*/
drop table sctj_sc
where a.YGBH=b.YGBH and datediff(dd,a.RQ,b.RQ)>4 and b.RQ>a.RQ
and a.RQ>='2008-02-26' and b.RQ<='2008-03-06'
呵呵 回答过类似的
insert into tb values('00001' , '张三' , '2008-02-26')
insert into tb values('00001' , '张三' , '2008-03-02')
insert into tb values('00002' , '李四' , '2008-02-28')
insert into tb values('00002' , '李四' , '2008-03-01')
insert into tb values('00003' , '王五' , '2008-02-28')
insert into tb values('00003' , '王五' , '2008-03-01')
insert into tb values('00003' , '王五' , '2008-03-06')
goselect distinct m.ygxm from
(
select * , px = (select count(1) from tb where ygbh = t.ygbh and rq < t.rq) + 1 from tb t
) m,
(
select * , px = (select count(1) from tb where ygbh = t.ygbh and rq < t.rq) + 1 from tb t
) n
where m.ygbh = n.ygbh and m.px = n.px - 1 and datediff(day , m.rq , n.rq) >= 5drop table tb/*
ygxm
----------
张三
王五(所影响的行数为 2 行)
*/
where a.YGBH=b.YGBH and datediff(dd,a.RQ,b.RQ)>4 and b.RQ>a.RQ
and a.RQ>='2008-02-26' and b.RQ<='2008-03-06'
group by a.YGBH,a.YGXM
insert into tb values('00001' , '张三' , '2008-02-26')
insert into tb values('00001' , '张三' , '2008-03-02')
insert into tb values('00002' , '李四' , '2008-02-28')
insert into tb values('00002' , '李四' , '2008-03-01')
insert into tb values('00003' , '王五' , '2008-02-28')
insert into tb values('00003' , '王五' , '2008-03-01')
insert into tb values('00003' , '王五' , '2008-03-06')
godeclare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2008-02-26'
set @dt2 = '2008-03-06'select ygxm from tb where YGBH not in
( select YGBH from
(
select * , px = (select count(1) from tb where rq between @dt1 and @dt2 and ygbh = t.ygbh and rq < t.rq) + 1 from tb t where rq between @dt1 and @dt2
) m
where px = 2
) and (datediff(day , @dt1 , rq) >= 5 or datediff(day , rq , @dt2) >= 5)
union
select m.ygxm from
(
select * , px = (select count(1) from tb where rq between @dt1 and @dt2 and ygbh = t.ygbh and rq < t.rq) + 1 from tb t where rq between @dt1 and @dt2
) m,
(
select * , px = (select count(1) from tb where rq between @dt1 and @dt2 and ygbh = t.ygbh and rq < t.rq) + 1 from tb t where rq between @dt1 and @dt2
) n
where m.ygbh = n.ygbh and m.px = n.px - 1 and datediff(day , m.rq , n.rq) >= 5drop table tb/*
ygxm
----------
张三
王五(所影响的行数为 2 行)
*/