<menu path> <link to F1 content>
Ribbon Bar / Launch HTML5 / Launch KPI
Various reports are available to measure a Picker’s efficiency.
| Info |
|---|
If you don’t like reading, you can watch the video. |
| Panel | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
<link to YouTube video> <link to PDF doc on my OneDrive> |
Try it!
None |
Try it!
Picker Statistics Sales Order & SCTs
This report will give you an overview of the pickers lines and QTYs per picker for Sales Orders and SCTs for a specific date range.
SQL Script
| Code Block |
|---|
declare @FromDate Date
declare @ToDate Date
set @FromDate= '2018-01-01'
set @ToDate = '2018-09-01'
SELECT [Owner],SUM(QtyPicked)AS [Total Quantity Picked],SUM(MassPicked) AS [Total Mass Picked],COUNT(*) LinesPicked,
(CASE 0 WHEN 0 THEN 0 ELSE (SUM(MassPicked)/CONVERT(DECIMAL,0)) END + CASE 0 WHEN 0 THEN 0 ELSE (COUNT(*)/CONVERT(DECIMAL,0)) END)/1*100/(DateDiff(DAY, @FromDate, @ToDate)+1) AS [Weighted Ave],
COUNT(DISTINCT(PickingSlipNumber))AS [Completed Picking Slips]
FROM
(SELECT PSI.[Owner],LU.OperatorName,PS.PickingSlipNumber,SourceNumber AS SalesOrder,CustomerName,CustomerPO,InvoiceNumber,StartTime,EndTime,
PSI.StockCode,Qty AS PickingSlipQty,SUM(ISNULL(PR.QtyOrdered,PRA.QtyOrdered)) AS QtyOrdered,SUM(ISNULL(PR.QtyPicked,
PRA.QtyPicked)) AS QtyPicked,(SUM(ISNULL(PR.QtyPicked,PRA.QtyPicked))*IM.Mass) AS MassPicked
FROM tblPickingSlip PS WITH (NOLOCK)
INNER JOIN tblPickingSlipSource PSS WITH (NOLOCK)
ON PS.PickingSlipNumber=PSS.PickingSlipNumber
INNER JOIN tblPickingSlipItem PSI WITH (NOLOCK)
ON PSS.PickingSlipSourceNumber=PSI.PickingSlipSourceNumber
LEFT JOIN tblPalletReservedArchive PRA WITH (NOLOCK)
ON PSI.ItemNumber=PRA.PickingItemNumber
LEFT JOIN tblPalletReserved PR WITH (NOLOCK)
ON PSI.ItemNumber=PR.PickingItemNumber
LEFT JOIN SysproCompanyTest.dbo.InvMaster IM WITH (NOLOCK)
ON PSI.StockCode=IM.StockCode
LEFT JOIN Warehouse_Control.dbo.tblLicensedUsers LU WITH (NOLOCK)
ON PSI.Owner = LU.UserName
WHERE ISNULL(PRA.Skipped,0)='0' and SourceType='DISP'
AND [Owner] IS NOT NULL
AND EndTime IS NOT NULL
AND CONVERT(DATETIME,[EndTime],111)>= @FromDate
AND CONVERT(DATETIME,[EndTime],111) < @ToDate
GROUP BY PSI.Owner, LU.OperatorName, PS.PickingSlipNumber,SourceNumber,PSS.CustomerName,StartTime,
EndTime,PSI.StockCode,PSI.Qty,IM.Mass,PSS.CustomerPO,InvoiceNumber
) as A
GROUP BY [Owner],OperatorName
ORDER BY [Owner] |
Picker Statistics Jobs
This report will give you an overview of the pickers lines and QTYs per picker for Jobs for a specific date range.
SQL Script
| Code Block |
|---|
declare @FromDate Date
declare @ToDate Date
set @FromDate= '2018-01-01'
set @ToDate = '2018-09-01'
SELECT [Owner],SUM(QtyPicked)AS [Total Quantity Picked],SUM(MassPicked) AS [Total Mass Picked],COUNT(*) LinesPicked,
(CASE 0 WHEN 0 THEN 0 ELSE (SUM(MassPicked)/CONVERT(DECIMAL,0)) END + CASE 0 WHEN 0 THEN 0 ELSE (COUNT(*)/CONVERT(DECIMAL,0)) END)/1*100/(DateDiff(DAY, @FromDate, @ToDate)+1) AS [Weighted Ave],
COUNT(DISTINCT(PickingSlipNumber))AS [Completed Picking Slips]
FROM
(SELECT PSI.[Owner],LU.OperatorName,PS.PickingSlipNumber,SourceNumber AS SalesOrder,CustomerName,CustomerPO,InvoiceNumber,StartTime,EndTime,
PSI.StockCode,Qty AS PickingSlipQty,SUM(ISNULL(PR.QtyOrdered,PRA.QtyOrdered)) AS QtyOrdered,SUM(ISNULL(PR.QtyPicked,
PRA.QtyPicked)) AS QtyPicked,(SUM(ISNULL(PR.QtyPicked,PRA.QtyPicked))*IM.Mass) AS MassPicked
FROM tblPickingSlip PS WITH (NOLOCK)
INNER JOIN tblPickingSlipSource PSS WITH (NOLOCK)
ON PS.PickingSlipNumber=PSS.PickingSlipNumber
INNER JOIN tblPickingSlipItem PSI WITH (NOLOCK)
ON PSS.PickingSlipSourceNumber=PSI.PickingSlipSourceNumber
LEFT JOIN tblPalletReservedArchive PRA WITH (NOLOCK)
ON PSI.ItemNumber=PRA.PickingItemNumber
LEFT JOIN tblPalletReserved PR WITH (NOLOCK)
ON PSI.ItemNumber=PR.PickingItemNumber
LEFT JOIN SysproCompanyTest.dbo.InvMaster IM WITH (NOLOCK)
ON PSI.StockCode=IM.StockCode
LEFT JOIN Warehouse_Control.dbo.tblLicensedUsers LU WITH (NOLOCK)
ON PSI.Owner = LU.UserName
WHERE ISNULL(PRA.Skipped,0)='0' and SourceType='JOB'
AND [Owner] IS NOT NULL
AND EndTime IS NOT NULL
AND CONVERT(DATETIME,[EndTime],111)>= @FromDate
AND CONVERT(DATETIME,[EndTime],111) < @ToDate
GROUP BY PSI.Owner, LU.OperatorName, PS.PickingSlipNumber,SourceNumber,PSS.CustomerName,StartTime,
EndTime,PSI.StockCode,PSI.Qty,IM.Mass,PSS.CustomerPO,InvoiceNumber
) as A
GROUP BY [Owner],OperatorName
ORDER BY [Owner] |
Picker Statistics Replen
This report will give you an overview of the pickers lines and QTYs per picker for Replenishments for a specific date range.
SQL Script
| Code Block |
|---|
declare @FromDate Date
declare @ToDate Date
set @FromDate= '2018-01-01'
set @ToDate = '2018-09-01'
SELECT [Owner],SUM(QtyPicked)AS [Total Quantity Picked],SUM(MassPicked) AS [Total Mass Picked],COUNT(*) LinesPicked,
(CASE 0 WHEN 0 THEN 0 ELSE (SUM(MassPicked)/CONVERT(DECIMAL,0)) END + CASE 0 WHEN 0 THEN 0 ELSE (COUNT(*)/CONVERT(DECIMAL,0)) END)/1*100/(DateDiff(DAY, @FromDate, @ToDate)+1) AS [Weighted Ave],
COUNT(DISTINCT(PickingSlipNumber))AS [Completed Picking Slips]
FROM
(SELECT PSI.[Owner],LU.OperatorName,PS.PickingSlipNumber,SourceNumber AS SalesOrder,CustomerName,CustomerPO,InvoiceNumber,StartTime,EndTime,
PSI.StockCode,Qty AS PickingSlipQty,SUM(ISNULL(PR.QtyOrdered,PRA.QtyOrdered)) AS QtyOrdered,SUM(ISNULL(PR.QtyPicked,
PRA.QtyPicked)) AS QtyPicked,(SUM(ISNULL(PR.QtyPicked,PRA.QtyPicked))*IM.Mass) AS MassPicked
FROM tblPickingSlip PS WITH (NOLOCK)
INNER JOIN tblPickingSlipSource PSS WITH (NOLOCK)
ON PS.PickingSlipNumber=PSS.PickingSlipNumber
INNER JOIN tblPickingSlipItem PSI WITH (NOLOCK)
ON PSS.PickingSlipSourceNumber=PSI.PickingSlipSourceNumber
LEFT JOIN tblPalletReservedArchive PRA WITH (NOLOCK)
ON PSI.ItemNumber=PRA.PickingItemNumber
LEFT JOIN tblPalletReserved PR WITH (NOLOCK)
ON PSI.ItemNumber=PR.PickingItemNumber
LEFT JOIN SysproCompanyTest.dbo.InvMaster IM WITH (NOLOCK)
ON PSI.StockCode=IM.StockCode
LEFT JOIN Warehouse_Control.dbo.tblLicensedUsers LU WITH (NOLOCK)
ON PSI.Owner = LU.UserName
WHERE ISNULL(PRA.Skipped,0)='0' and SourceType='REPLEN'
AND [Owner] IS NOT NULL
AND EndTime IS NOT NULL
AND CONVERT(DATETIME,[EndTime],111)>= @FromDate
AND CONVERT(DATETIME,[EndTime],111) < @ToDate
GROUP BY PSI.Owner, LU.OperatorName, PS.PickingSlipNumber,SourceNumber,PSS.CustomerName,StartTime,
EndTime,PSI.StockCode,PSI.Qty,IM.Mass,PSS.CustomerPO,InvoiceNumber
) as A
GROUP BY [Owner],OperatorName
ORDER BY [Owner] |
Picker Rate Report
This report displays the average rate a Picker is picking per hour, and productive vs unproductive times can be measured.
SQL Script
| Code Block |
|---|
Declare @FromDate Date
Declare @ToDate Date
set @FromDate = '2018-01-01'
set @ToDate = '2018-11-01'
CREATE TABLE #PickerRate (
[Operator] VARCHAR(50)
,[Warehouse] VARCHAR(10)
,[OperatorName] VARCHAR(50)
,[TotalLinesPicked] INTEGER
,[NumberOfBoxes] INTEGER
,[RateLinesProductiveHour] DECIMAL(10, 2)
,[Productive] INTEGER
,[Unproductive] INTEGER
,[ProductiveDuration] VARCHAR(10)
,[UnproductiveDuration] VARCHAR(10)
,[ProductivePercent] DECIMAL(10, 2)
,[UnproductivePercent] DECIMAL(10, 2)
);
CREATE TABLE #TempTable(
[Operator] VARCHAR(50)
,[OperatorName] VARCHAR(50)
,[ItemNumber] VARCHAR(50)
,[TransTime] DATETIME
,[Difference] INTEGER
,[Productive] INTEGER
,[Unproductive] INTEGER
);
DECLARE LineCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT tblPickingSlipItemTrans.[Operator]
,tblLicensedUsers.[OperatorName]
,tblPickingSlipItemTrans.[ItemNumber]
,tblPickingSlipItemTrans.[TransTime]
FROM WarehouseCompanyTest.dbo.tblPickingSlipItem
INNER JOIN WarehouseCompanyTest.dbo.tblPickingSlipItemTrans
ON tblPickingSlipItem.[ItemNumber] = tblPickingSlipItemTrans.[ItemNumber]
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers
ON tblPickingSlipItemTrans.[Operator] = tblLicensedUsers.[UserName]
WHERE CAST(tblPickingSlipItemTrans.[TransTime] AS DATE) >= @FromDate
AND CAST(tblPickingSlipItemTrans.[TransTime] AS DATE) <= @ToDate
ORDER BY tblPickingSlipItemTrans.[Operator] ASC
,tblPickingSlipItemTrans.[TransTime] ASC;
DECLARE @Operator VARCHAR(50)
,@OperatorName VARCHAR(50)
,@ItemNumber VARCHAR(50)
,@TransTime DATETIME
,@PreviousDateTime DATETIME
,@Productive INTEGER
,@UnProductive INTEGER
,@Difference INTEGER
,@TempOperator VARCHAR(50);
SET @Unproductive = 0;
DECLARE @Threshold AS INTEGER;
SET @Threshold = 10;
SET @PreviousDateTime = NULL;
OPEN LineCursor;
FETCH NEXT
FROM LineCursor
INTO @Operator
,@OperatorName
,@ItemNumber
,@TransTime;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Operator <> @TempOperator)
BEGIN
SET @PreviousDateTime =null
END;
SET @Difference = DATEDIFF(MINUTE, @PreviousDateTime, @TransTime);
SET @Productive = @Difference;
SET @Unproductive = 0;
IF (@Productive > @Threshold)
BEGIN
SET @UnProductive = @Difference - @Threshold;
SET @Productive = @Difference - @UnProductive;
END;
INSERT INTO #TempTable VALUES (
@Operator
,@OperatorName
,@ItemNumber
,@TransTime
,@Difference
,@Productive
,@Unproductive
);
SET @PreviousDateTime = @TransTime;
SET @TempOperator = @Operator;
FETCH NEXT
FROM LineCursor
INTO @Operator
,@OperatorName
,@ItemNumber
,@TransTime;
END;
CLOSE LineCursor;
DEALLOCATE LineCursor;
INSERT INTO #PickerRate
SELECT SMRYDATA.[Operator] AS [Operator]
,SMRYDATA.[Warehouse] AS [Warehouse]
,SMRYDATA.[OperatorName] AS [OperatorName]
,SMRYDATA.[Total Lines Picked] AS [TotalLinesPicked]
,SMRYDATA.[Number of boxes] AS [NumberOfBoxes]
,( SMRYDATA.[Total Lines Picked]
/ ( (CAST(NULLIF(SMRYDATA.[Productive],0) AS DECIMAL(10, 2)))
/ 60)) AS [RateLinesProductiveHour]
,SMRYDATA.[Productive]
,SMRYDATA.[Unproductive]
,ISNULL( ( RIGHT('0' + CAST(SMRYDATA.[Productive] / 60 AS VARCHAR(2)), 2)
+ ':' + RIGHT('00' + CAST(SMRYDATA.[Productive] / 60 AS VARCHAR(2)), 2))
,'00:00') AS [ProductiveDuration]
,ISNULL( ( RIGHT('' + CAST(SMRYDATA.[Unproductive] / 60 AS VARCHAR(3)), 3)
+ ':' + RIGHT('00'+CAST(SMRYDATA.[Unproductive] / 60 AS VARCHAR(2)), 2))
,'00:00') AS [UnproductiveDuration]
,IIF( ISNULL(SMRYDATA.[Productive], 0)
+ ISNULL(SMRYDATA.[Unproductive], 0) = 0
,0
, ( SMRYDATA.[Productive]
/ (CAST( ISNULL(SMRYDATA.[Productive], 0)
+ ISNULL(SMRYDATA.[Unproductive], 0) AS DECIMAL(10, 2))))
* 100) AS [ProductivePercent]
,IIF( ISNULL(SMRYDATA.[Productive], 0)
+ ISNULL(SMRYDATA.[Unproductive], 0) = 0
,0
, ( SMRYDATA.[Unproductive]
/ (CAST( ISNULL(SMRYDATA.[Productive], 0)
+ NULLIF(SMRYDATA.[Unproductive], 0) AS DECIMAL(10, 2))))
* 100) AS [UnproductivePercent]
FROM (SELECT [Data].[Operator] AS [Operator]
,[Data].[Warehouse] AS [Warehouse]
,[Data].[OperatorName] AS [OperatorName]
,[Data].[Start Hour] AS [Start Hour]
,[Data].[End Hour] AS [End Hour]
,[Data].[Total Lines Picked] AS [Total Lines Picked]
,[Data].[Number of boxes] AS [Number of boxes]
,SUM(Detail.[Productive]) AS [Productive]
,SUM(Detail.[Unproductive]) AS [Unproductive]
FROM (SELECT tblPickingSlipItemTrans.[Operator] AS [Operator]
,tblPickingSlipItem.[Warehouse] AS [Warehouse]
,tblLicensedUsers.[OperatorName] AS [OperatorName]
,COUNT(tblPickingSlipItemTrans.[ItemNumber]) AS [Total Lines Picked]
,COUNT(DISTINCT((ISNULL( tblPalletReserved.[PickingPallet]
,tblPalletReservedArchive.[PickingPallet])))) AS [Number of boxes]
,DATEPART(HOUR, MIN(tblPickingSlipItemTrans.[TransTime])) AS [Start Hour]
,DATEPART(HOUR, MAX(tblPickingSlipItemTrans.[TransTime])) AS [End Hour]
,( DATEPART(HOUR, MAX(tblPickingSlipItemTrans.[TransTime]))
- DATEPART(HOUR, MIN(tblPickingSlipItemTrans.[TransTime]))) + 1 AS [Working Hours]
FROM WarehouseCompanyTest.dbo.tblPickingSlipItem
INNER JOIN WarehouseCompanyTest.dbo.tblPickingSlipItemTrans
ON tblPickingSlipItem.[ItemNumber] = tblPickingSlipItemTrans.[ItemNumber]
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers
ON tblPickingSlipItemTrans.[Operator] = tblLicensedUsers.[UserName]
LEFT OUTER JOIN WarehouseCompanyTest.dbo.tblPalletReserved
ON tblPalletReserved.[pRes] = tblPickingSlipItemTrans.[pRes]
LEFT OUTER JOIN WarehouseCompanyTest.dbo.tblPalletReservedArchive
ON tblPalletReservedArchive.[pRes] = tblPickingSlipItemTrans.[pRes]
WHERE CAST(tblPickingSlipItemTrans.[TransTime] AS DATE) >= @FromDate
AND CAST(tblPickingSlipItemTrans.[TransTime] AS DATE) <= @ToDate
GROUP BY tblPickingSlipItemTrans.[Operator]
,tblPickingSlipItem.[Warehouse]
,tblLicensedUsers.[OperatorName]) AS [Data]
LEFT OUTER JOIN #TempTable AS Detail
ON [Data].[Operator] = Detail.[Operator] COLLATE Latin1_General_BIN
GROUP BY [Data].[Operator]
,[Data].[Warehouse]
,[Data].[OperatorName]
,[Data].[Start Hour]
,[Data].[End Hour]
,[Data].[Total Lines Picked]
,[Data].[Number of boxes]) AS SMRYDATA;
select * from #PickerRate
--drop table #TempTable
--drop table #PickerRate |
Pick Slips in the same status for more than 5 days
This report give you an overview of stagnant pick slips that have not moved in 5 days.
SQL Script
| Code Block |
|---|
SELECT tblPickingSlip.[Status] AS [Status]
,COUNT(tblPickingSlip.[PickingSlipNumber]) AS [TotalPickSlips]
FROM WarehouseCompanyTest.dbo.tblPickingSlip
INNER JOIN WarehouseCompanyTest.dbo.tblPickingSlipStatusHistory
ON tblPickingSlip.[PickingSlipNumber] = tblPickingSlipStatusHistory.[PickingSlipNumber]
AND tblPickingSlip.[Status] = tblPickingSlipStatusHistory.[Status]
WHERE tblPickingSlip.[Status] NOT IN ('CANCELED','COMPLETE','CLOSED')
AND tblPickingSlipStatusHistory.[Warehouse] = 'ALL'
AND tblPickingSlipStatusHistory.[TimeStamp] < GETDATE() - 5
GROUP BY tblPickingSlip.[Status]; |