Ribbon Bar / Launch HTML5 / Launch KPI
Various reports are available to measure a Picker’s efficiency. Refer to the below list.
Info |
---|
If you don’t like reading, you can watch the video. |
Panel | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
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]; |