Solved

need to create a summary table

Posted on 2012-03-20
25
245 Views
Last Modified: 2012-03-22
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.
0
Comment
Question by:mirthless
  • 15
  • 9
25 Comments
 
LVL 13

Expert Comment

by:alpmoon
Comment Utility
"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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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 Comment

by:mirthless
Comment Utility
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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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
      SET @ToDt = DATEADD(D,1,@ToDt)
      -- 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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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 Comment

by:mirthless
Comment Utility
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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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
  and    month(dateadd(month,35,'2009-01-06 00:00:00.000'))
       <= 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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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 @FirstDayNextMonth = dateadd(MONTH,1,@InputDate)
    SET @LastDayofMonth = CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +
                          CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01'
    SET @LastDayofMonth = DATEADD(DD, -1, DATEADD(M, 1, @LastDayofMonth))


    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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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 Comment

by:mirthless
Comment Utility
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
  and    month(dateadd(month,35,'2009-01-06 00:00:00.000'))
       <= 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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:mirthless
Comment Utility
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
  and    month(dateadd(month,35,'2009-01-06 00:00:00.000'))
       <= 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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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)
and    RptLastMonthDate <= dateadd(month,35,'2009-01-06 00:00:00.000')  

You will of course change the hard coding to feed in your startdates variables...
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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 Comment

by:mirthless
Comment Utility
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
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 500 total points
Comment Utility
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 @FirstDayNextMonth = dateadd(MONTH,1,@InputDate)
    SET @LastDayofMonth = CAST(YEAR(@InputDate) AS VARCHAR(4)) + '-' +
                          CAST(MONTH(@InputDate) AS VARCHAR(2)) + '-01'
    SET @LastDayofMonth = DATEADD(DD, -1, DATEADD(M, 1, @LastDayofMonth))
    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
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
On the first month.  If you simply set the day portion to '01' it will correct the situation....
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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 Comment

by:mirthless
Comment Utility
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 Comment

by:mirthless
Comment Utility
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))
and datepart(year,RptLastMonthDate) <= datepart(year,dateadd(month,35,'2009-01-06 00:00:00.000'))
and datepart(MONTH,RptLastMonthDate) <= datepart(MONTH,dateadd(month,35,'2009-01-06 00:00:00.000'))

Now onto using this nice code with my source data so I can run it off of variables.
0
 

Author Comment

by:mirthless
Comment Utility
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))
and    RptLastMonthDate <= dateadd(month,35,'2009-01-06 00:00:00.000')
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
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
and    RptLastMonthDate <= dateadd(month,35,@newinputdate )  
order by   RptFirstMonthDate
0
 

Author Comment

by:mirthless
Comment Utility
That works too...probably easier to work in a "proration" date if needed with this method.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
Sorry,  I was working on my method and did not see your posts until after I had posted mine.  I call it target fixation... lol !   Both work :-)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now