April 19, 2007 at 3:36 am
I have a table of projects with project name, start date, end date, and budget columns. I need to be able to show the budget by month between the start and end dates. I know how to do this by selecting the data into Excel then adding the formulas in extra columns, but how do I do this in T-SQL so that I don't have to use Excel? BTW, I don't need to worry about fractions of months, approximate averages are adequate.
Output example:
Project, start, end, budget, Jan, Feb, March, Apr, May, June
1st project, 1/9/07, 10/15/07, 10000, 1000, 1000, 1000, etc
2nd project, 3/5/07, 6/20/07, 6000, 0 0 2000, 2000, 2000
TIA
Dean
April 19, 2007 at 3:49 am
Since we do not know the source DDL, we'll have to guess.
Something similar to
SELECT Col1,
SUM (CASE WHEN DATENAME(month, Col2) = 'january' then Col3 else 0 END) AS [January],
SUM (CASE WHEN DATENAME(month, Col2) = 'february' then Col3 else 0 END) AS [February],
...
from Table
Group by Col1
order by Col1
N 56°04'39.16"
E 12°55'05.25"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply