CREATE PROC [dbo].[sp_ReconByFiscalPeriod] 
AS

SELECT	dbo.GetFiscalPeriod(A.dateOfService) FiscalPeriod
		,CONVERT(CHAR(4), A.dateOfService, 100) 
			+ CONVERT(CHAR(4), A.dateOfService, 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 = 'CJ'
			THEN A.amount ELSE 0 END) CreditNotes
		,SUM(CASE WHEN A.procedureType = 'DJ'
			THEN A.amount ELSE 0 END) DebitNotes
		,ROW_NUMBER() OVER (ORDER BY dbo.GetFiscalPeriod(A.dateOfService)) RowNumber
INTO	#tempAccount
--SELECT *
FROM	pal_Account A
WHERE	A.isVisible = 1		
GROUP BY dbo.GetFiscalPeriod(A.dateOfService)
		,CONVERT(CHAR(4), A.dateOfService, 100) 
			+ CONVERT(CHAR(4), A.dateOfService, 120)  
ORDER BY 1

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.RowNumber =P.RowNumber + 1

ORDER BY 1

DROP TABLE #tempAccount
