need to create a summary table

I have a request to summarize projects by average monthly amounts for the duration of the project, then plot on a report.

So far I have created the duration for a project with the total and average monthly amount like so:

Project     StartDate                               Duration (in months)   monthly amount  total amount
ABC           2009-01-06 00:00:00.000                 35                               20000                 700000

Now I am kinda gettin fuzzy where I go from here. I was thinking I should build another table with a row for each project with 12 fields for months and fill in the monthly amounts, one field for the each project (ABC), and one column for the year.

In this case it would take 3 separate rows one for 2009, one for 2010, and ond for 2011.

The idea is to be able to sum up all of the average monthly amounts for each year, then plot them on an SSRS graph report.

After I have this, I am still trying to figure out how I am going to plot this data on a graph. Keep in mind that I am talking about 500 - 1000 projects that span anywhere from 6 to 36 months each. I expect the final graph will span one year at a time.

Any help getting me started in the right direction would be greatly appreciated.
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
"I was thinking I should build another table with a row for each project with 12 fields for months and fill in the monthly amounts, one field for the each project (ABC), and one column for the year."

I think it would make the whole thing complicated. A more structured approach is:

Project  Year  Month   Amount
ABC       2009   12        2000
ABC       2010   01        1500
0
Commented:
Have you looked at the pivot functionality of sql server. I think it may give you what you want?  I could not work up an example without more input data from you.
0
Author Commented:
Thanks, I can use the pivot approach as well. My biggest problem right now is how to step through my source data to create the data by month and year over the duration of the project. I assume some type of for next loop, but I am not quite sure the best way to accomplish this. I think if I could get this figured I would be well on my way.

Ideas on looping through to create as many rows as needed for each project .

Project     StartDate                               Duration (in months)   monthly amount  total amount
ABC           2009-01-06 00:00:00.000                 35                               20000                 700000

In this case I would expect to create 35 rows of data that I can then aggregate.
0
Commented:
This example may help.  It develops a variable list of payment types first and feeds it to the pivot.  I wondered if this is what you were having problems with. As I was at first for a variable axis...

