多次中断计算sql-server

Question

嗨，我想按日期计算多个进出时间组。

下面是当前的表数据

userid  checktime
2336    2018-11-01 08:28:20.000
2336    2018-11-01 13:27:18.000
2336    2018-11-01 13:31:12.000
2336    2018-11-01 18:03:57.000
2336    2018-11-04 07:59:09.000
2336    2018-11-04 13:10:58.000
2336    2018-11-04 13:17:46.000
2336    2018-11-04 17:58:03.000
2336    2018-11-05 08:08:07.000
2336    2018-11-05 13:10:13.000
2336    2018-11-05 13:14:12.000
2336    2018-11-05 17:58:58.000
2336    2018-11-05 17:59:02.000
2336    2018-11-06 07:40:51.000
2336    2018-11-06 13:09:48.000
2336    2018-11-06 13:14:30.000
2336    2018-11-06 17:55:07.000
2336    2018-11-07 07:53:18.000
2336    2018-11-07 13:49:19.000
2336    2018-11-07 13:53:16.000
2336    2018-11-07 18:02:12.000
2336    2018-11-08 07:45:14.000
2336    2018-11-08 13:18:28.000
2336    2018-11-08 13:21:59.000
2336    2018-11-08 18:00:04.000

查询的预期结果

UserID  Date       timein1 timeout1 timein2 timeout2 timein3 timeout3
2336    1-Nov-18    8:28    13:27   13:31   18:03       
2336    4-Nov-18    7:59    13:10   13:17   17:58       
2336    5-Nov-18    8:08    13:10   13:14   17:58     17:59

借助Gordon逻辑创建查询

`DECLARE @EMPID AS VARCHAR(50) = '101356'
DECLARE @CHECKTIME AS DATE ='11-01-2018'
DECLARE @CHECKTIME2 AS DATE = '11-20-2018'
select convert(varchar,checkdate,6) as DAYDATE,
       DAYPART,
       CASE WHEN DATEDIFF(MINUTE,(MIN(thetime)),(MAX(thetime))) = 0 AND (DATEDIFF(MINUTE,'08:45:00',(CONVERT(VARCHAR(20), MIN(thetime), 108)))) > 240 OR (DATEDIFF(MINUTE,'08:45:00',(CONVERT(VARCHAR(20), MIN(thetime), 108)))) < 0 THEN ' '
       ELSE (DATEDIFF(MINUTE,'08:30:00',(CONVERT(VARCHAR(20), MIN(thetime), 108)))) END AS LATETIMEIN,
       CASE WHEN DATEDIFF(MINUTE,(MIN(thetime)),(MAX(thetime))) = 0 AND (DATEDIFF(MINUTE,'08:30:00',(CONVERT(VARCHAR(20), MIN(thetime), 108)))) > 240 THEN ' '
       ELSE CONVERT(VARCHAR(20), MIN(thetime), 108) END AS TIMEIN2,
       max(case when seqnum = 2 then thetime  end) as BreakOut1,
       max(case when seqnum = 3 then thetime end) as BreakIn1,
       CASE WHEN DATEDIFF(MINUTE,(MIN(thetime)),(MAX(thetime))) = 0 AND (DATEDIFF(MINUTE,(CONVERT(VARCHAR(20), MAX(thetime), 108)),'18:00:00')) > 240 THEN ' '
       ELSE CONVERT(VARCHAR(20), MAX(thetime), 108) END AS TIMEOUT2,       
       CASE WHEN DATEDIFF(MINUTE,(MIN(thetime)),(MAX(thetime))) = 0 AND (DATEDIFF(MINUTE,(CONVERT(VARCHAR(20), MAX(thetime), 108)),'18:00:00')) > 240 OR (DATEDIFF(MINUTE,(CONVERT(VARCHAR(20), MAX(thetime), 108)),'18:00:00')) < 0 THEN ' '
       ELSE (DATEDIFF(MINUTE,(CONVERT(VARCHAR(20), MAX(thetime), 108)),'18:00:00')) END AS EARLYTIMEOUT,
       (convert(varchar(30), (datediff(mi, (MIN(thetime)), (MAX(thetime))) / 60)) + ':' + convert(varchar(30), (datediff(mi, (MIN(thetime)), (MAX(thetime))) % 60))) As WORKhrs,
       DATEDIFF(mi,(MIN(thetime)),(MAX(thetime))) as WRSMIN,
       Remarks
from 
(select 
       convert(date,d.Caldate) as checkdate,
       SUBSTRING(DATENAME(DW,CONVERT(VARCHAR(20),D.Caldate,106)),1,3) AS DAYPART,
       cast(convert(varchar(20),c.checktime,108)as varchar) as thetime,
       D.Holiday as Remarks,
       row_number() over (partition by c.userid, convert(date, c.checktime) order by c.checktime) as seqnum
       FROM Calender AS D
       CROSS JOIN USERINFO AS E
       LEFT OUTER JOIN CHECKINOUT AS C ON (E.USERID = C.USERID AND CONVERT(VARCHAR,C.CHECKTIME,5)=CONVERT(VARCHAR,D.CALDATE,5) )
       RIGHT JOIN Employee AS X ON E.BADGENUMBER=X.EmployeeID OR E.BADGENUMBER=X.Badgenumber
       WHERE X.EmployeeID=@EMPID AND D.Caldate >= @CHECKTIME AND D.Caldate <= @CHECKTIME2
     ) t
group by checkdate,DAYPART,Remarks`

