Hello Chris,

We are reached almost near to our result,

But i can see two issues which is occurring when shift schedule changing time,

my query example for one person_num 20345,

action_idCreation_Date event_name

1684882016-07-21 15:51:48.697CLOCK_IN

1688252016-07-22 00:06:48.697CLOCK_OUT

1688702016-07-22 10:07:48.697CLOCK_IN

1689142016-07-22 20:12:48.697CLOCK_OUT

1689452016-07-23 05:49:48.697CLOCK_IN

1692032016-07-23 16:07:48.697CLOCK_OUT

1695862016-07-24 07:52:48.697CLOCK_IN

1698102016-07-24 16:09:48.697CLOCK_OUT

1753432016-07-25 07:50:48.700CLOCK_IN

1755992016-07-25 16:08:48.700CLOCK_OUT

1760602016-07-26 07:54:48.700CLOCK_IN

1762202016-07-26 16:03:48.700CLOCK_OUT

1765562016-07-27 07:50:48.700CLOCK_IN

1767802016-07-27 16:07:48.700CLOCK_OUT

1770352016-07-28 05:54:48.700CLOCK_IN

1773402016-07-28 16:11:48.700CLOCK_OUT

1773582016-07-28 16:16:48.700CLOCK_OUT

1794702016-07-30 23:50:48.700CLOCK_IN

1797622016-07-31 08:02:48.700CLOCK_OUT

1802722016-07-31 23:55:48.700CLOCK_IN

1805982016-08-01 08:13:48.700CLOCK_OUT

1811532016-08-02 00:19:48.700CLOCK_IN

1814272016-08-02 08:04:48.700CLOCK_OUT

1819692016-08-02 23:56:48.700CLOCK_IN

1821682016-08-03 08:00:48.700CLOCK_OUT

1828072016-08-03 23:57:48.700CLOCK_IN

1830962016-08-04 08:03:48.700CLOCK_OUT

1836112016-08-04 23:55:48.700CLOCK_IN

1836792016-08-05 10:05:48.700CLOCK_OUT

1837202016-08-05 19:50:48.700CLOCK_IN

1838092016-08-06 06:10:48.700CLOCK_OUT

1841582016-08-06 15:53:48.700CLOCK_IN

1844692016-08-07 00:06:48.700CLOCK_OUT

1849192016-08-07 15:53:48.700CLOCK_IN

1852782016-08-08 00:06:48.700CLOCK_OUT

1857322016-08-08 15:54:48.700CLOCK_IN

1861032016-08-09 00:05:48.700CLOCK_OUT

1874812016-08-10 15:55:48.703CLOCK_IN

1878452016-08-11 00:06:48.703CLOCK_OUT

1883402016-08-11 15:51:48.703CLOCK_IN

1885372016-08-12 00:04:48.703CLOCK_OUT

1885752016-08-12 09:56:48.703CLOCK_IN

1886162016-08-12 20:03:48.703CLOCK_OUT

1886452016-08-13 06:00:48.703CLOCK_IN

1888972016-08-13 16:07:48.703CLOCK_OUT

1892132016-08-14 07:51:48.703CLOCK_IN

1894152016-08-14 16:13:48.703CLOCK_OUT

1895432016-08-14 18:45:48.703CLOCK_IN

1895792016-08-14 20:29:48.703CLOCK_OUT

1897582016-08-15 07:53:48.703CLOCK_IN

1899952016-08-15 17:08:48.703CLOCK_OUT

1902952016-08-16 07:48:48.703CLOCK_IN

1904922016-08-16 16:15:48.703CLOCK_OUT

1908172016-08-17 07:47:48.703CLOCK_IN

1924762016-08-17 16:07:48.703CLOCK_OUT

1930552016-08-18 07:50:48.703CLOCK_IN

1933482016-08-18 16:11:48.703CLOCK_OUT

1945152016-08-20 23:55:48.703CLOCK_IN

1948682016-08-21 08:12:48.707CLOCK_OUT

1953232016-08-21 23:59:48.707CLOCK_IN

