Friday, October 31, 2014

Inventory Transaction Analysis - Unique Transactions per day - SQL

Recently I answered a forum post for 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 Inventory Transactions processed on a given day and the number of overall lines.

SET @Date = '04/12/2017'

/* Inventory Transactions */
Select SUM(a.IV_Trxs) IV_Trxs from (
select count(I.IV_TRXS) IV_Trxs from (
select count(IVDOCNBR) IV_Trxs from IV10001 group by IVDOCNBR) I
Union All
select COUNT(IH.IV_TRXS) IV_Trxs from (
select COUNT(DOCNUMBR) IV_Trxs from IV30300 WHERE DOCDATE = @Date group by DOCNUMBR) IH ) a
/* Inventory Transaction Lines */
select SUM(IV_Trxs) from (
select COUNT(I.IVDOCNBR) IV_Trxs from IV10001 I
Union All
Select COUNT(DOCNUMBR) IV_Trxs from IV30300 where DOCDATE = @Date) a