Userperformancedetails
Report UserPerformanceDetails
- Filename: UserPerformanceDetails
- Title: User performance :StartDate - :EndDate-1
- Sample: UnitX
Synopsis
Display user peformance report with/without totals.
SQL
With TempData as (
SELECT
OpType, ARTCODE, QTY, ORDERNO, CREATIONDATE, UserName,
ScanStartDate, originallocation, location ,
(CASE
WHEN Optype='PICK'
THEN OPTYPE+'-'+Left(Location,1)+' - '+OrderNo
WHEN Optype in ('MOVE', 'MOVE-FL') and Left(Location,4)<>'H.99'
THEN OPTYPE+'-'+Left(Location,1)+' - '+Left(OrderNo,11)
WHEN Optype in ('MOVE', 'MOVE-FL') and Left(Location,4)='H.99'
THEN OPTYPE+'-'+Left(Location,4)+' - '+Left(OrderNo,11)
ELSE
OpType
END) as TOpType,
(CASE
WHEN tmpSCANHISTORY.Optype='PICK' THEN 'PICK'
WHEN tmpSCANHISTORY.Optype in ('MOVE', 'MOVE-FL') THEN 'MOVE'
ELSE tmpSCANHISTORY.Optype END) AS DisplayOP
FROM
SCANHISTORY
WHERE
(CreationDate>=:StartDate)
AND (CreationDate<:EndDate)
AND (OPTYPE in ('PICK', 'MOVE', 'MOVE-FL'))
AND (left(OrderNo, 2)<>'KG')
ORDER BY
USERNAME, OPTYPE, creationdate asc, SCANSTARTDATE asc
),
TempMinDates as (
SELECT
TopType, min(X.CreationDate) as MOpDate
FROM
TempData
GROUP BY
TopType
)
SELECT
TopType,
ARTCODE, QTY, ORDERNO, T.CREATIONDATE, T.SCANSTARTDATE, UserName,
DateDiff(second, ScanStartDate, T.CreationDate) as WorkTime,
originallocation, location,
(Select MOpDate from TempMinDates X WHERE (X.TopType=T.TopType)) as MOpDate,
T.DisplayOp as OP
FROM
TempData T
inner join OUT on (OrderNo=Aviz) and (isnull(CLIENT,'')='GUTHY RENKER')
WHERE
(T.CreationDate>=:StartDate)
AND (T.CreationDate<:EndDate)
AND (T.OPTYPE in ('PICK', 'MOVE', 'MOVE-FL'))
ORDER BY
USERNAME, MOpDate, OPTYPE, OP, T.creationdate asc, T.SCANSTARTDATE asc
Parameters
- StartDate Start date of performance report period.
- EndDate End date of performance report period.
- ShowDetails Boolean, when false, hide details (Example code, see form 187, BitBtn2Click)