1955402016-08-22 08:18:48.707CLOCK_OUT

1962102016-08-22 23:57:48.707CLOCK_IN

1965552016-08-23 08:10:48.707CLOCK_OUT

1970482016-08-23 23:53:48.707CLOCK_IN

1974082016-08-24 08:10:48.707CLOCK_OUT

1979742016-08-24 23:57:48.707CLOCK_IN

1982772016-08-25 08:02:48.707CLOCK_OUT

1987772016-08-25 23:55:48.707CLOCK_IN

1988602016-08-26 10:10:48.707CLOCK_OUT

1989192016-08-26 19:52:48.707CLOCK_IN

1990262016-08-27 06:11:48.707CLOCK_OUT

1994612016-08-27 15:55:48.707CLOCK_IN

1997082016-08-28 00:07:48.707CLOCK_OUT

2002532016-08-28 15:54:48.707CLOCK_IN

2006092016-08-29 00:01:48.707CLOCK_OUT

2011042016-08-29 15:53:48.707CLOCK_IN

2014392016-08-30 00:03:48.707CLOCK_OUT

2019022016-08-30 15:50:48.710CLOCK_IN

2022572016-08-31 00:03:48.710CLOCK_OUT

2027472016-08-31 15:53:48.710CLOCK_IN

2030642016-09-01 00:03:48.710CLOCK_OUT

2035522016-09-01 15:57:48.710CLOCK_IN

2039252016-09-02 00:07:48.710CLOCK_OUT

2040222016-09-02 19:58:48.710CLOCK_IN

2041002016-09-03 06:09:48.710CLOCK_OUT

2045002016-09-03 15:57:48.710CLOCK_IN

2047392016-09-03 22:08:48.710CLOCK_OUT

2052272016-09-04 08:34:48.710CLOCK_IN

2054392016-09-04 16:07:48.710CLOCK_OUT

2060382016-09-05 07:56:48.710CLOCK_IN

2062522016-09-05 13:47:48.710CLOCK_OUT

As per your query, When shift changing one day clock is missing with time negative number with 4 hours less showing in the total calculation.

same above person result with your query.

Creation_Date DATE_IN1 DATE_OUT1 CLOCK_IN1 CLOCK_OUT1

2016-07-212016-07-212016-07-2215:51:30.660000000:06:30.6500000NULLNULLNULLNULLNULLNULL8.250000

2016-07-222016-07-222016-07-2210:07:30.660000020:12:30.6500000NULLNULLNULLNULLNULLNULL10.083333

2016-07-232016-07-232016-07-2305:49:30.660000016:07:30.6500000NULLNULLNULLNULLNULLNULL10.300000

2016-07-242016-07-242016-07-2407:52:30.660000016:09:30.6500000NULLNULLNULLNULLNULLNULL8.283333

2016-07-252016-07-252016-07-2507:50:30.660000016:08:30.6500000NULLNULLNULLNULLNULLNULL8.300000

2016-07-262016-07-262016-07-2607:54:30.660000016:03:30.6500000NULLNULLNULLNULLNULLNULL8.150000

2016-07-272016-07-272016-07-2707:50:30.660000016:07:30.6500000NULLNULLNULLNULLNULLNULL8.283333

2016-07-282016-07-282016-07-2805:54:30.660000016:11:30.6500000NULLNULLNULLNULLNULLNULL10.283333

2016-07-312016-07-302016-07-3123:50:30.660000008:02:30.6500000NULLNULLNULLNULLNULLNULL8.200000

2016-08-012016-07-312016-08-0123:55:30.660000008:13:30.6500000NULLNULLNULLNULLNULLNULL8.300000

2016-08-022016-08-022016-08-0200:19:30.660000008:04:30.6530000NULLNULLNULLNULLNULLNULL7.750000

2016-08-032016-08-022016-08-0323:56:30.660000008:00:30.6530000NULLNULLNULLNULLNULLNULL8.066666

2016-08-042016-08-032016-08-0423:57:30.663000008:03:30.6530000NULLNULLNULLNULLNULLNULL8.100000

