12,065,606 members (50,693 online)
Rate this:
See more:
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
------------------------------------------------------
------------------------------------------------------
..... and ExpenseRecord lists goes down ....

my question is how to get desired output mentioned below

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

..... and product lists goes down ....
Posted 12-Jul-13 16:10pm
Edited 13-Jul-13 9:33am
v2
Sergey Alexandrovich Kryukov 12-Jul-13 22:07pm

Sorry, not a question.
—SA

Rate this:

## Solution 1

This may not be the best answer but at least you can get an idea on how to work on
your report, and not using pivot.

```SELECT A.[Category],
isnull(SUM([JANUARY]),'') as [JANUARY],
isnull(SUM([FEBRUARY]),'') as [FEBRUARY],
isnull(SUM([MARCH]),'') as [MARCH],
isnull(SUM([APRIL]),'') as [APRIL],
isnull(SUM([MAY]),'') as [MAY],
isnull(SUM([JUNE]),'') as [JUNE],
isnull(SUM([JULY]),'') as [JULY],
isnull(SUM([AUGUST]),'') as [AUGUST],
isnull(SUM([SEPTEMBER]),'') as [SEPTEMBER],
isnull(SUM([OCTOBER]),'') as [OCTOBER],
isnull(SUM([NOVEMBER]),'') as [NOVEMBER],
isnull(SUM([DECEMBER]),'') as [DECEMBER]
FROM (SELECT EXPENSERECORD.Category [Category],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'January' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JANUARY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'February' THEN SUM(EXPENSERECORD.AMOUNT) END AS [FEBRUARY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'March' THEN SUM(EXPENSERECORD.AMOUNT) END AS [March],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'April' THEN SUM(EXPENSERECORD.AMOUNT) END AS [APRIL],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'May' THEN SUM(EXPENSERECORD.AMOUNT) END AS [MAY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'June' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JUNE],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'July' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JULY],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'August' THEN SUM(EXPENSERECORD.AMOUNT) END AS [AUGUST],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'September' THEN SUM(EXPENSERECORD.AMOUNT) END AS [SEPTEMBER],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'October' THEN SUM(EXPENSERECORD.AMOUNT) END AS [OCTOBER],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'November' THEN SUM(EXPENSERECORD.AMOUNT) END AS [NOVEMBER],
CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'December' THEN SUM(EXPENSERECORD.AMOUNT) END AS [DECEMBER]
FROM REVENUE
LEFT JOIN EXPENSERECORD ON REVENUE.transactionID =  EXPENSERECORD.transactionID
GROUP BY  REVENUE.transactionDate, EXPENSERECORD.Category) A
WHERE A.Category IS NOT NULL
GROUP BY A.Category```
Rate this:

## Solution 2

SELECT
er.category `Category`,
SUM(IF(transactionDate between '2013-01-01' and '2013-01-31', r.totalamount, 0)) Jan,
SUM(IF(transactionDate between '2013-02-01' and '2013-02-31', r.totalamount, 0)) Feb,
SUM(IF(transactionDate between '2013-03-01' and '2013-03-31', r.totalamount, 0)) Mar,
SUM(IF(transactionDate between '2013-04-01' and '2013-04-31', r.totalamount, 0)) Apr,
SUM(IF(transactionDate between '2013-05-01' and '2013-05-31', r.totalamount, 0)) May,
SUM(IF(transactionDate between '2013-06-01' and '2013-06-31', r.totalamount, 0)) Jun,
SUM(IF(transactionDate between '2013-07-01' and '2013-07-31', r.totalamount, 0)) Jul,
SUM(IF(transactionDate between '2013-08-01' and '2013-08-31', r.totalamount, 0)) Aug,
SUM(IF(transactionDate between '2013-09-01' and '2013-09-31', r.totalamount, 0)) Sept,
SUM(IF(transactionDate between '2013-10-01' and '2013-10-31', r.totalamount, 0)) Oct,
SUM(IF(transactionDate between '2013-11-01' and '2013-11-31', r.totalamount, 0)) Nov,
SUM(IF(transactionDate between '2013-12-01' and '2013-12-31', r.totalamount, 0)) Dece

from revenue as r
LEFT JOIN expenserecord er ON R.transactionID=ER.transactionID

GROUP BY
er.category

Top Experts
Last 24hrsThis month
 OriginalGriff 240 RDBurmon 100 CPallini 60 Sergey Alexandrovich Kryukov 49 CHill60 40
 Dave Kreskowiak 1,841 Richard MacCutchan 1,260 OriginalGriff 1,114 d@nish 980 CPallini 810