chris find follow,

2016-07-26 03:57:34.0002016-07-26 06:57:47.6902016-07-2600010079 EVENSCLOCK_IN1

2016-07-26 03:57:41.0002016-07-26 06:57:47.6902016-07-2600010079EVENSCLOCK_IN2

2016-07-26 14:08:43.0002016-07-26 17:08:47.6902016-07-2600010079EVENSCLOCK_IN3

2016-07-27 03:56:55.0002016-07-27 06:56:47.6902016-07-2700010079EVENSCLOCK_IN4

2016-07-27 14:35:40.0002016-07-27 17:35:47.6902016-07-2700010079EVENSCLOCK_IN5

2016-07-28 03:55:11.0002016-07-28 06:55:47.6902016-07-2800010079EVENSCLOCK_IN6

2016-07-28 13:57:37.0002016-07-28 16:57:47.6902016-07-2800010079EVENSCLOCK_IN7

2016-07-26 03:57:38.0002016-07-26 06:57:47.6902016-07-2600010079EVENSCLOCK_OUT1

2016-07-26 04:41:09.0002016-07-26 07:41:47.6902016-07-2600010079EVENSCLOCK_OUT2

2016-07-26 15:06:48.0002016-07-26...

Chris,

Did you apply UTCTOLOCAL converter ?

Our table creation_date is UTC datetime format which i am converting to local datetime format, to over come this datetime issue i did applied following...

chris pls note that ,creation_Date is UTC datettime. which i am converting with this scalar valued function ,

USE [test]

GO

/****** Object: UserDefinedFunction [dbo].[UtcToLocal] Script Date: 10/19/2016...

TABLE OUTPUT

query output,

hello chris as per you query,

2016-07-2100010079JOHN STEVENS13:52:56.000000014:30:33.000000013:53:02.000000004:36:30.0000000NULLNULLNULLNULL39.350000

total hours shows 39:35 which is not correct.

what you think?

Hello chris,

Thanks but bit diffcult to come desired output due to UTCTOLOCAL .

i am trying but failed but in your previous information i was able to add dbo.Utctolocal. but in...

First Issue is:

my table dbo.test does not have any control on clock event(Clockin or Clocout) and table will accept whatever our Bio Metric machines throws the data. please note that...

chris,

Got on suggestion, table column name is shift starts from 1 to 10

If it is 1 = 8 am to 4pm

if it is 2 =4 pm to 12 am

if it...

Ray agreed on cross post but i disagree on your statement where you mention that solution is already available for recent query.

since both requirement differ from each other.

BR

Hello Ray,

Both are different query with different requirement but table pattern is same.

BR.

Hello Ray,

Both are different query with different requirement but table pattern is same.

BR.

chris thanks for your useful query . but i am seeing timespan issue. how to overcome this can you help?

WITH Prep AS

(SELECT CAST(dbo.UtcToLocal(creation_date) AS date) AS creation_date, CAST(dbo.UtcToLocal(creation_date) AS time)...

sscrazy,

thanks its worked.

but some time we do have more than 2 clock in and clock out and yours cover only 2 clock in and clock out. Could you assist to...

Hello Alan,

Its Good One ...Thanks.

Also how to achieve the following,

Req 1 : 11:38:32.000000011:38:27.0000000 (.0000000 decimal need to be removed)

Req 2 : creation date...

Hi,

Find below my table ,

action_idperson_numperson_idcreation_datebadge_numevent_nameSHIFTmiddle_namefull_namepreffered_name

16844810148164777/21/16 11:38 AM9372CLOCK_IN1DAYRICHARDFINANCE

16844910148164777/21/16 3:56 PM9372CLOCK_OUT1DAYRICHARDFINANCE

16845010148164777/23/16 7:41 AM9372CLOCK_IN1DAYRICHARDFINANCE

16845110148164777/23/16 5:22 PM9372CLOCK_OUT1DAYRICHARDFINANCE

16845210148164777/24/16 5:42 AM9372CLOCK_IN1DAYRICHARDFINANCE

16845310148164777/24/16 4:45 PM9372CLOCK_OUT1DAYRICHARDFINANCE

16845410148164777/25/16 5:46 AM9372CLOCK_IN1DAYRICHARDFINANCE

16845510148164777/25/16 5:57 PM9372CLOCK_OUT1DAYRICHARDFINANCE

16845610148164777/26/16 5:45 AM9372CLOCK_IN1DAYRICHARDFINANCE

16845710148164777/26/16 3:42 PM9372CLOCK_OUT1DAYRICHARDFINANCE

16845810148164777/27/16 6:33 AM9372CLOCK_IN1DAYRICHARDFINANCE

16845910148164777/27/16 6:48 PM9372CLOCK_OUT1DAYRICHARDFINANCE

16846010148164777/28/16 5:20...