MBEWBE.sql

select
	cur.R_ID,
	cur.ATS_ID,
	cur.STAT_NAME,
	cur.R_DESCRIPTION,
	cur.R_TERM_START,
	cur.R_TERM_END,
	cur.V_NAME,
	cur.VADR_ADDRESS_LINE1,
	cur.VADR_ADDRESS_LINE2,
	cur.VADR_CITY,
	cur.VADR_STATE,
	rbpa.MBE,
	rbpa.WBE,
	rdoc.DOCT_NAME,
	rdoc.RDOC_DOCUMENT_DATE
from 
	ats.vw_r_current cur, -- current records
	(select * from
		(select r_id, bpty_name, rbpa_amount from ats.vw_rbpa) ps
		PIVOT(
			sum(rbpa_amount) FOR bpty_name in (MBE,WBE)
		) AS pvt -- determine total contribution to record business participation
	) rbpa left outer join ats.vw_rdoc rdoc
		on rdoc.r_id = rbpa.r_id -- this will be used to join the document dates
 where cur.r_id=rbpa.r_id 
	and (rbpa.MBE is not null or rbpa.WBE is not null) -- no need to display non-participating records
	and (rdoc.doct_name is null or rdoc.doct_name = 'Contract Executed') -- show empty dates, or relevant ones