USE [xxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [SummaryByDept]
@FromDt varchar(20),
@ToDt varchar(20)
AS
BEGIN
-- Pivot Table ordered by PmtType

DECLARE @PmtTypeList varchar(max)
DECLARE @PmtTypePivot varchar(max)
DECLARE @sql varchar(max)

SELECT @PmtTypeList = (
SELECT DISTINCT '[' + RTRIM(PmtType) + '],'
FROM  dbo.mytablenam
ORDER BY '[' + RTRIM(PmtType) + '],'
FOR XML PATH('')
)
SET @PmtTypeList = LEFT(@PmtTypeList, LEN(@PmtTypeList) - 1)

SELECT @PmtTypePivot = (
SELECT DISTINCT 'MAX([' + RTRIM(PmtType) + ']) AS [' + RTRIM(PmtType) + '],'
FROM dbo.mytablenam
ORDER BY 'MAX([' + RTRIM(PmtType) + ']) AS [' + RTRIM(PmtType) + '],'
FOR XML PATH('')
)
SET @PmtTypePivot = LEFT(@PmtTypePivot, LEN(@PmtTypePivot) - 1)

SELECT @PmtTypeList as '@PmtType List', @PmtTypePivot as '@PmtTypePivot'

SET @sql = '
SELECT  ISNULL(DeptCd, '+'''Unknown' +''') AS DeptCd,  ' + @PmtTypePivot + '
FROM (
SELECT DeptCd  , Pmttype, AmtApplied as PmtTotal
FROM       dbo.mytablenam
WHERE     (TransDt BETWEEN ''' + @FromDt + ''' AND ''' + @ToDt + ''' )
) AS baseTable
PIVOT
(
SUM(PmtTotal)
FOR PmtType IN (' + @PmtTypeList + ')
) AS pvtTable
GROUP BY
DeptCd
ORDER BY
DeptCd
'

----PRINT @sql

EXEC(@sql)

END
0
Commented:
If I might offer another suggestion, I think you are working backwards.  First mock up the report. then the data the report wants as input and work backwards from there.  The answer to your question should be easy as soon as you see which report you choose as best representing how the user wants to view the project data.
0
Author Commented:
Maybe I am missing something. Let me try to explain where I am stuck a little better and you can set me straight where this procedure may ot may not handle it.

At this time here is my source data:

Project     StartDate                               Duration (in months)   monthly amount  total amount
ABC           2009-01-06 00:00:00.000                 35                               20000                 700000

It appears to me that I need to create table something like this

Project   Year    Month    Amount
ABC        2009       01        20000
ABC        2009       02        20000
ABC        2009       03        20000
ABC        2009       04        20000
ABC        2009       05        20000
ABC        2009       06        20000
ABC        2009       07        20000
ABC        2009       08        20000
ABC        2009       09        20000
ABC        2009       10        20000
ABC        2009       11        20000
ABC        2009       12        20000
ABC        2010       01        20000
ABC        2010       02        20000
ABC        2010       03        20000
ABC        2010       04        20000
ABC        2010       05        20000
ABC        2010       06        20000
ABC        2010       07        20000
ABC        2010       08        20000
ABC        2010       09        20000
ABC        2010       10        20000
ABC        2010       11        20000
ABC        2010       12        20000
ABC        2011       01        20000
ABC        2011       02        20000
ABC        2011       03        20000
ABC        2011       04        20000
ABC        2011       05        20000
ABC        2011       06        20000
ABC        2011       07        20000
ABC        2011       08        20000
ABC        2011       09        20000
ABC        2011       10        20000
ABC        2011       11        20000

Then apply a pivot function on this data being the source yes?

I first need to be able to get from my source data to the 35 row table. Here is where I am stuck.

In regards to the final report, I am not so sure that I need to go with the pivot structure or not.

In the end the report will graph out an aggregate of monthly totals of the amount over time. The report will span one year at a time and will depict total costs of all projects on a line graph.

If I am able to create a table similar to the one above, I can then select over a specific time frame then aggregate according to the month/year pretty easily I think.

Then simply display on a graph.
0
Commented:
Oh!  you just want to create rows from the start of the project until all the money is projected to be consumed.  Give me a few minutes to think how I would best do this....
0
Commented:
I think I would make the assumption that I would be doing a fair number of reports by months.  And I do not know that it is a sql server strong point.  Date dimensions are fairly standard for reporting structures and I think I would go ahead and create one for this endevor if it would be me.  because it would amke my life so much easier for the rest of it.

He is a very simple example of wht it would be like.  (Except that I would use sql functions for last day of month and better time values on the datetime fields if it were to go in production on my box...)

drop table reportdates
go
create table reportdates
(RptYear smallint,
RptMonth tinyint,
RptFirstMonthDate datetime,
RptLastMonthDate datetime)
GO
insert into reportdates values (2009, 01 , '2009-01-01' , '2009-01-31');
go
insert into reportdates values (2009, 02 , '2009-01-02' , '2009-02-28');
go
insert into reportdates values (2013, 01 , '2013-01-01' , '2013-01-31');
go
insert into reportdates values (2013, 02 , '2013-01-02' , '2013-02-28');
go

SELECT RptYear , RptMonth from reportdates
where '2009-01-06 00:00:00.000' >= RptFirstMonthDate
<= RptLastMonthDate

In effect this added the duration you gave me in months to the starting date and looked for rows in the monthly reporting table that fell in the range.  That made it a no brainer....
0
Commented:
You may or may not be interested in this technique.  If you are here is logic to create the reporting dates table:
create FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @InputDate  datetime )
RETURNS DATETIME
BEGIN
DECLARE @FirstDayNextMonth       DATETIME
DECLARE @LastDayofMonth      DATETIME
SET @LastDayofMonth = CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01'

