Sales Transaction Entry Open and History Files Transaction Volume Analysis

Recently I answered a forum post by a user who wanted to know the number of transactions by module entered in a given day.  I authored a series of queries which allow a user to input a date and execute the query. 

The queries identify the number of unique transactions and the number of lines in the table. Dividing one by the other will reveal the average lines per transaction.  

This query reveals the Number of Sales Transactions processed on a given day and the number of overall lines.

DECLARE @Date as DATETIME

SET @Date = '04/12/2017'

/*Sales Orders*/

select sum(a.Sales_Orders) Sales_Orders from

(Select count(s.Sales_Orders) Sales_Orders  from (Select COUNT(sl.SOPNUMBE) Sales_Orders from SOP10100 SH

Inner Join SOP10200 SL

on SH.SOPNUMBE = SL.SOPNUMBE

and SH.SOPTYPE = SL.SOPTYPE

where sh.DOCDATE = @Date

group by SL.SOPNUMBE) S

Union All

Select count(s.Sales_Orders) Sales_Orders  from (Select COUNT(sl.SOPNUMBE) Sales_Orders from SOP30200 SH

Inner Join SOP30300 SL

on SH.SOPNUMBE = SL.SOPNUMBE

and SH.SOPTYPE = SL.SOPTYPE

where sh.DOCDATE = @Date

group by SL.SOPNUMBE) S) a

/*Sales Lines*/

select sum(a.SOP_Lines) SOP_Lines from

(Select COUNT(SH.SOPNUMBE) SOP_Lines from SOP10100 SH

Inner Join SOP10200 SL

on SH.SOPNUMBE = SL.SOPNUMBE

and SH.SOPTYPE = SL.SOPTYPE

where sh.DOCDATE = @Date

Union All

Select COUNT(SH.SOPNUMBE) SOP_Lines from SOP30200 SH

Inner Join SOP30300 SL

on SH.SOPNUMBE = SL.SOPNUMBE

and SH.SOPTYPE = SL.SOPTYPE

where sh.DOCDATE = @Date) a

CASE Statement for SOPTYPE in Dynamics GP Sales Tables

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the SOP Type Field in Dynamics GP Sales Open [SOP10100].[SOPTYPE] or History [SOP30200].[SOPTYPE] tables: use the following case statement to return text versions of the default SOPTYPE in Dynamics GP:

select

CASE SOPTYPE

      WHEN 1 THEN 'Quote'

      WHEN 2 THEN 'Order'

      WHEN 3 THEN 'Invoice'

      WHEN 4 THEN 'Return'

      WHEN 5 THEN 'Back Order'

      WHEN 6 THEN 'Fulfillment Order'

      ELSE 'ERROR'

END SOPTYPE_TEXT,