Warehouse Picking Metrics – Number of Orders and Lines Picked per Day

Accellos / High Jump WMS: Warehouse Picking Metrics – Number of Orders and Lines Picked per Day

A few things to note about this query:
– query is written to provide a summary of total number of picked orders and transfers in a day by individual picker
– query is written to provide a detail of total number of lines picked by hour throughout the day by individual picker
– the DB is set to use military time, therefore a case statement is needed to translate the times

Variables used in this query:
– XXX, replace each of the XXX in the case statement to match the handheld (RF device or iPod) login ID’s. These will identify your picking staff.
– YYY, replace the YYY with the order type ID from your ERP that is sync’d to the WMS.
– ZZZ, replace the ZZZ with the transfer ID if you are transferring items between branches.
– AAA, replace the AAA with the date you want to pull the metrics for. If you want to do a date range comment out the single date and un-comment the between range.

select summary.month [Month],
summary.Picker, sum(summary.orders) [Orders], sum(summary.OrderLines) [Order Lines],
sum(summary.am7orderlines) [7-8A Lines], sum(summary.am8orderlines) [8-9A Lines], sum(summary.am9orderlines) [9-10A Lines], sum(summary.am10orderlines) [10-11A Lines], sum(summary.am11orderlines) [11-12A Lines],
sum(summary.am12orderlines) [12-1P Lines], sum(summary.am1orderlines) [1-2P Lines], sum(summary.am2orderlines) [2-3P Lines], sum(summary.am3orderlines) [3-4P Lines], sum(summary.am4orderlines) [4-5P Lines],
sum(summary.am5orderlines) [5-6P Lines], sum(summary.am6orderlines) [6-7P Lines],
sum(summary.transfers) [Transfers], sum(summary.TransferLines) [Transfer Lines],
(sum(summary.orderlines) + sum(summary.transferlines)) [Total Lines]

from

(select case
when details.month = ‘1’ then ‘January’
when details.month = ‘2’ then ‘February’
when details.month = ‘3’ then ‘March’
when details.month = ‘4’ then ‘April’
when details.month = ‘5’ then ‘May’
when details.month = ‘6’ then ‘June’
when details.month = ‘7’ then ‘July’
when details.month = ‘8’ then ‘August’
when details.month = ‘9’ then ‘September’
when details.month = ’10’ then ‘October’
when details.month = ’11’ then ‘November’
when details.month = ’12’ then ‘December’
end [Month],
details.Picker,
isnull((case when details.Type = ‘order’ then count(details.lines) end),0) [OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ‘7’ and ‘7’ then count(details.lines) end),0) [am7OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ‘8’ and ‘8’ then count(details.lines) end),0) [am8OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ‘9’ and ‘9’ then count(details.lines) end),0) [am9OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’10’ and ’10’ then count(details.lines) end),0) [am10OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’11’ and ’11’ then count(details.lines) end),0) [am11OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’12’ and ’12’ then count(details.lines) end),0) [am12OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’13’ and ’13’ then count(details.lines) end),0) [am1OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’14’ and ’14’ then count(details.lines) end),0) [am2OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’15’ and ’15’ then count(details.lines) end),0) [am3OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’16’ and ’16’ then count(details.lines) end),0) [am4OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’17’ and ’17’ then count(details.lines) end),0) [am5OrderLines],
isnull((case when details.Type = ‘order’ and details.Times between ’18’ and ’18’ then count(details.lines) end),0) [am6OrderLines],
isnull((case when details.Type = ‘transfer’ then count(details.lines) end),0) [TransferLines],
isnull((case when details.Type = ‘order’ then count(distinct(details.lines)) end),0) [Orders],
isnull((case when details.Type = ‘transfer’ then count(distinct(details.lines)) end),0) [Transfers]

from

(select month(date_time) [Month],
case
when userid = ‘XXX’ then ‘Picker 1’
when userid = ‘XXX’ then ‘Picker 2’
when userid = ‘XXX’ then ‘Picker 3 ‘
else userid
end [Picker], packslip [Order], packslip [Lines],
case
when packslip like ‘YYY’ then ‘Order’
when packslip like ‘ZZZ’ then ‘Transfer’
end [Type],
extended, action, location, datepart(hour, [DATE_TIME]) [Times] from Rf_log_all
where action = ‘pickdetl’
and convert(date,DATE_TIME) = AAA
— and convert(date,DATE_TIME) between AAA and BBB

) details

group by details.Month, details.Picker, details.type, details.Times ) Summary

group by Summary.Month, summary.picker