12,395,319 members (67,370 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 215 Jochen Arndt 130 Richard MacCutchan 90 Richard Deeming 80 khaled Ezzat 70
 OriginalGriff 5,998 Karthik Bangalore 2,382 ppolymorphe 2,350 F-ES Sitecore 1,877 Richard MacCutchan 1,707