select A.Operator,
   Count(distinct I.PalletNumber) as NumberofBoxes--, Sum(I.Qty) as TotalQuantity --, L.OperatorName
from tblPalletItem I with (nolock)
       inner join tblPalletAction A with (nolock)
              on I.PalletNumber=A.PalletNumber and A.ActionName='ACTIVE'
       left join Warehouse_Control.dbo.tblLicensedUsers L with (nolock)
              on A.Operator=L.UserName
where I.PalletNumber like '%+%+B%' and Cast(A.ActionDate as Date)>=@FromDate and Cast(A.ActionDate as Date)<@ToDate
group by A.Operator,L.OperatorName
order by NumberofBoxes DESC

SELECT CAST (DATEPART(YEAR, InvoiceDate) AS VARCHAR) + '/' + CAST(  DATEPART(MONTH, InvoiceDate) AS VARCHAR) AS 'Month',
SUM(NetSalesValue)  AS 'SalesValue' FROM SysproCompanyTest.dbo.ArTrnDetail
WHERE InvoiceDate > DATEADD(yy,-1,getdate())
GROUP BY  DATEPART(YEAR, InvoiceDate) , DATEPART(MONTH, InvoiceDate)
ORDER BY DATEPART(YEAR, InvoiceDate) DESC , DATEPART(MONTH, InvoiceDate) DESC;

select PSH.PickingSlipNumber, PSH.TimeStamp as PKPrint, PSH.Operator, LU.OperatorName into #PKPRINT
from tblPickingSlipStatusHistory PSH with (nolock)
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers LU on PSH.Operator=LU.UserName
where PSH.[Status]='PRINTED' and Warehouse='ALL'

select PSH.PickingSlipNumber, PSH.TimeStamp as PKStart, PSH.Operator, LU.OperatorName into #PKSTART
from tblPickingSlipStatusHistory PSH with (nolock)
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers LU on PSH.Operator=LU.UserName
where PSH.[Status]='STARTED' and Warehouse='ALL'

select PSH.PickingSlipNumber, max(PSH.TimeStamp) as PKPicked, PSH.Operator, LU.OperatorName into #PKPICKED
from tblPickingSlipStatusHistory PSH with (nolock)
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers LU on PSH.Operator=LU.UserName
where PSH.[Status]='PICKED' and Warehouse='ALL'
group by PSH.PickingSlipNumber,  PSH.Operator, LU.OperatorName


select PSH.PickingSlipNumber, PSH.TimeStamp as PKChecked, PSH.Operator, LU.OperatorName into #PKCHECKED
from tblPickingSlipStatusHistory PSH with (nolock)
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers LU on PSH.Operator=LU.UserName
where PSH.[Status]='CHECKED' and Warehouse='ALL'


select PSH.PickingSlipNumber, PSH.TimeStamp as PKCOMPL, PSH.Operator, LU.OperatorName, PSH.Warehouse into #PKCOMPL
from tblPickingSlipStatusHistory PSH with (nolock)
INNER JOIN Warehouse_Control.dbo.tblLicensedUsers LU on PSH.Operator=LU.UserName
where PSH.[Status]='INVOICED' OR PSH.[Status]='D/NOTE'

Select PSS.SourceNumber, PSS.PickingSlipNumber, PS.[Status] as CurrentStatus, COUNT(PSI.Line) as LineCount, PSS.EntryDateTime into #SOs
from tblPickingSlipSource PSS with (nolock) 
INNER JOIN tblPickingSlip PS with (nolock) on PS.PickingSlipNumber=PSS.PickingSlipNumber
INNER JOIN tblPickingSlipItem PSI with (nolock) on PSI.PickingSlipSourceNumber=PSS.PickingSlipSourceNumber
group by PSS.SourceNumber, PSS.PickingSlipNumber, PSS.EntryDateTime, PS.Status


select S.SourceNumber as SalesOrder, S.PickingSlipNumber, S.CurrentStatus, S.LineCount, S.EntryDateTime, PP.OperatorName as PrintOperator, PP.PKPrint as PrintedTime,
PS.OperatorName as Picker, PS.PKStart as StartPickTime, PK.PKPicked as EndPickTime, 
datediff(MINUTE,PS.PKStart, PK.PKPicked)as PickDuration_in_minutes, PC.OperatorName as Checker, PC.PKChecked as CheckedTime,PKI.PKCOMPL as Invoiced_or_DNOTE_Time, 
DATEDIFF(minute, PC.PKChecked, PKI.PKCOMPL) as Time_from_Check_to_invoic_in_minutes, datediff(minute,PP.PKPrint,PS.PKStart)as Time_from_Print_to_Start, datediff(minute, PP.PKPrint,PKI.PKCOMPL) as Time_from_printed_to_Invoiced

from #SOs S
INNER JOIN #PKPRINT PP on S.PickingSlipNumber=PP.PickingSlipNumber
INNER JOIN #PKSTART PS on S.PickingSlipNumber=PS.PickingSlipNumber
INNER JOIN #PKPICKED PK on S.PickingSlipNumber=PK.PickingSlipNumber
INNER JOIN #PKCHECKED PC on S.PickingSlipNumber=PC.PickingSlipNumber
left JOIN #PKCOMPL PKI on S.PickingSlipNumber=PKI.PickingSlipNumber
where S.EntryDateTime>'2018-01-01'
ORDER BY S.EntryDateTime

drop table #PKPRINT
drop table #PKSTART
drop table #PKPICKED
drop table #PKCHECKED
drop table #PKCOMPL
drop table #SOs