Calculations and then Pivot? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calculations and then Pivot?

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2009-02-04 : 15:31:20

I have a table with project, start date, end date, budget. What I would like to do is try calculate what was spent each year of the budget, based on the number of months it ran, and the budget amount.

So, for example, project one started on 1/15/2007 and ended on 3/14/2008. Its budget was $101,000.

What I want to end up with is colums like:

Project, startDate, endDate, budget, 2007, 2008

and the values for 2007 and 2008 would be $80,800 and $20,200 respectively.

The projects in the table run from 1995 to 2020.

I have no idea how to start. I know how to get the yearly totals in my mind,(for first year, if startdate and enddate are not the same, then the amount spent in the first year would be the the number of months in the first year (in this example 12), times the budget, divided by the complete number of months) but don't know how to code it.

Any ideas on how I can get started?

Thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 15:42:47

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2009-02-04 : 15:49:27

Sorry. I didn't realize my question was not in the correct format. Thanks for pointing it out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 15:56:55

[code]DECLARE @Sample TABLE
(
ProjectID INT,
Starting DATETIME,
Ending DATETIME,
Budget MONEY
)

INSERT @Sample
SELECT 1, '20070115', '20080314', 101000

-- Per day
SELECT ProjectID,
Starting,
Ending,
SUM(CASE WHEN theYear = 2007 THEN perDay ELSE 0 END) AS [2007],
SUM(CASE WHEN theYear = 2008 THEN perDay ELSE 0 END) AS [2008]
FROM (
SELECT s.ProjectID,
s.Starting,
s.Ending,
DATEPART(YEAR, DATEADD(DAY, v.Number, s.Starting)) AS theYear,
Budget / (1.0E + DATEDIFF(DAY, s.Starting, s.Ending)) AS perDay
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number <= DATEDIFF(DAY, s.Starting, s.Ending)
) AS d
GROUP BY ProjectID,
Starting,
Ending
ORDER BY ProjectID

-- Per month
SELECT ProjectID,
Starting,
Ending,
SUM(CASE WHEN theYear = 2007 THEN perMonth ELSE 0 END) AS [2007],
SUM(CASE WHEN theYear = 2008 THEN perMonth ELSE 0 END) AS [2008]
FROM (
SELECT s.ProjectID,
s.Starting,
s.Ending,
DATEPART(YEAR, DATEADD(MONTH, v.Number, s.Starting)) AS theYear,
Budget / (1.0E + DATEDIFF(MONTH, s.Starting, s.Ending)) AS perMonth
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number <= DATEDIFF(MONTH, s.Starting, s.Ending)
) AS d
GROUP BY ProjectID,
Starting,
Ending
ORDER BY ProjectID[/code]

E 12°55'05.63"
N 56°04'39.26"

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2009-02-04 : 16:35:42

Thanks - it will take me time to understand what it all does, but thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 16:40:51

Thanks.

E 12°55'05.63"
N 56°04'39.26"

bmahony993
Yak Posting Veteran

58 Posts