2016-08-052016-08-042016-08-0523:55:30.663000010:05:30.6530000NULLNULLNULLNULLNULLNULL10.166666

2016-08-062016-08-062016-08-0615:53:30.663000006:10:30.6530000NULLNULLNULLNULLNULLNULL-9.716666

2016-08-072016-08-072016-08-0815:53:30.663000000:06:30.6530000NULLNULLNULLNULLNULLNULL8.216666

2016-08-082016-08-082016-08-0915:54:30.663000000:05:30.6530000NULLNULLNULLNULLNULLNULL8.183333

2016-08-102016-08-102016-08-1115:55:30.663000000:06:30.6530000NULLNULLNULLNULLNULLNULL8.183333

2016-08-112016-08-112016-08-1215:51:30.663000000:04:30.6530000NULLNULLNULLNULLNULLNULL8.216666

2016-08-122016-08-122016-08-1209:56:30.663000020:03:30.6530000NULLNULLNULLNULLNULLNULL10.116666

2016-08-132016-08-132016-08-1306:00:30.663000016:07:30.6530000NULLNULLNULLNULLNULLNULL10.116666

2016-08-142016-08-142016-08-1407:51:30.663000016:13:30.653000018:45: 20:29 NULLNULLNULLNULL10.100000

2016-08-152016-08-152016-08-1507:53:30.663000017:08:30.6530000NULLNULLNULLNULLNULLNULL9.250000

2016-08-162016-08-162016-08-1607:48:30.663000016:15:30.6530000NULLNULLNULLNULLNULLNULL8.450000

2016-08-172016-08-172016-08-1707:47:30.663000016:07:30.6530000NULLNULLNULLNULLNULLNULL8.333333

2016-08-182016-08-182016-08-1807:50:30.663000016:11:30.6530000NULLNULLNULLNULLNULLNULL8.350000

2016-08-212016-08-202016-08-2123:55:30.663000008:12:30.6570000NULLNULLNULLNULLNULLNULL8.283333

2016-08-222016-08-212016-08-2223:59:30.663000008:18:30.6570000NULLNULLNULLNULLNULLNULL8.316666

2016-08-232016-08-222016-08-2323:57:30.667000008:10:30.6570000NULLNULLNULLNULLNULLNULL8.216666

2016-08-242016-08-232016-08-2423:53:30.667000008:10:30.6570000NULLNULLNULLNULLNULLNULL8.283333

2016-08-252016-08-242016-08-2523:57:30.667000008:02:30.6570000NULLNULLNULLNULLNULLNULL8.083333

2016-08-262016-08-252016-08-2623:55:30.667000010:10:30.6570000NULLNULLNULLNULLNULLNULL10.250000

2016-08-272016-08-272016-08-2715:55:30.667000006:11:30.6570000NULLNULLNULLNULLNULLNULL-9.733333

2016-08-282016-08-282016-08-2915:54:30.667000000:01:30.6570000NULLNULLNULLNULLNULLNULL8.116666

2016-08-292016-08-292016-08-3015:53:30.667000000:03:30.6570000NULLNULLNULLNULLNULLNULL8.166666

2016-08-302016-08-302016-08-3115:50:30.667000000:03:30.6570000NULLNULLNULLNULLNULLNULL8.216666

2016-08-312016-08-312016-09-0115:53:30.667000000:03:30.6570000NULLNULLNULLNULLNULLNULL8.166666

2016-09-012016-09-012016-09-0215:57:30.667000000:07:30.6570000NULLNULLNULLNULLNULLNULL8.166666

2016-09-032016-09-032016-09-0315:57:30.667000006:09:30.6570000NULLNULLNULLNULLNULLNULL-9.800000

2016-09-042016-09-042016-09-0408:34:30.667000016:07:30.6570000NULLNULLNULLNULLNULLNULL7.550000

2016-09-052016-09-052016-09-0507:56:30.667000013:47:30.6600000NULLNULLNULLNULLNULLNULL5.850000

What is your suggestion..