27,579
社区成员
发帖
与我相关
我的任务
分享
select name, convert(varchar(100), checktime, 23) checkdate , min(checktime) checkin, max(checktime) checkout
from checkinout
where checktime >= '2018-01-01'
and checktime < '2018-02-01'
group by name, convert(varchar(100), checktime, 23)
order by name
select name, convert(varchar(100), checktime, 23) checkdate ,
dateadd(hour, 6, min(checktime)) checkin,
dateadd(hour, 6, max(checktime)) checkout
from(
select dateadd(hour, -6, checkdate) as checkdate from checkinout
)data
where checktime >= '2018-01-01'
and checktime < '2018-02-01'
group by name, convert(varchar(100), checktime, 23)
order by name
DECLARE @t TABLE (
[name] NVARCHAR(10),
checktime DATETIME
)
INSERT INTO @t VALUES ('小明','2018-02-01 07:00')
INSERT INTO @t VALUES ('小明','2018-02-01 18:00')
INSERT INTO @t VALUES ('小红','2018-02-01 07:00')
INSERT INTO @t VALUES ('小红','2018-02-02 01:00')
;WITH cte AS (
select name, CAST(convert(CHAR(10), checktime, 120) AS DATETIME) checktime
--, min(checktime) checkin, max(checktime) checkout
from @t AS t
WHERE DATEPART(hour,t.checktime)>6
group by name,CAST(convert(CHAR(10), checktime, 120) AS DATETIME)
)
SELECT
t.name
,t.checktime
,(SELECT MIN(checktime) FROM @t AS b WHERE
b.name=t.name
--同一天
AND convert(CHAR(10), b.checktime, 120)= t.checktime
--6点之后
AND b.checktime>convert(CHAR(10), t.checktime, 120)+' 06:00'
) AS checkin
,(SELECT MAX(checktime) FROM @t AS b WHERE
b.name=t.name
--同一天或者后一天
AND (
convert(CHAR(10), b.checktime, 120)= t.checktime
OR
convert(CHAR(10), b.checktime, 120)= t.checktime+1
)
--必须是后一天6点之前
AND b.checktime<convert(CHAR(10), t.checktime+1, 120)+' 06:00'
) AS checkout
FROM cte AS t
/*
name checktime checkin checkout
---------- ----------------------- ----------------------- -----------------------
小红 2018-02-01 00:00:00.000 2018-02-01 07:00:00.000 2018-02-02 01:00:00.000
小明 2018-02-01 00:00:00.000 2018-02-01 07:00:00.000 2018-02-01 18:00:00.000
*/