13,146,739 members (84,803 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 15:10pm
Updated 13-Jul-13 8: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 305 Dave Kreskowiak 65 CPallini 60 RickZeeland 40 Jochen Arndt 30
 OriginalGriff 6,105 Graeme_Grant 5,061 ppolymorphe 1,984 Jochen Arndt 1,904 CPallini 1,815