Skip to content

SQL Scripts–Order Count–Previous Week By User

September 1, 2011

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

About these ads

From → Dynamics GP, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: