Multiple break in and out calculation sql-server
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 asked Nov 20 '18 at 7:51
MoizMoiz
133
add a comment |
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 asked Nov 20 '18 at 7:51
MoizMoiz
133
1
please show how does the expected result looks like
– Squirrel
Nov 20 '18 at 7:55
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
Nov 20 '18 at 8:05
There is no any flag column to specify which is IN and OUT
– Sreenu131
Nov 20 '18 at 8:05
no there is none.
– Moiz
Nov 20 '18 at 8:08
add a comment |
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 asked Nov 20 '18 at 7:51
MoizMoiz
133
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 sql
sql-server tsql asked Nov 20 '18 at 7:51
MoizMoiz
133
asked Nov 20 '18 at 7:51
MoizMoiz
133
edited Nov 21 '18 at 8:17
Moiz
asked Nov 20 '18 at 7:51
MoizMoiz
133
asked Nov 20 '18 at 7:51
MoizMoiz
133
asked Nov 20 '18 at 7:51
MoizMoiz
133
133
1
please show how does the expected result looks like
– Squirrel
Nov 20 '18 at 7:55
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
Nov 20 '18 at 8:05
There is no any flag column to specify which is IN and OUT
– Sreenu131
Nov 20 '18 at 8:05
no there is none.
– Moiz
Nov 20 '18 at 8:08
add a comment |
1
please show how does the expected result looks like
– Squirrel
Nov 20 '18 at 7:55
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
Nov 20 '18 at 8:05
There is no any flag column to specify which is IN and OUT
– Sreenu131
Nov 20 '18 at 8:05
no there is none.
– Moiz
Nov 20 '18 at 8:08
1
1
please show how does the expected result looks like
– Squirrel
Nov 20 '18 at 7:55
please show how does the expected result looks like
– Squirrel
Nov 20 '18 at 7:55
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
Nov 20 '18 at 8:05
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
Nov 20 '18 at 8:05
There is no any flag column to specify which is IN and OUT
– Sreenu131
Nov 20 '18 at 8:05
There is no any flag column to specify which is IN and OUT
– Sreenu131
Nov 20 '18 at 8:05
no there is none.
– Moiz
Nov 20 '18 at 8:08
no there is none.
– Moiz
Nov 20 '18 at 8:08
add a comment |
2 Answers
2
active
oldest
votes
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;
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
Nov 20 '18 at 12:56
add a comment |
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)
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
Nov 20 '18 at 8:46
@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
Nov 20 '18 at 9:22
add a comment |
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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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;
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
Nov 20 '18 at 12:56
add a comment |
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;
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
Nov 20 '18 at 12:56
add a comment |
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;
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
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;
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
answered Nov 20 '18 at 12:26
Gordon LinoffGordon Linoff
777k35306409
777k35306409
Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
Nov 20 '18 at 12:56
add a comment |
Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
Nov 20 '18 at 12:56
Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
Nov 20 '18 at 12:56
Thanks a Lot Gordon pointing me to right direction thanks i will post the outcome soon.
– Moiz
Nov 20 '18 at 12:56
add a comment |
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)
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
Nov 20 '18 at 8:46
@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
Nov 20 '18 at 9:22
add a comment |
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)
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
Nov 20 '18 at 8:46
@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
Nov 20 '18 at 9:22
add a comment |
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)
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
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)
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
edited Nov 20 '18 at 8:57
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
answered Nov 20 '18 at 8:40
Zeki GumusZeki Gumus
1,400212
1,400212
But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
Nov 20 '18 at 8:46
@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
Nov 20 '18 at 9:22
add a comment |
But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
Nov 20 '18 at 8:46
@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
Nov 20 '18 at 9:22
But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
Nov 20 '18 at 8:46
But O/p needs both TimeIn AND TimeOut Columns to be showed dynamically
– Sreenu131
Nov 20 '18 at 8:46
@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
Nov 20 '18 at 9:22
@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
Nov 20 '18 at 9:22
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
1
please show how does the expected result looks like
– Squirrel
Nov 20 '18 at 7:55
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
Nov 20 '18 at 8:05
There is no any flag column to specify which is IN and OUT
– Sreenu131
Nov 20 '18 at 8:05
no there is none.
– Moiz
Nov 20 '18 at 8:08