# Calculate sections of time

I have a some scan times from a warehouse job , the table shows where the job was parked for breaks / overnight , I cant think of a logical way to just calculate the time between the breaks . The total time spent is 54 minutes but the actual pick time is only 9mins , As always any pointer greatly appreciated

``````Create table #packpark
( Shipment bigint,
Shipmentid Nvarchar (20),
CreatedUser Nvarchar (20),
TableName Nvarchar (50),
CreatedDate Datetime ,
AuditCode Nvarchar (50),
QtyPicked decimal (13,2),
SystemType Nvarchar (20)
)

INSERT INTO #packpark
VALUES

('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:09:00.723',	'Picking Started',	'0.00000',	'S'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:09:28.833',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:09:29.380',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:09:29.910',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:09:43.147',	'',	'1.00000',	'N'),
('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:09:48.630',	'Picking Parked',	'0.00000',	'P'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:15:11.587',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:15:12.290',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:15:13.257', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:15:21.570', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:15:37.710', '',		'1.00000',	'N'),
('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:15:43.070',	'Picking Parked',	'0.00000',	'P'),
('849975', 	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:26:23.343', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:26:57.907', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:27:12.780', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:27:13.657',	'',	'1.00000',	'N'),
('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:50:09.320',	'Picking Parked',	'0.00000',	'P')

Select
Shipment
,ShipmentId
,CreatedUser
,Tablename
,CreatedDate
,CONVERT(VARCHAR(15), CONVERT(TIME , CreatedDate - LAG(CreatedDate) OVER (ORDER BY CreatedDate)))as time_hhmm
,AuditCode
,QtyPicked
,SystemType

from
#packpark

drop table #packpark

Results look like this

``````

hi

I cant think of a logical way to just calculate the time between the breaks . ?

does this look like something you want

To me, the actual pick time was only 2 min., unless you want to include the time between the last pick and the final park.

``````
;WITH cte_packpark AS (
SELECT *, LEAD(CreatedDate) OVER(PARTITION BY Shipment ORDER BY CreatedDate) AS LeadCreatedDate,
MAX(CASE WHEN SystemType = 'N' THEN CreatedDate END) OVER(PARTITION BY Shipment) AS LastPick
FROM #packpark
)
--select * from cte_packpark order by shipment, createddate
Select
Shipment
,MAX(ShipmentId) AS ShipmentId
,MAX(CreatedUser) AS CreatedUser
,MIN(CASE WHEN SystemType = 'S' THEN CreatedDate END) AS PickingStarted
,MAX(CASE WHEN SystemType = 'N' THEN CreatedDate END) AS PickingEnded
--,MAX(CASE WHEN SystemType = 'P' THEN CreatedDate END) AS PickingFinal
SUM(CASE WHEN SystemType = 'P' AND CreatedDate < LastPick THEN DATEDIFF(SECOND, CreatedDate, LeadCreatedDate) ELSE 0 END)
, 0) AS time(0)) AS TotalParkTime
DATEDIFF(SECOND, MIN(CASE WHEN SystemType = 'S' THEN CreatedDate END), MAX(CASE WHEN SystemType = 'N' THEN CreatedDate END))
- SUM(CASE WHEN SystemType = 'P' AND CreatedDate < LastPick THEN DATEDIFF(SECOND, CreatedDate, LeadCreatedDate) ELSE 0 END)
, 0) AS time(0)) AS TotalPickTime
,MAX(QtyPicked) AS QtyPicked
from
cte_packpark
GROUP BY Shipment
``````

Hi

this solution works on a Single Shipment , what I'm now struggling with is when I call all shipments in a date range the LAG calculates from the previous shipment ,I need to work out a way to Loop each shipment into a temp table so it retains its unique shipment time .

``````Create table #packpark
( Shipment bigint,
Shipmentid Nvarchar (20),
CreatedUser Nvarchar (20),
TableName Nvarchar (50),
CreatedDate Datetime ,
AuditCode Nvarchar (50),
QtyPicked decimal (13,2),
SystemType Nvarchar (20)
)

INSERT INTO #packpark
VALUES

('849976',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:09:00.723',	'Picking Started',	'0.00000',	'S'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:09:28.833',	'',	'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:09:29.380',	'',	'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:09:29.910',	'',	'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:09:43.147',	'',	'1.00000',	'N'),
('849976',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:09:48.630',	'Picking Parked',	'0.00000',	'P'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:15:11.587',	'',	'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:15:12.290',	'',	'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:15:13.257', '',		'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:15:21.570', '',		'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:15:37.710', '',		'1.00000',	'N'),
('849976',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:15:43.070',	'Picking Parked',	'0.00000',	'P'),
('849976', 	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:26:23.343', '',		'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:26:57.907', '',		'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:27:12.780', '',		'1.00000',	'N'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:27:13.657',	'',	'1.00000',	'N'),
('849976',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:50:09.320',	'Picking Parked',	'0.00000',	'P'),
('849976',	'SH00216749',	'Jon',	'Picking',	'2023-01-26 10:52:13.657',	'',	'1.00000',	'N'),
('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:08:00.723',	'Picking Started',	'0.00000',	'S'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:10:28.833',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:10:29.380',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:10:29.910',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:10:43.147',	'',	'1.00000',	'N'),
('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:10:48.630',	'Picking Parked',	'0.00000',	'P'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:16:11.587',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:16:12.290',	'',	'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:16:13.257', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:16:21.570', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:16:37.710', '',		'1.00000',	'N'),
('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:17:43.070',	'Picking Parked',	'0.00000',	'P'),
('849975', 	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:27:23.343', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:27:57.907', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:28:12.780', '',		'1.00000',	'N'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:28:13.657',	'',	'1.00000',	'N'),
('849975',	'',	'Jon',	'bdt_PickedItems',	'2023-01-26 10:51:09.320',	'Picking Parked',	'0.00000',	'P'),
('849975',	'SH00216747',	'Jon',	'Picking',	'2023-01-26 10:58:13.657',	'',	'1.00000',	'N')

SELECT
Shipment,
Shipmentid,
CreatedUser,
TableName,
CreatedDate,
CASE WHEN LAG(AuditCode) OVER (ORDER BY CreatedDate) = 'Picking Parked' THEN
'00:00:00.000000'
ELSE
CONVERT(
VARCHAR(15),
CONVERT(
TIME,
CreatedDate
- LAG(CreatedDate) OVER (ORDER BY CreatedDate)
)
)
END AS 'hh:mm:ss:ms',
CASE WHEN LAG(AuditCode) OVER (ORDER BY CreatedDate) = 'Picking Parked' THEN
'000000000'
ELSE
datediff(millisecond,
LAG(CreatedDate) OVER (ORDER BY CreatedDate),createddate)

END AS 'milliseconds',

AuditCode,
QtyPicked,
SystemType
FROM #packpark;

drop table #packpark

``````