Multiple break in and out calculation sql-server











up vote
2
down vote

favorite












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










share|improve this question









New contributor




Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 1




    please show how does the expected result looks like
    – Squirrel
    yesterday










  • Hi Squirrel thanks for looking in my question below is the output i am trying to achieve 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
    – Moiz
    yesterday












  • There is no any flag column to specify which is IN and OUT
    – Sreenu131
    yesterday












  • no there is none.
    – Moiz
    yesterday















up vote
2
down vote

favorite












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










share|improve this question









New contributor




Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 1




    please show how does the expected result looks like
    – Squirrel
    yesterday










  • Hi Squirrel thanks for looking in my question below is the output i am trying to achieve 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
    – Moiz
    yesterday












  • There is no any flag column to specify which is IN and OUT
    – Sreenu131
    yesterday












  • no there is none.
    – Moiz
    yesterday













up vote
2
down vote

favorite









up vote
2
down vote

favorite











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










share|improve this question









New contributor




Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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







sql sql-server tsql






share|improve this question









New contributor




Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 19 hours ago





















New contributor




Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked yesterday









Moiz

133




133




New contributor




Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Moiz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 1




    please show how does the expected result looks like
    – Squirrel
    yesterday










  • Hi Squirrel thanks for looking in my question below is the output i am trying to achieve 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
    – Moiz
    yesterday












  • There is no any flag column to specify which is IN and OUT
    – Sreenu131
    yesterday












  • no there is none.
    – Moiz
    yesterday














  • 1




    please show how does the expected result looks like
    – Squirrel
    yesterday










  • Hi Squirrel thanks for looking in my question below is the output i am trying to achieve 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
    – Moiz
    yesterday












  • There is no any flag column to specify which is IN and OUT
    – Sreenu131
    yesterday












  • no there is none.
    – Moiz
    yesterday








1




1




please show how does the expected result looks like
– Squirrel
yesterday




please show how does the expected result looks like
– Squirrel
yesterday












Hi Squirrel thanks for looking in my question below is the output i am trying to achieve 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
– Moiz
yesterday






Hi Squirrel thanks for looking in my question below is the output i am trying to achieve 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
– Moiz
yesterday














There is no any flag column to specify which is IN and OUT
– Sreenu131
yesterday






There is no any flag column to specify which is IN and OUT
– Sreenu131
yesterday














no there is none.
– Moiz
yesterday




no there is none.
– Moiz
yesterday












2 Answers
2






active

oldest

votes

















up vote
0
down vote



accepted










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;





share|improve this answer





















  • Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
    – Moiz
    yesterday


















up vote
0
down vote













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)





share|improve this answer























  • But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
    – Sreenu131
    yesterday










  • @Zeki Gumus, thanks for the query i will try to use the logic with dynamic data and let you know what is the outcome.
    – Moiz
    yesterday











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});






Moiz is a new contributor. Be nice, and check out our Code of Conduct.










 

draft saved


draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53388441%2fmultiple-break-in-and-out-calculation-sql-server%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
0
down vote



accepted










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;





share|improve this answer





















  • Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
    – Moiz
    yesterday















up vote
0
down vote



accepted










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;





share|improve this answer





















  • Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
    – Moiz
    yesterday













up vote
0
down vote



accepted







up vote
0
down vote



accepted






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;





share|improve this answer












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;






share|improve this answer












share|improve this answer



share|improve this answer










answered yesterday









Gordon Linoff

744k32285390




744k32285390












  • Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
    – Moiz
    yesterday


















  • Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
    – Moiz
    yesterday
















Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
yesterday




Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
yesterday












up vote
0
down vote













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)





share|improve this answer























  • But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
    – Sreenu131
    yesterday










  • @Zeki Gumus, thanks for the query i will try to use the logic with dynamic data and let you know what is the outcome.
    – Moiz
    yesterday















up vote
0
down vote













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)





share|improve this answer























  • But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
    – Sreenu131
    yesterday










  • @Zeki Gumus, thanks for the query i will try to use the logic with dynamic data and let you know what is the outcome.
    – Moiz
    yesterday













up vote
0
down vote










up vote
0
down vote









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)





share|improve this answer














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)






share|improve this answer














share|improve this answer



share|improve this answer








edited yesterday

























answered yesterday









Zeki Gumus

2336




2336












  • But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
    – Sreenu131
    yesterday










  • @Zeki Gumus, thanks for the query i will try to use the logic with dynamic data and let you know what is the outcome.
    – Moiz
    yesterday


















  • But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
    – Sreenu131
    yesterday










  • @Zeki Gumus, thanks for the query i will try to use the logic with dynamic data and let you know what is the outcome.
    – Moiz
    yesterday
















But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
yesterday




But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
yesterday












@Zeki Gumus, thanks for the query i will try to use the logic with dynamic data and let you know what is the outcome.
– Moiz
yesterday




@Zeki Gumus, thanks for the query i will try to use the logic with dynamic data and let you know what is the outcome.
– Moiz
yesterday










Moiz is a new contributor. Be nice, and check out our Code of Conduct.










 

draft saved


draft discarded


















Moiz is a new contributor. Be nice, and check out our Code of Conduct.













Moiz is a new contributor. Be nice, and check out our Code of Conduct.












Moiz is a new contributor. Be nice, and check out our Code of Conduct.















 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53388441%2fmultiple-break-in-and-out-calculation-sql-server%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

Calculate evaluation metrics using cross_val_predict sklearn

Insert data from modal to MySQL (multiple modal on website)