表中有2个字段Cigarn(打卡时间),Meing(打卡人)select * from Thugin Where Meing='4012203' AND DATEPART(yyyy,CONVERT(datetime,Cigarn))=DATEPART(yyyy,'2010-04-1' ) AND DATEPART(MM,Cigarn)=datepart(MM,'2010-04-30' )
已经查出该员工一个月的所有记录
,但是有几天是重复打了N卡,我现在想取该月每天的最小时间和最大时间
比如本月有30天 就是60条记录。
已经查出该员工一个月的所有记录
,但是有几天是重复打了N卡,我现在想取该月每天的最小时间和最大时间
比如本月有30天 就是60条记录。
(
select * from Thugin Where Meing='4012203' and datediff(mm,Cigarn,'2010-04-1')=0
)
select * from wsp a where
not exists(select 1 from wsp where datediff(mm,Cigarn,a.Cigarn)=0 and Cigarn<a.Cigarn)
and
not exists(select 1 from wsp where datediff(mm,Cigarn,a.Cigarn)=0 and Cigarn>a.Cigarn)
select * from Thugin Where Meing='4012203'
AND DATEPART(yyyy,CONVERT(datetime,Cigarn))=DATEPART(yyyy,'2010-04-1' )
AND DATEPART(MM,Cigarn)=datepart(MM,'2010-04-30' )
)
select * from cte a
where not exists((select 1 from cte where day(a.Cigarn)=day(Cigarn) and a.Cigarn<Cigarn)
or not exists((select 1 from cte where day(a.Cigarn)=day(Cigarn) and a.Cigarn>Cigarn)???
Where Meing='4012203'
AND Cigarn between '2010-04-1' AND '2010-04-30'
group by CONVERT(varchar(10),Cigarn,120)
from Thugin
Where Meing='4012203'
AND DATEPART(yyyy,CONVERT(datetime,Cigarn))=DATEPART(yyyy,'2010-04-1' )
AND DATEPART(MM,Cigarn)=datepart(MM,'2010-04-30' )
group by userid,convert(varchar(7),Cigarn,23)
union all
select userid, max(Cigarn)maxCigarn
from Thugin
Where Meing='4012203'
AND DATEPART(yyyy,CONVERT(datetime,Cigarn))=DATEPART(yyyy,'2010-04-1' )
AND DATEPART(MM,Cigarn)=datepart(MM,'2010-04-30' )
group by userid,convert(varchar(7),Cigarn,23)
;with wsp as
(
select * from Thugin Where Meing='4012203' and datediff(mm,Cigarn,'2010-04-1')=0
)SELECT min(Cigarn),max(Cigarn),Cigarn from wsp
group by datename(dd,Cigarn)
create table tb
(
id int,
starttime datetime
)
goINSERT INTO tb values (1,'2010-05-02 00:00:01')
INSERT INTO tb values (2,'2010-05-02 00:10:01')
INSERT INTO tb values (3,'2010-05-02 01:00:01')
INSERT INTO tb values (4,'2010-05-02 02:00:01')
INSERT INTO tb values (5,'2010-05-03 00:00:01')
INSERT INTO tb values (6,'2010-05-03 02:00:01')
INSERT INTO tb values (7,'2010-05-03 00:04:01')
INSERT INTO tb values (8,'2010-05-03 05:00:01')SELECT datename(dd,starttime),min(starttime),max(starttime) from tb
group by datename(dd,starttime)
以上代码可以求出每日的最早和最晚纪录
gocreate table Thugin (Meing varchar(10) not null,Cigarn datetime null,ID bigint identity(1,1) not null)
goinsert into Thugin (Meing,Cigarn) values ('4012201','2010-04-1 8:00:00')
go
insert into Thugin (Meing,Cigarn) values ('4012201','2010-04-1 9:00:00')
go
insert into Thugin (Meing,Cigarn) values ('4012201','2010-04-1 10:00:00')
go
insert into Thugin (Meing,Cigarn) values ('4012201','2010-04-1 18:00:00')
go
insert into Thugin (Meing,Cigarn) values ('4012202','2010-04-1 8:00:00')
go
insert into Thugin (Meing,Cigarn) values ('4012202','2010-04-1 9:00:00')
go
insert into Thugin (Meing,Cigarn) values ('4012202','2010-04-1 11:00:00')
go
insert into Thugin (Meing,Cigarn) values ('4012202','2010-04-1 18:00:00')
goselect a.meing,a.Cigarn from (
SELECT Meing,min(Cigarn) as Cigarn from Thugin
group by Meing
union all
SELECT Meing,max(Cigarn) as Cigarn from Thugin
group by Meing) a
order by a.meinggo
--drop table Thugin--go