RETURN @LastDayofMonth

END
GO
create FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @InputDate  datetime )
RETURNS DATETIME
BEGIN
DECLARE @FirstDayofMonth       DATETIME

SET @FirstDayofMonth = CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01'
RETURN @FirstDayofMonth

END
GO

delete from dbo.reportdates
go

declare      @start datetime
,@end  datetime
,@date datetime
,@hour int
select   @start = '2012-01-01 00:00'
,@end =   '2015-12-31 23:59'
set @date = @start
while @date < @end
begin
set @hour = 0
insert into dbo.reportdates
([RptYear]
,[RptMonth]
,[RptFirstMonthDate]
,[RptLastMonthDate])
values(  (datepart(year, @date))
, (datepart(month, @date))
,[dbo].[ufn_GetFirstDayOfMonth] (@date)
,[dbo].[ufn_GetLastDayOfMonth](@date)
)
set @hour = @hour + 1;
set @date = dateadd(month, 1, @date)
end
0
Commented:
Sorry, I should ahve removed references to hour.  Please ignore them.    One advantage to this method is that all reports can use the same table and indexes can be built to speed access rather than having each report try to build what are really standard 'buckets' with standard cutoffs.  It also allows for changes if the company decides that they want the cutoff to occur on the last Saturday of the month instead of month-end as you have clearly seperated the functions of defining the reporting periods and accumulating the totals.
0
Author Commented:
ok running into issue here...

after creating the dimension table I run the code

SELECT RptYear , RptMonth from reportdates
where '2009-01-06 00:00:00.000' >= RptFirstMonthDate
<= RptLastMonthDate

against it...returning only one record. I assume I must have missed something in my explanation of what I need. I need to generate 35 rows of data based on the source data I described. Basically, because the project is anticipated to have a duration of 35 months, I need to generate 35 different records that I can populate with the monthly amount which is already documented in the source data.
0
Commented:
Oops...  I started my population from 2012...  Rerun this and it should repopulate from 2008 and thus include all 2009 dates.  My apologies....   After it runs.  Select * from the table and confirm it covers the years you expect the projects to cover...

delete from dbo.reportdates
go

declare      @start datetime
,@end  datetime
,@date datetime
,@hour int
select   @start = '2008-01-01 00:00'
,@end =   '2015-12-31 23:59'
set @date = @start
while @date < @end
begin
set @hour = 0
insert into dbo.reportdates
([RptYear]
,[RptMonth]
,[RptFirstMonthDate]
,[RptLastMonthDate])
values(  (datepart(year, @date))
, (datepart(month, @date))
,[dbo].[ufn_GetFirstDayOfMonth] (@date)
,[dbo].[ufn_GetLastDayOfMonth](@date)
)
set @hour = @hour + 1;
set @date = dateadd(month, 1, @date)
end
0
Author Commented:
Ok so far so good, the function and dimension works great

...problem is again using

SELECT RptYear , RptMonth from reportdates
where '2009-01-06 00:00:00.000' >= RptFirstMonthDate
<= RptLastMonthDate

I now return the 13 rows prior to '2009-01-06 00:00:00.000'  including 01 2009. I should be returning 35 rows starting at 01 2009 and ending with 12 2011
0
Commented:
Second apology. I was careless with my query and had it reversed.

SELECT RptYear , RptMonth ,cast('2009-01-06 00:00:00.000' as datetime) AS BASEDATE, (dateadd(month,35,'2009-01-06 00:00:00.000')) AS ENDDATE,  * from reportdates
where  RptFirstMonthDate >= cast('2009-01-06 00:00:00.000' as datetime)

You will of course change the hard coding to feed in your startdates variables...
0
Commented:
And if you want the first partial month included, the day needs to be changed to '01' or it defaults to showing for the next month...  In the example it started on the 6th so technically it should be prorated?
0
Author Commented:
better, one little detail, we are missing the first month

