defaultJoins.sql

select 
	sum(recs) as recs,rct_name, 
	sum(rrc_sabrc_reportable) as rrc_sabrc_reportable, 
	sum(rrc_sabrc_compliant) as rrc_sabrc_compliant 
from 
(
	-- Select the stuff that is associated with the record
	select	count(rct_name) as recs, 
			rct_name, 
			sum(convert(numeric,isnull(rrc_sabrc_reportable, 0))) as rrc_sabrc_reportable, 
			sum(convert(numeric,isnull(rrc_sabrc_compliant, 0))) as rrc_sabrc_compliant 
	from	ats.RCT_RECYCLED_CONTENT_TYPE rct
			left join   ats.RRC_RECORD_RECYCLED_CONTENT rrc
			on rrc.RCT_ID = rct.RCT_ID 
			left join ats.VW_RDOC rdoc
			on rrc.R_ID =rdoc.R_ID 
			left join ats.VW_R r
			on rdoc.R_ID = r.R_ID 
			left join ats.VW_FY FY
			on r.FY_ID= FY.FY_ID
			
	where 1=1 
		and (cast(RDOC_DOCUMENT_DATE as DATETIME) between 
		cast(FY.FY_START_DATE as DATETIME) and cast(FY.FY_END_DATE as DATETIME) )
		and (DOCT_NAME='CONTRACT EXECUTED')
		and (FY_NAME = '10/11')
	group by rct_name
union
	-- And join it with our empty set of records (i.e. our baseline)
	select 0, rct_name, 0,0 from ats.RCT_RECYCLED_CONTENT_TYPE rct
) a
group by a.rct_name