输出是

DAYDATE TIMEIN2 BreakOut1 BreakIn1 TIMEOUT2 EARLYTIMEOUT WORKhrs WRSMIN备注:11月18日清华24 08:54:59 17:30空17:27:27: 33 8:33 513 NULL 02 18 Fri NULL 03 11月18日Sat NULL 04 11月18日Sun 18 :48:43 NULL 0 0 0:0 0 0 518星期一21 08 :45 18 :23 18 : 19 18:23:19 0 9:32 572 011月18日清华008:42:52 18:13:0318:13:03 0 9:31 57109:46 586 NULL 13 18 Tue 0 08:35:27 18:02:17 20:09 20:07:09 0 11:32 692 NULL 14 Wed 30 :00:54 18:12:38 NULL 18:12:38 0 9:12 55211月18日无效18:47:33 0 9:48 588

Answer 1

如果最多有3对列，则只需使用条件聚合：

select userid, checkdate,
       max(case when seqnum = 1 then thetime end) as timein1,
       max(case when seqnum = 2 then thetime end) as timeout1,
       max(case when seqnum = 3 then thetime end) as timein2,
       max(case when seqnum = 4 then thetime end) as timeout2,
       max(case when seqnum = 5 then thetime end) as timein3,
       max(case when seqnum = 6 then thetime end) as timeout3
from (select t.*,
             convert(date, checktime) as checkdate,
             cast(checktime as time) as thetime,
             row_number() over (partition by userid, convert(date, checktime) order by checktime) as seqnum
      from t
     ) t
group by userid, checkdate
order by userid, checkdate;

Answer 2

你可以用枢轴来满足你的需要。而且，似乎你的时间在每一天都很特殊。因此，您需要动态地查找时间列名。我已经准备了一些关于你需要的问题，你可以用它：

DROP TABLE #UserCheck
CREATE TABLE #UserCheck
(
    userid      INT
    ,checktime  DATETIME
)
INSERT INTO #UserCheck
VALUES
(2336,'2018-11-01 08:28:20.000')
,(2336,'2018-11-01 13:27:18.000')
,(2336,'2018-11-01 13:31:12.000')
,(2336,'2018-11-01 18:03:57.000')
,(2336,'2018-11-04 07:59:09.000')
,(2336,'2018-11-04 13:10:58.000')
,(2336,'2018-11-04 13:17:46.000')
,(2336,'2018-11-04 17:58:03.000')
,(2336,'2018-11-05 08:08:07.000')
,(2336,'2018-11-05 13:10:13.000')
,(2336,'2018-11-05 13:14:12.000')
,(2336,'2018-11-05 17:58:58.000')
,(2336,'2018-11-05 17:59:02.000')
,(2336,'2018-11-06 07:40:51.000')
,(2336,'2018-11-06 13:09:48.000')
,(2336,'2018-11-06 13:14:30.000')
,(2336,'2018-11-06 17:55:07.000')
,(2336,'2018-11-07 07:53:18.000')
,(2336,'2018-11-07 13:49:19.000')
,(2336,'2018-11-07 13:53:16.000')
,(2336,'2018-11-07 18:02:12.000')
,(2336,'2018-11-08 07:45:14.000')
,(2336,'2018-11-08 13:18:28.000')
,(2336,'2018-11-08 13:21:59.000')
,(2336,'2018-11-08 18:00:04.000');

drop table #temp; 
SELECT  userid
        ,cast(checktime as date) [CheckDate]
        ,'timein'+CAST(ROW_NUMBER() OVER(PARTITION BY userid,cast(checktime as date) ORDER BY userid,cast(checktime as date)) AS varchar) AS Seq
        ,cast(checktime as time)  [CheckTime] 
INTO #Temp
FROM #UserCheck

DECLARE @DynamicColumns VARCHAR (MAX)
DECLARE @SQLString      VARCHAR (MAX)

   SET @DynamicColumns=STUFF((SELECT distinct ',' + QUOTENAME(Seq) 
                    from  #Temp  
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @SQLString='
SELECT * 
FROM 
    (   SELECT * 
        FROM #Temp
    ) Temp
    PIVOT 
        (
            MAX(CheckTime)
            FOR Seq in ('+@DynamicColumns+')
        ) P'

EXEC(@SQLString)