select sum from two tables where year is dynamic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select sum from two tables where year is dynamic

Author

Topic

kblurry
Starting Member

6 Posts

Posted - 2011-12-10 : 11:22:51

i have two tables:
table 1:
Amount
PostDate
table 2:
Cost
Revenue
ClockDate

Revenue = Revenue
TotalCost = Amount + Cost
Profit = Revenue - TotalCost

i have to sum the two tables according to month and year so it would i guess look like:

month | TotalCost | Revenue | Profit | Year | TotalCost | Revenue | Yr
1 ------------------------------------2010*-----------------------2011
2 ------------------------------------2010*-----------------------2011
3 ------------------------------------2010*-----------------------2011
4 ------------------------------------2010*-----------------------2011
5 ------------------------------------2010*-----------------------2011
6 ------------------------------------2010*-----------------------2011
7 ------------------------------------2010*-----------------------2011
8 ------------------------------------2010*-----------------------2011
9 ------------------------------------2010*-----------------------2011
10 ------------------------------------2010*----------------------2011
11 ------------------------------------2010*----------------------2011
12------------------------------------2010*-----------------------2011

*must be the year that is in the database(dynamic year) up to current year (2011)

i have this query to start:


SELECT *
FROM (
  SELECT TOP 100 2003 + ROW_NUMBER() OVER (ORDER BY PostingDate) AS Yr, SUM(Amount) as Total
  FROM table1
  GROUP BY PostingDate
  ) Years
WHERE Yr <= YEAR(GETDATE())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 11:35:13

you sample output doesnt make much sense. what would be value of other columns? also whats the column based on which you need to determine the year?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kblurry
Starting Member

6 Posts

Posted - 2011-12-10 : 11:46:05

This is what the empty columns value will be:


Revenue = Revenue
TotalCost = Amount + Cost
Profit = Revenue - TotalCost

i need all the years in the database, so if i have 2009 as the least year in the database, i need the Revenue, TotalCost,Profit for that year.

like: for 2009 the cost,revenue,profit would be listed by month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 12:12:23

you dont have year column in database. that's why i asked which column to be considered for taking year values (PostDate or ClockDate)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kblurry
Starting Member

6 Posts

Posted - 2011-12-10 : 12:58:51

The year date is supposed to be the new column that combines the year PostingDate of table 1 and ClockDate of table 2.

something like:


SELECT [Month], SUM(Cost) AS TotalCost, SUM(Revenue) AS Revenue, SUM(Revenue - Cost) AS Profit
FROM (
        SELECT MONTH([PostingDate]) AS [Month], SUM(Amount) AS Cost, 0 AS Revenue
        FROM table1
        where YEAR([PostingDate]) = '2010'
        GROUP BY MONTH([PostingDate])
        UNION ALL
        SELECT MONTH([ClockDate]),  SUM(Cost), SUM(Revenue)
        FROM table2
           where YEAR([ClockDate]) = '2010'
        GROUP BY MONTH([ClockDate])
) tmp

GROUP BY [Month]
ORDER BY [Month]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 01:09:30

ok...so now whats the issue you're facing with posted code above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kblurry
Starting Member

6 Posts

Posted - 2011-12-11 : 16:49:28

the issue is I need the year to be dynamic, the where year = '2010' should not be fixed. It needs to figure out the year in my database without me specifying it to be 2009,2010 etc. The where clause should be where year = //query the database for all years up to current year which is (getdate)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 23:40:56

you mean this?


SELECT [Year],[Month], SUM(Cost) AS TotalCost, SUM(Revenue) AS Revenue, SUM(Revenue - Cost) AS Profit
FROM (
        SELECT YEAR([PostingDate]) AS [Year],MONTH([PostingDate]) AS [Month], SUM(Amount) AS Cost, 0 AS Revenue
        FROM table1
        GROUP BY YEAR([PostingDate]),MONTH([PostingDate])
        UNION ALL
        SELECT YEAR([ClockDate]) ,MONTH([ClockDate]),  SUM(Cost), SUM(Revenue)
        FROM table2
        GROUP BY YEAR([ClockDate]),MONTH([ClockDate])
) tmp

GROUP BY [Year],[Month]
ORDER BY [Year],[Month]