****************************************************************************************************
Location : Local or Network Folders
Host     : FINDITEZ-T460P
Path     : C:\Users\ken\Documents\Test Cases\en\Samples\Reports\Financial\Consolidated Income Statement.rpt
****************************************************************************************************

-------------------------------------------------- Main Body --------------------------------------------------

-- Command(s)

Command

SELECT [Amount], [Account Number]
FROM [Monthly Account Budgets]
WHERE Year=Year({?End Date}) and Month = Month({?End Date})



Command_1

SELECT Sum(Amount) AS Year_Budget, [Account Number]
FROM [Monthly Account Budgets]
WHERE Year=Year({?End Date}) and month <= month({?End Date})
GROUP BY [Account Number];


-- Query(s)

SELECT 
	Command.Amount
	,Command.[Account Number]
FROM
	Command

SELECT 
	Command_1.Year_Budget
	,Command_1.[Account Number]
FROM
	Command_1

SELECT 
	Journal_Entry.Date
	,Journal_Entry.Amount
	,Journal_Entry.[Debit Or Credit]
	,Journal_Entry_Type.[Journal Entry Type]
FROM
	[Journal Entry Type] Journal_Entry_Type
		INNER JOIN [Journal Entry] Journal_Entry ON Journal_Entry_Type.[Journal Entry Type ID] = Journal_Entry.[Journal Entry Type ID]

SELECT 
	Account.[Account Number]
	,Account.[Account Heading Number]
	,Account.[Account Type ID]
	,Account.[Account Name]
	,Account_Type.[Account Type]
	,Journal_Entry.Date
	,Journal_Entry.Amount
	,Journal_Entry.[Debit Or Credit]
	,Account_Heading.[Account Heading Name]
	,Command.Amount
	,Command.[Account Number]
	,Command_1.Year_Budget
	,Command_1.[Account Number]
	,Journal_Entry_Type.[Journal Entry Type]
FROM
	Command
		INNER JOIN Account ON Command.[Account Number] = Account.[Account Number]
		INNER JOIN [Account Type] Account_Type ON Account.[Account Type ID] = Account_Type.[Account Type ID]
		INNER JOIN [Journal Entry] Journal_Entry ON Account.[Account Number] = Journal_Entry.[Account Number]
		INNER JOIN [Account Heading] Account_Heading ON Account.[Account Heading Number] = Account_Heading.[Account Heading Number]

SELECT 
	Account.[Account Number]
	,Account.[Account Heading Number]
	,Account.[Account Type ID]
	,Account.[Account Name]
	,Account_Type.[Account Type]
	,Account_Heading.[Account Heading Name]
	,Command.[Account Number]
	,Command_1.Year_Budget
	,Command_1.[Account Number]
FROM
	Command_1
		INNER JOIN Account ON Command_1.[Account Number] = Account.[Account Number]

-- Record Selection Criteria

WHERE
	Account_Type.[Account Type] in ("Expense", "Revenue") and
Journal_Entry_Type.[Journal Entry Type] <> "closing entries" and
Journal_Entry.Date <= {?End Date} and
Journal_Entry.Date >= dateserial(year({?End Date}),1,1)

-- Record Sorting

ORDER BY
	Account_Type.[Account Type] -- Original Order Group #1
	,Account_Heading.[Account Heading Name] ASC -- Group #2
	,Account.[Account Name] ASC -- Group #3