SQL Scripts–Order Count–Previous Week By User
Do you ever wonder what your sales order workload looks like for you customer service staff? This script will show you the number of orders entered by user for last week.
***Note that you will need to make sure the @doc_date is set to January 1st of the current year***
This script uses the following views:
– Open Sales Documents (tspvSalesDocument)
– Historical Sales Documents (tspvSalesDocuementHistory)
DECLARE @doc_date DATETIME
–Set @doc_date to January 1st of the current year–
SET @doc_date = ‘1/1/2011’
SELECT created_by,
[Week] = DATEPART(ww, doc_date),
[Orders_Entered] = COUNT(*)
FROM ( select Created_By,
Doc_Date
from tspvSalesDocumentHistory
WHERE Doc_Date >= @doc_date
AND Sales_Doc_Type = ‘order’
AND Original_Num = ”
union all
select Created_By,
Doc_Date
FROM tspvSalesDocument
WHERE Doc_Date >= @doc_date
AND Sales_Doc_Type = ‘order’
AND Original_Num = ”
) as combined
WHERE DATEPART(ww,combined.Doc_Date) = DATEPART(ww,GETDATE()-7)
GROUP BY Created_By,
DATEPART(ww, doc_date)
ORDER BY DATEPART(ww, Doc_Date),
Created_By
Trackbacks & Pingbacks