Purchase Order Entry Transaction Analysis - Unique Purchase Orders and Total Lines

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 appropriate tables (Open or Historical). Dividing one by the other will reveal the average lines per transaction.  

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

declare @Date as DATETIME

SET @Date = '04/12/2017'

/*Purchase Orders*/

select sum(a.Sales_Orders) Purchase_Orders from (

Select count(P.Sales_Orders) Sales_Orders  from (Select COUNT(PL.PONUMBER) Sales_Orders from POP10100 PH

Inner Join POP10110 PL

on PH.PONUMBER = PL.PONUMBER

and PH.POTYPE = PL.POTYPE

where PH.DOCDATE = @Date

group by PL.PONUMBER) P

Union All

Select count(P.Sales_Orders) Purchase_Orders  from (Select COUNT(PL.PONUMBER) Sales_Orders from POP30100 PH

Inner Join POP30110 PL

on PH.PONUMBER = PL.PONUMBER

and PH.POTYPE = PL.POTYPE

where PH.DOCDATE = @Date

group by PL.PONUMBER) P) a

/*Purchase Lines*/

select sum(a.SOP_Lines) POP_Lines from (

Select COUNT(PH.PONUMBER) SOP_Lines from POP10100 PH

Inner Join POP10110 PL

on PH.PONUMBER = PL.PONUMBER

and PH.POTYPE = PL.POTYPE

where PH.DOCDATE = @Date

Union All

Select COUNT(PH.PONUMBER) POP_Lines from POP30100 PH

Inner Join POP30110 PL

on PH.PONUMBER = PL.PONUMBER

and PH.POTYPE = PL.POTYPE

where PH.DOCDATE = @Date) a