set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History) 

set @Date = (select dateadd(MM,-12,max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])

; with 
Sales as
(
	SELECT 	ParentID,
		sum ([Net_Sales]) as Net_Sales_Rolling12,
		SUM ([Net_Intg]) as Net_Intg_Rolling12,
		SUM ([Scheme_Fees]) as Scheme_Fees_Rolling12,
		SUM ([Funding_Amt]) as Funding_Amt_Rolling12
	FROM 	[FDMS].[dbo].[Fact_Financial_History]f 
		full outer join Dim_Outlet o	on 	f.hst_merchnum = o.FDMSAccountNo_First9
	Where 	hst_date_processed > @date
	group by o.ParentID
),
Processing as
(
	SELECT 	ParentID
		,sum([Value]) as ProcessingCost_Rolling12
	FROM 	[FDMS].[dbo].[Fact_ProcessingCost]f 	
		full outer join Dim_Outlet o	on 	f.FDMSAccountNo = o.ParentID
	Where 	Period > @date
	group by o.ParentID
),
MscCount as
(
	SELECT	FDMSAccountNo,
		[Month_end_date] as hst_date_processed,
		Case 	when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
			When Fee_Code = '00Y' Then 'Refund Transaction Charge'
			When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'Y' then 'Other repeatable'
			When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'N' then 'Other Non repeatable'
			Else salesMI_Group1 
			end as description,
		d.repeat_msc,
		Sum([Retail_amount]) As Amount
	FROM 	FDMS.dbo.Fact_Fee_History f
		INNER JOIN FDMS.dbo.Dim_Fee_Codes D 	ON 	f.Fee_Sequence_Number = D.Fee_Code
	where 	Month_end_date >= dateadd(mm,datediff(mm,0,getdate())-12,0)
	and 	Month_end_date < dateadd(mm,datediff(mm,0,getdate()),0)
	and 	FDMSAccountNo ='878000039882'
	Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1, d.Repeat_MSC
),
Msc as
(
	SELECT 	[FDMSAccountNo],
		hst_date_processed,
		SUM ( case when description ='MSC' then ([Amount])else 0 end ) as [MSC_2012],
		SUM ( case when description ='PCI' then ([Amount])else 0 end ) as [PCI_2012],
		SUM ( case when description ='Refund Transaction Charge' then ([Amount])else 0 end ) as [RefundTransactionCharge_2012],
		SUM ( case when description ='Other repeatable' then ([Amount])else 0 end ) as [Other_repeatable2012],
		SUM ( case when description ='Other Non repeatable' then ([Amount])else 0 end ) as [Other_Non_repeatable2012]
	FROM 	MscCount
	where 	FDMSAccountNo = '878000039882'
	group by [FDMSAccountNo], hst_date_processed
)
select	*
from 	Sales s
	full outer join Processing p	on 	f.ParentID 	= p.ParentID
	full outer join Msc m		on	f.ParentID	= m.FDMSAccountNo

select	*
from 	Sales s
	full outer join Processing p	on 	f.ParentID 	= p.ParentID
	full outer join Msc m		on	f.ParentID	= m.FDMSAccountNo