should start with 01 2009, I am thinking we should be using >= datepart month, year of start date?? something like that
0
Commented:
I also updated the functions to populate the times better on the end of month.  But I cannnot for the life of me get Sql Server to accept the last .999   I am still lookinig into that particular snag...  I love Sql server, but datetimes are never fun...

drop FUNCTION [dbo].[ufn_GetLastDayOfMonth]
go

create FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @InputDate  datetime )
RETURNS DATETIME
BEGIN
DECLARE @FirstDayNextMonth       DATETIME
DECLARE @LastDayofMonth      DATETIME
SET @LastDayofMonth = CAST(YEAR(@InputDate) AS VARCHAR(4)) + '-' +
CAST(MONTH(@InputDate) AS VARCHAR(2)) + '-01'
SET @LastDayofMonth = CAST((@LastDayofMonth) AS VARCHAR(11)) + cast(' 23:59:59.997' as varchar(13))

RETURN @LastDayofMonth

END
GO
drop FUNCTION [dbo].[ufn_GetFirstDayOfMonth]
GO
create FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @InputDate  datetime )
RETURNS DATETIME
BEGIN
DECLARE @FirstDayofMonth       DATETIME

SET @FirstDayofMonth = CAST(YEAR(@InputDate) AS VARCHAR(4)) + '-' +
CAST(MONTH(@InputDate) AS VARCHAR(2)) + '-01 00:00'
RETURN @FirstDayofMonth

END
GO
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
On the first month.  If you simply set the day portion to '01' it will correct the situation....
0
Commented:
Maybe I should elaborate.  For now, i would just set the date used in the comparison to '01'.  the reason beingthat i would expect them to realize that they want to consider partial months differently.  I expect that they will either tell you to prorate or come back with a cutoff date. For example, if a project starts on or before the 20th include the start month.  if after the 20th start the next month...  So i still think the day will play a part her and I'd leve in the full datetime comparison and get on with the plotting to get them something to tweak further :-)
0
Author Commented:
I agree and have given that thought already, I will certainly need a way to include the first month based on that cutoff date. Any ideas you have in that direction would be helpfull. I will give this a little more of a spin to see if I can tweak it for the cutoff date. I may have more questions regarding this project but will post them in another Post if needed.
0
Author Commented:
This appears to have taken care of it for now. Using this adjustment I am just using the entire first month and last month no matter which date it falls on.

SELECT RptYear , RptMonth ,cast('2009-01-06 00:00:00.000' as datetime) AS BASEDATE,
(dateadd(month,35,'2009-01-06 00:00:00.000')) AS ENDDATE,  * from reportdates
where  datepart(year,RptFirstMonthDate) >= datepart(year,cast('2009-01-06 00:00:00.000' as datetime))
and  datepart(month,RptFirstMonthDate) >= datepart(month,cast('2009-01-06 00:00:00.000' as datetime))

Now onto using this nice code with my source data so I can run it off of variables.
0
Author Commented:
correction, only use the datepart on the start date not the ending date

this is correct

SELECT RptYear , RptMonth ,cast('2009-01-06 00:00:00.000' as datetime) AS BASEDATE,
(dateadd(month,35,'2009-01-06 00:00:00.000')) AS ENDDATE,  * from reportdates
where  datepart(year,RptFirstMonthDate) >= datepart(year,cast('2009-01-06 00:00:00.000' as datetime))
and  datepart(month,RptFirstMonthDate) >= datepart(month,cast('2009-01-06 00:00:00.000' as datetime))
0
Commented:
Replace the day with '01' in the comparison.

go
declare @inputdate datetime
declare @newinputdate datetime
set @inputdate = '2009-01-06 00:00:00.000'
set @newinputdate = cast(datepart(year,@inputdate) as varchar(4)) + '-' + cast(datepart(month,@inputdate) as varchar(2)) + '-' + '01'
SELECT * from reportdates
where  RptFirstMonthDate >= @newinputdate