CREATE PROC [dbo].[sp_ReconByFiscalPeriod] 
AS

SELECT	dbo.GetFiscalPeriod(CASE WHEN A.procedureType = 'PM'
							THEN A.dateOfService
							ELSE A.dateOfCapture
							END) FiscalPeriod
		,CONVERT(CHAR(4), CASE WHEN A.procedureType = 'PM'
			THEN A.dateOfService
			ELSE A.dateOfCapture
			END, 100) 
			+ CONVERT(CHAR(4), CASE WHEN A.procedureType = 'PM'
			THEN A.dateOfService
			ELSE A.dateOfCapture
			END, 120)  CalendarDate
		,SUM(CASE WHEN A.procedureType = 'TI'
			THEN A.amount ELSE 0 END) Invoices
		,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
INTO	#tempAccount
--SELECT *
FROM	pal_Account A	
WHERE	isVisible = 1		
GROUP BY dbo.GetFiscalPeriod(CASE WHEN A.procedureType = 'PM'
							THEN A.dateOfService
							ELSE A.dateOfCapture
							END)
		,CONVERT(CHAR(4), CASE WHEN A.procedureType = 'PM'
			THEN A.dateOfService
			ELSE A.dateOfCapture
			END, 100) 
			+ CONVERT(CHAR(4), CASE WHEN A.procedureType = 'PM'
			THEN A.dateOfService
			ELSE A.dateOfCapture
			END, 120)  

SELECT	C.FiscalPeriod
		,C.CalendarDate
		,OpeningBalance = SUM(ISNULL(P.Invoices,0)+ISNULL(P.Receipts,0)+ISNULL(P.CreditNotes,0)+ISNULL(P.DebitNotes,0)) OVER (ORDER BY P.FiscalPeriod ROWS UNBOUNDED PRECEDING)
		,C.Invoices
		,C.Receipts
		,C.CreditNotes
		,C.DebitNotes
		,ClosingBalance = SUM(C.Invoices+C.Receipts+C.CreditNotes+C.DebitNotes) OVER (ORDER BY C.FiscalPeriod ROWS UNBOUNDED PRECEDING)
FROM	#tempAccount C
		LEFT JOIN #tempAccount P			
			ON C.FiscalPeriod = P.FiscalPeriod + 1
ORDER BY 1

DROP TABLE #tempAccount