locked
how to show sum(cost) and revenue from two tables and display per month RRS feed

  • Question

  • User-1098969383 posted

    i have two tables:

    table 1:
    Expense cost
    Expense date

    table
    2:
    Employee cost
    Rvenue
    Employee date

    Totalcost = Expense cost + Employee cost
    Pofit = Revenue - TotalCost

    i have to display the report like so. It should show data per month according to year lets say 2011, (i know how to filter by year)

       Totalcost | Revenue | Profit
    jan
    feb
    mar
    apr
    may
    jun
    jul
    aug
    sep
    oct
    nov
    dec

    I'm having a tough time fugring out the query. The problem is how can i display the data in report viewer table

    Thursday, December 8, 2011 9:30 AM

Answers

  • User992646781 posted

    The following should do the trick:

    SELECT [Month], SUM(Cost) AS TotalCost, SUM(Revenue) AS Revenue, SUM(Revenue - Cost) AS Profit
    FROM (
    	SELECT MONTH([Date]) AS [Month], SUM(Cost) AS Cost, 0 AS Revenue
    	FROM Table1
    	GROUP BY MONTH([Date])
    	UNION ALL
    	SELECT MONTH([Date]), 0, SUM(Cost), SUM(Revenue)
    	FROM Table2
    	GROUP BY MONTH([Date])
    ) tmp
    GROUP BY [Month]
    ORDER BY [Month]
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 8, 2011 12:23 PM

All replies

  • User992646781 posted

    Table 1 and Table 2 you described do not have any dates in them. How is grouping supposed to work without dates? Also, is there a relationship between employee and expense?

    Thursday, December 8, 2011 9:56 AM
  • User-1098969383 posted

    sorry, i upated my post. yes there is a difference betwen expense cost and employee cost

    Thursday, December 8, 2011 11:54 AM
  • User992646781 posted

    The following should do the trick:

    SELECT [Month], SUM(Cost) AS TotalCost, SUM(Revenue) AS Revenue, SUM(Revenue - Cost) AS Profit
    FROM (
    	SELECT MONTH([Date]) AS [Month], SUM(Cost) AS Cost, 0 AS Revenue
    	FROM Table1
    	GROUP BY MONTH([Date])
    	UNION ALL
    	SELECT MONTH([Date]), 0, SUM(Cost), SUM(Revenue)
    	FROM Table2
    	GROUP BY MONTH([Date])
    ) tmp
    GROUP BY [Month]
    ORDER BY [Month]
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 8, 2011 12:23 PM