P and L Reports
SELECT
1 as 'Index',
'INCOME' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Income'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
2 as 'Index',
'TOTAL OPERATING INCOME' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Income'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
3 as 'Index',
'COST OF GOODS SOLD' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") * -1 as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Cost Of Goods Sold'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
4 as 'Index',
'GROSS PROFIT' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" in ( 'Cost Of Goods Sold' , 'Income' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
5 as 'Index',
'EXPENSE' as 'Expense',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") * -1 as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Expense'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
6 as 'Index',
'TOTAL OPERATING EXPENSE' as 'Expense',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") * -1 as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Expense'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
7 as 'Index',
'OPERATING PROFIT' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" in ( 'Cost Of Goods Sold' , 'Income' , 'Expense' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
8 as 'Index',
'NON OPERATING INCOME/EXPENSE' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" in ( 'Other Income' , 'Other Expense' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
9 as 'Index',
'TOTAL NON OPERATIN INCOME/EXPENSE' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" in ( 'Other Income' , 'Other Expense' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
10 as 'Index',
'NET PROFIT/LOSS' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" in ( 'Cost Of Goods Sold' , 'Income' , 'Expense' , 'Other Income' , 'Other Expense' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
Balance Sheet For This Year
SELECT
1 as 'Index',
'BANK' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'BANK' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Bank'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Bank'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
2 as 'Index',
'CASH' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'CASH' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Cash'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Cash'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
3 as 'Index',
'OTHER CURRENT ASSETS' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'OTHER CURRENT ASSETS' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Other Current Asset'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Other Current Asset'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
4 as 'Index',
'OTHER CURRENT ASSETS' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'OTHER CURRENT ASSETS' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Other Asset'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Other Asset'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
5 as 'Index',
'FIXED ASSET' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'FIXED ASSET' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Fixed Asset'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Fixed Asset'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
6 as 'Index',
'TOTAL ASSETS' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'TOTAL ASSETS' as 'Type',
'' as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" in ( 'Bank' , 'Cash' , 'Other Current Asset' , 'Other Asset' , 'Fixed Asset' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" in ( 'Bank' , 'Cash' , 'Other Current Asset' , 'Other Asset' , 'Fixed Asset' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
UNION ALL
SELECT
7 as 'Index',
'OTHER CURRENT LIABILITY' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'OTHER CURRENT LIABILITY' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" IN ( 'Other Current Liability' , 'Other Liability' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" IN ( 'Other Current Liability' , 'Other Liability' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
8 as 'Index',
'OTHER CURRENT LIABILITY' as 'Type',
" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'OTHER CURRENT LIABILITY' as 'Type',
" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" IN ( 'Other Current Liability' , 'Other Liability' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" IN ( 'Other Current Liability' , 'Other Liability' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
9 as 'Index',
'EQUITIES' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'EQUITIES' as 'Type',
"Accounts"."Account Name" as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Equity'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Equity'
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
10 as 'Index',
'TOTAL EQUITIES' as 'Type',
as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'TOTAL EQUITIES' as 'Type',
as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Equity' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" = 'Equity' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
GROUP BY "Accounts"."Account Name"
UNION ALL
SELECT
11 as 'Index',
'TOTAL CURRENT LIABILITIES' as 'Type',
as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
'TOTAL CURRENT LIABILITIES' as 'Type',
as 'Account',
sum("Accrual Transactions"."Transaction Amount") as 'Total'
FROM "Accrual Transactions"
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" IN ( 'Bank' , 'Cash' , 'Other Current Asset' , 'Other Asset' , 'Fixed Asset' , 'Other Current Liability' , 'Other Liability' , 'Equity' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())
JOIN "Accounts" ON "Accrual Transactions"."Account ID" = "Accounts"."Account ID"
WHERE "Accounts"."Account Type" IN ( 'Bank' , 'Cash' , 'Other Current Asset' , 'Other Asset' , 'Fixed Asset' , 'Other Current Liability' , 'Other Liability' , 'Equity' )
AND year("Accrual Transactions"."Transaction Date") = year(CURRENT_DATE())