如何获得缺少的日期型的连续数?如果有解另加200分送上!!!!!!!!!!

Question

我的表名为: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分!谢谢!!!!!!!!!!!!!!!

Answer 1

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2 
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/

Answer 2

--计算两个日期之间相差的工作天数
CREATE FUNCTION f_WorkDateDiff(
@dt_begin datetime,
@dt_end datetime)
RETURNS int
AS
BEGIN
DECLARE @workday int,@i int,@bz bit,@dt datetime
IF @dt_begin>@dt_end
SELECT @bz=1,@dt=@dt_bsegin,@dt_begin=@dt_end,@dt_end=@dt
ELSE
SET @bz=0
SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
@workday=@i/7*5,
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
WHILE @dt_begin<=@dt_end
BEGIN
SELECT @workday=CASE 
WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
THEN @workday+1 ELSE @workday END,
@dt_begin=@dt_begin+1
END
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO--测试数据
CREATE TABLE tb(Name varchar(10),WorkDate datetime)
INSERT tb SELECT 'aa','2005-01-03'
UNION ALL SELECT 'aa','2005-01-04'
UNION ALL SELECT 'aa','2005-01-05'
UNION ALL SELECT 'aa','2005-01-06'
UNION ALL SELECT 'aa','2005-01-07'
UNION ALL SELECT 'aa','2005-01-10'
UNION ALL SELECT 'aa','2005-01-14'
UNION ALL SELECT 'aa','2005-01-17'
UNION ALL SELECT 'bb','2005-01-11'
UNION ALL SELECT 'bb','2005-01-12'
UNION ALL SELECT 'bb','2005-01-13'
UNION ALL SELECT 'bb','2005-01-10'
UNION ALL SELECT 'bb','2005-01-14'
UNION ALL SELECT 'bb','2005-01-20'
GO--缺勤统计
DECLARE @dt_begin datetime,@dt_end datetime
SELECT @dt_begin='2005-1-1', --统计的开始日期
@dt_end='2005-1-20'        --统计的结束日期--统计
SELECT Name,Days=SUM(Days) FROM(
SELECT Name,Days=dbo.f_WorkDateDiff(
DATEADD(Day,1,WorkDate),
ISNULL(DATEADD(Day,-1,(
SELECT MIN(WorkDate) FROM tb aa
WHERE Name=a.Name 
AND WorkDate>a.WorkDate AND WorkDate<=@dt_end
AND NOT EXISTS(
SELECT * FROM tb 
WHERE WorkDate>@dt_begin AND WorkDate<=@dt_end
AND Name=aa.Name 
AND dbo.f_WorkDateDiff(WorkDate,aa.WorkDate)=2))
),@dt_end))
FROM(
SELECT Name,WorkDate FROM tb
WHERE WorkDate>=@dt_begin AND WorkDate<@dt_end
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT Name,DATEADD(Day,-1,@dt_begin) FROM tb
)a
WHERE (@@DATEFIRST+DATEPART(Weekday,WorkDate)-1)%7 BETWEEN 1 AND 5
AND NOT EXISTS(
SELECT * FROM tb 
WHERE WorkDate>@dt_begin AND WorkDate<=@dt_end
AND Name=a.Name 
AND dbo.f_WorkDateDiff(WorkDate,a.WorkDate)=-2)
)aa GROUP BY Name
/*--结果
Name       Days 
---------------- ----------- 
aa         6
bb         8
--*/

Answer 3

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) 

Answer 4

--> 测试数据
 
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)*/

Answer 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

Answer 6

--> 测试数据: #T
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
*/

Answer 7

declare @a table (tid int identity(1,1),YGBH varchar(100),t_name varchar(100),t1 datetime)
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 )

Answer 8

create table tb(YGBH varchar(10) , YGXM varchar(10) , RQ varchar(10))
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 行）
*/