Ytukyg

Hi I would like to calculate Multiple in and out time group by date.

Below is the current table data

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

Expected result of the query

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

Created Query with the help of Gordon logic

`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`

Output is

DAYDATE DAYPART LATETIMEIN TIMEIN2 BreakOut1 BreakIn1 TIMEOUT2 EARLYTIMEOUT WORKhrs WRSMIN Remarks
01 Nov 18 Thu 24 08:54:59 17:27:30 NULL 17:27:30 33 8:33 513 NULL
02 Nov 18 Fri NULL NULL NULL NULL NULL NULL NULL NULL NULL
03 Nov 18 Sat NULL NULL NULL NULL NULL NULL NULL NULL NULL
04 Nov 18 Sun 18 08:48:43 NULL NULL 0 0:0 0 NULL
05 Nov 18 Mon 21 08:51:45 18:23:19 NULL 18:23:19 0 9:32 572 NULL
06 Nov 18 Tue 19 08:49:45 18:27:15 NULL 18:27:15 0 9:38 578 NULL
07 Nov 18 Wed 28 08:58:16 18:21:30 NULL 18:21:30 0 9:23 563 NULL
08 Nov 18 Thu 0 08:42:52 18:13:03 NULL 18:13:03 0 9:31 571 NULL
09 Nov 18 Fri NULL NULL NULL NULL NULL NULL NULL NULL NULL
10 Nov 18 Sat NULL NULL NULL NULL NULL NULL NULL NULL NULL
11 Nov 18 Sun 48 09:18:30 19:01:48 NULL 19:01:48 0 9:43 583 NULL
12 Nov 18 Mon 0 08:38:51 18:24:08 NULL 18:24:08 0 9:46 586 NULL
13 Nov 18 Tue 0 08:35:27 18:02:17 20:07:09 20:07:09 0 11:32 692 NULL
14 Nov 18 Wed 30 09:00:54 18:12:38 NULL 18:12:38 0 9:12 552 NULL
15 Nov 18 Thu 0 08:08:50 18:29:32 NULL 18:29:32 0 10:21 621 NULL
16 Nov 18 Fri NULL NULL NULL NULL NULL NULL NULL NULL NULL
17 Nov 18 Sat NULL NULL NULL NULL NULL NULL NULL NULL NULL
18 Nov 18 Sun NULL NULL NULL NULL NULL NULL NULL NULL Birthday of Prophet Mohammad (PBUH)
19 Nov 18 Mon 23 08:53:35 18:08:07 NULL 18:08:07 0 9:15 555 NULL
20 Nov 18 Tue 29 08:59:02 18:47:33 NULL 18:47:33 0 9:48 588 NULL

If you have at most 3 pairs of columns, you can just use conditional aggregation:

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;

you can use PIVOT for your need. Also, it seems your times specific for each days. So you need to find the time column names dynamically. I have prepared some queries with your needs, you can use it:

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)