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