Yearly Report in mysql (Project organisation)

by hammad ali raza ⌂ @, Abbottabad, Saturday, July 13, 2013, 09:46 (3912 days ago)

MYSQL

I have 2 tables revenue and expense

Table: Revenue
------------------------------------------------------
transactionID | transactionDate | Branch | TotalAMount
------------------------------------------------------
1 | 2013-01-01 | MC | 23900
------------------------------------------------------
2 | 2013-01-02 | MC | 12500
------------------------------------------------------
3 | 2013-02-11 | MC | 1735
------------------------------------------------------
4 | 2013-02-15 | MC | 9000
------------------------------------------------------
5 | 2013-03-01 | MC | 1100
------------------------------------------------------
6 | 2013-04-21 | MC | 45690
------------------------------------------------------
7 | 2013-05-01 | MC | 9900
------------------------------------------------------
..... and Revenue lists goes down ....

Table : expenserecord
------------------------------------------------------
id |transactionID | Category| Item| Amount
------------------------------------------------------
1 | 1 | Refreshment | 1500
------------------------------------------------------
2 | 1 | Patrol | 2000
------------------------------------------------------
3 | 2 | Cargo | 350
------------------------------------------------------
4 | 3 | Advertisement | 1200
------------------------------------------------------
..... and ExpenseRecord lists goes down ....

**My Question is how to get desired output mentioned below


Desired Output:

1. Give me 12 months Expense report Category wise. (It may look like below)

Category | Jan | Feb | March
----------------| ------ | -------- | -------
Cargo | 350 | 0 | 0
Refreshment | 1500 | 0 | 6374
Advertisement | 0 | 1200 | 0

..... and category lists goes down ....

RSS Feed of thread