Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
 
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 product lists goes down ....
Posted 12-Jul-13 16:10pm
Edited 13-Jul-13 9:33am
v2
Comments
Sergey Alexandrovich Kryukov at 12-Jul-13 22:07pm
   
Sorry, not a question.
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 13 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100