AP vouchers by user

Are you ever curious how many vouchers your AP team enters on a monthly and annual basis? This query will help you get those totals by the user who created the voucher. Just remember, since we are using APDOC, this is the count of vouchers. This does not take into account how many lines are on the voucher, which you would find by joining to APTRAN.

To display the username, you could join to the UserRec table in your SYS database.

declare @year varchar(4) = '2019'

;with cte as
(
  select	
	  d.batnbr, 
    d.refnbr, 
  	d.doctype, 
  	d.perpost, 
  	d.crtd_user,
  	d.crtd_datetime, 
  	d.vendid, 
  	d.origdocamt
  from dbo.apdoc  d
  where	d.doctype IN ('VO', 'AD', 'AC') 
      AND YEAR(d.crtd_datetime) = @year
)

select  crtd_user,
		[1] as jan,
		[2] as feb,
		[3] as mar,
		[4] as apr,
		[5] as may,
		[6] as jun,
		[7] as jul,
		[8] as aug,
		[9] as sep,
		[10] as oct,
		[11] as nov,
		[12] as dec, 
		SUM([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]) over (partition by crtd_user ) as total
from
(
		select crtd_user, month(cte.crtd_datetime) AS mon, cte.doctype AS num
		from  cte
		UNION ALL
		select 'TOTAL', month(cte.crtd_datetime), doctype
		from cte
) sub
PIVOT (COUNT(num) for mon IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p
order by  crtd_user;
go

Feel free to share...

Leave a Reply

Close Menu