CREATE VIEW [dbo].[pal_vReconByFiscalPeriod] AS
SELECT	dbo.GetFiscalPeriod(CASE WHEN A.procedureType = 'PM'
							THEN A.dateOfService
							ELSE A.dateOfCapture
							END) FiscalPeriod
		,SUM(CASE WHEN A.procedureType = 'TI'
			THEN A.amount ELSE 0 END) Invoiced
		,SUM(CASE WHEN A.procedureType = 'PM'
			THEN A.amount ELSE 0 END) Receipts
		,SUM(CASE WHEN A.procedureType = 'CN'
			THEN A.amount ELSE 0 END) CreditNotes
		,SUM(CASE WHEN A.procedureType = 'DN'
			THEN A.amount ELSE 0 END) DebitNotes

		,SUM(CASE WHEN A.procedureType = 'TI'
			THEN A.amount ELSE 0 END) 
		+ SUM(CASE WHEN A.procedureType = 'PM'
			THEN A.amount ELSE 0 END) 
		+ SUM(CASE WHEN A.procedureType = 'CN'
			THEN A.amount ELSE 0 END) 
		+ SUM(CASE WHEN A.procedureType = 'DN'
			THEN A.amount ELSE 0 END) Balance

--SELECT *
FROM	pal_Account A
			
GROUP BY dbo.GetFiscalPeriod(CASE WHEN A.procedureType = 'PM'
							THEN A.dateOfService
							ELSE A.dateOfCapture
							END)

GO
