在Table1中有PNO,DDate,PNO表示员工工号,DDate表示日期,该表用于记录某一员工某一天上班记录的信息,现在要查从2008-4-1到2008-04-15号内连续上班等于或大于4天的员工
该语句怎样写?thanks!假如table1中的数据为
A0001 2008-04-01 00:00:00.000
A0001 2008-04-02 00:00:00.000
A0001 2008-04-03 00:00:00.000
A0001 2008-04-10 00:00:00.000
A0001 2008-04-11 00:00:00.000
A0001 2008-04-12 00:00:00.000
A0001 2008-04-13 00:00:00.000
A0002 2008-03-31 00:00:00.000
A0002 2008-04-01 00:00:00.000
A0002 2008-04-02 00:00:00.000
A0002 2008-04-03 00:00:00.000
A0002 2008-04-05 00:00:00.000
则只有A0001才符合
该语句怎样写?thanks!假如table1中的数据为
A0001 2008-04-01 00:00:00.000
A0001 2008-04-02 00:00:00.000
A0001 2008-04-03 00:00:00.000
A0001 2008-04-10 00:00:00.000
A0001 2008-04-11 00:00:00.000
A0001 2008-04-12 00:00:00.000
A0001 2008-04-13 00:00:00.000
A0002 2008-03-31 00:00:00.000
A0002 2008-04-01 00:00:00.000
A0002 2008-04-02 00:00:00.000
A0002 2008-04-03 00:00:00.000
A0002 2008-04-05 00:00:00.000
则只有A0001才符合
from tb
group by pno
having count(1)>=4 and datediff(d,min(ddate),max(ddate))>=4
from tb
group by pno
having count(1)>=4 and datediff(d,min(ddate),max(ddate))>=
RETURNS int
AS
begin
declare @ii int
set @ii=0
while not exists(select * from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date)
begin
set @ii=@ii+1
set @date=dateadd('d',1,@date)
if (@date<>(select top 1 convert(nvarchar(10),DDate,120) from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date order by DDate asc))
set @ii=0
end
if (@ii is null)
set @ii=0
RETURN @ii
ENDselect * from (
select PNO,dbo.判断时间次数(PNO,convert(nvarchar(10),min(DDate),120)) as 连续次数 from Table1
group by PNO) as a
where 连续次数>4
RETURNS int
AS
begin
declare @ii int
set @ii=0
while not exists(select * from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date)
begin
set @ii=@ii+1
set @date=dateadd('d',1,@date)
if (@date<>(select top 1 convert(nvarchar(10),DDate,120) from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date order by DDate asc))
set @ii=0
end
if (@ii is null)
set @ii=0
RETURN @ii
ENDselect * from (
select PNO,dbo.判断时间次数(PNO,convert(nvarchar(10),min(DDate),120)) as 连续次数 from Table1
group by PNO) as a
where 连续次数>4
insert into table1 select 'A0001', '2008-04-01 00:00:00.000'
insert into table1 select 'A0001', '2008-04-02 00:00:00.000'
insert into table1 select 'A0001', '2008-04-03 00:00:00.000'
insert into table1 select 'A0001', '2008-04-10 00:00:00.000'
insert into table1 select 'A0001', '2008-04-11 00:00:00.000'
insert into table1 select 'A0001', '2008-04-12 00:00:00.000'
insert into table1 select 'A0001', '2008-04-13 00:00:00.000'
insert into table1 select 'A0002', '2008-03-31 00:00:00.000'
insert into table1 select 'A0002', '2008-04-01 00:00:00.000'
insert into table1 select 'A0002', '2008-04-02 00:00:00.000'
insert into table1 select 'A0002', '2008-04-03 00:00:00.000'
insert into table1 select 'A0002', '2008-04-05 00:00:00.000' select a.pno
from table1 a
inner join table1 b on a.pno=b.pno and datediff(day,a.DDate,b.DDate)=1
inner join table1 c on b.pno=c.pno and datediff(day,b.DDate,c.DDate)=1
inner join table1 d on c.pno=d.pno and datediff(day,c.DDate,d.DDate)=1
where a.DDate between '2008-04-01 00:00:00.000' and '2008-04-15 00:00:00.000'
/*
------
A0001
*/
insert into Table1
select 'A0001','2008-04-01 00:00:00.000'
union all
select 'A0001','2008-04-02 00:00:00.000'
union all
select 'A0001','2008-04-03 00:00:00.000'
union all
select 'A0001','2008-04-10 00:00:00.000'
union all
select 'A0001','2008-04-11 00:00:00.000'
union all
select 'A0001','2008-04-12 00:00:00.000'
union all
select 'A0001','2008-04-13 00:00:00.000'
union all
select 'A0002','2008-03-31 00:00:00.000'
union all
select 'A0002','2008-04-01 00:00:00.000'
union all
select 'A0002','2008-04-02 00:00:00.000'
union all
select 'A0002','2008-04-03 00:00:00.000'
union all
select 'A0002','2008-04-05 00:00:00.000' Create FUNCTION 判断时间次数(@PNO nvarchar(50),@date datetime)
RETURNS int
AS
begin
declare @ii int
set @ii=0
while exists(select * from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date)
begin
set @ii=@ii+1
set @date=dateadd(d,1,@date)
if (@date <>(select top 1 convert(nvarchar(10),DDate,120) from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date order by DDate asc))
set @ii=0
end
if (@ii is null)
set @ii=0
RETURN @ii
END select * from (
select PNO,dbo.判断时间次数(PNO,convert(nvarchar(10),min(DDate),120)) as 连续次数 from Table1
group by PNO) as a
where 连续次数>4
insert into table1 select 'A0001', '2008-04-01 00:00:00.000'
insert into table1 select 'A0001', '2008-04-02 00:00:00.000'
insert into table1 select 'A0001', '2008-04-03 00:00:00.000'
insert into table1 select 'A0001', '2008-04-10 00:00:00.000'
insert into table1 select 'A0001', '2008-04-11 00:00:00.000'
insert into table1 select 'A0001', '2008-04-12 00:00:00.000'
insert into table1 select 'A0001', '2008-04-13 00:00:00.000'
insert into table1 select 'A0002', '2008-03-31 00:00:00.000'
insert into table1 select 'A0002', '2008-04-01 00:00:00.000'
insert into table1 select 'A0002', '2008-04-02 00:00:00.000'
insert into table1 select 'A0002', '2008-04-03 00:00:00.000'
insert into table1 select 'A0002', '2008-04-05 00:00:00.000'
GOselect A.* from table1 a where a.DDATE>='2008-04-04' and (select count(1) from table1 b where
pno=a.pno and DDate>=a.DDATE-3 AND DDate<=a.ddate)=4
GO
drop table table1
GO