trying to get duration by filtered categories from earliest and latest times

[fstupot]

hi guys x,

hope you can help.

i am trying to look at productivity using when someone first records a timestamp for that day as well as filtered by work type. it is driving me insane!

the data is attached. the yellow columns show "feat insp date" which shows a time stamp whenever a job is logged. i want to be able to see the duration for each "type" of job and also carried out by "officer code" to look at productivity for dates etc......please help!!!!

site_code	asset_number	asset_id	feature_insp_date	day	feat_cent_east	feat_cent_north	type	tmr	month	officer_code	site_name
37600478	10016	67784	11/08/2021 09:47	11-Aug	391931	323115	Annual Empty	Stop and Go	08-Aug	SGC2	TENTERBANKS
37600749	10006	77735	11/08/2021 10:32	11-Aug	392608	320962	Triennial Empty	Mobile Works	08-Aug	SGC3	ASTORIA DRIVE
37600749	10018	77857	11/08/2021 13:15	11-Aug	392587	320839	Triennial Empty	Mobile Works	08-Aug	SGC3	ASTORIA DRIVE
23401328	10040	157077	12/08/2021 10:06	12-Aug	420469	301904	Annual Empty	Stop and Go	08-Aug	SGC6	LICHFIELD STREET
37600373	10103	67726	12/08/2021 10:30	12-Aug	391544	322642	Annual Empty	Stop and Go	08-Aug	SGC2	NEWPORT ROAD
23400501	10004	156968	12/08/2021 10:50	12-Aug	420184	301308	Annual Empty	Stop and Go	08-Aug	SGC6	COLESHILL ROAD
37600923	10057	94192	12/08/2021 11:04	12-Aug	383439	330564	Annual Empty	Stop and Go	08-Aug	MWEB	SWYNNERTON ROAD
37600373	10042	67489	12/08/2021 12:14	12-Aug	391140	322287	Annual Empty	Stop and Go	08-Aug	SGC2	NEWPORT ROAD
37600373	10018	67437	12/08/2021 13:43	12-Aug	391323	322484	Annual Empty	Stop and Go	08-Aug	SGC2	NEWPORT ROAD
37600386	10019	77660	12/08/2021 14:30	12-Aug	392421	321424	Triennial Empty	Mobile Works	08-Aug	SGC3	MERRIVALE ROAD
37600364	10009	80298	13/08/2021 08:46	13-Aug	392427	322387	Triennial Empty	Mobile Works	08-Aug	SGC3	MEYRICK ROAD
37600364	10001	80072	13/08/2021 08:48	13-Aug	392324	322467	Triennial Empty	Mobile Works	08-Aug	SGC3	MEYRICK ROAD
36700958	10007	51673	16/08/2021 13:11	16-Aug	386991	303866	Adhoc Empty	Road Closure	08-Aug	SGC5	SANDY LANE
23400318	10003	39816	17/08/2021 08:52	17-Aug	403842	310439	Biennial Empty	Stop and Go	08-Aug	SGC6	IRONSTONE ROAD
37600406	10013	66903	17/08/2021 12:04	17-Aug	393332	322055	Annual Empty	Stop and Go	08-Aug	SGC2	QUEENSVILLE BRIDGE
12900460	15003		17/08/2021 12:31	17-Aug	423243.6	325472.5	Annual Empty	Road Closure	08-Aug	ABAT	KITLING GREAVES LANE
37700517	10062	56495	17/08/2021 12:47	17-Aug	396609	354643	Annual Empty	Stop and Go	08-Aug	SGC1	LADDEREDGE
37700986	10012	56456	17/08/2021 12:52	17-Aug	395995	354614	Annual Empty	Stop and Go	08-Aug	SGC1	LEEK ROAD
37700986	10021	56584	17/08/2021 12:53	17-Aug	395947	354568	Annual Empty	Stop and Go	08-Aug	SGC1	LEEK ROAD
37701013	10027	56971	17/08/2021 13:03	17-Aug	393706	354365	Annual Empty	Stop and Go	08-Aug	SGC1	LEEK ROAD
37701013	10019	56742	17/08/2021 13:04	17-Aug	393638	354336	Annual Empty	Stop and Go	08-Aug	SGC1	LEEK ROAD

[v-chenwuz-msft]

Hi @fstupot ,

Can you explain how it is calculated? Please give the expected result for your example data. For example, how is a category with only one point in time calculated?

Best Regards

Community Support Team _ chenwu zhu