Calculate sections of time

Transact-SQL
1

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 
[Results](https://drive.google.com/file/d/1pgowz-nVs3bEqGPCkClJjvQ5xKRXeg8W/view)
2

hi

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

does this look like something you want

image
image1407×393 22 KB

3

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
,CAST(DATEADD(SECOND,
 SUM(CASE WHEN SystemType = 'P' AND CreatedDate < LastPick THEN DATEDIFF(SECOND, CreatedDate, LeadCreatedDate) ELSE 0 END)
 , 0) AS time(0)) AS TotalParkTime
,CAST(DATEADD(SECOND,
 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
4

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