orders_raised_by_cpy.sql

----------------------------------------
/**
 * 
 * Returns number of purchase orders
 * raised this period per company.
 * 
 */
----------------------------------------
WITH 
	orders_by_company (company, order_count) 
AS 
	( 
		SELECT 
			poh.CPY_0, count(poh.POHNUM_0)
		FROM 
			PORDER poh
		JOIN 
			(
				SELECT 
					per.CPY_0, per.PERNUM_0, per.FIYNUM_0, per.LEDTYP_0, fiy.DESSHO_0, per.PERSTR_0, per.PEREND_0
				FROM 
					PERIOD per 
				JOIN 
					FISCALYEAR fiy 
					ON 
						per.CPY_0 = fiy.CPY_0
					AND 
						per.FIYNUM_0 = fiy.FIYNUM_0
					AND 
						per.LEDTYP_0 = fiy.LEDTYP_0
			) AS year_and_period
				ON 
					poh.CPY_0 = year_and_period.CPY_0
				AND 
					poh.CREDAT_0 BETWEEN year_and_period.PERSTR_0 AND year_and_period.PEREND_0
				AND 
					year_and_period.PERNUM_0 = 11
				AND 
					year_and_period.DESSHO_0 = '2019'
				AND 
					year_and_period.LEDTYP_0 = 1
		GROUP BY 
			poh.CPY_0	
	)
		SELECT 
			company [Company], order_count [No. of Orders]
		FROM 
			orders_by_company
     
/* Returned result set */

Company     No. of Orders
---------------------------
Company A   1471
Company B   66
Company C   295