January 7, 2009 at 1:19 pm
I am creating a report in SRSS from a database that does not maintain historical data. To accomplish this, I plan on creating a table that will hold the historical data (reported on for a rolling 90-day period). The new table will contain the same data as the source table except that I will want to add a new column on the 1st and 15th of each month. There will be new rows of data as well and I will need to add those as they appear. Since the report will only show the last 90 days (6 columns), I need to devise a method of updating the columns in such a way that I don't have to edit the report each time.
I anticipate creating a job that will run on schedule to insert the data from the source into the new table, and that I will need to alter the table/columns in the process.
Source Table will contain data like this:
owneridname, accountid, salesstagecodename
John Doe, Apache Junction Bait Shop, Prospect
John Doe, Archdiocesan Bait Shop Services,Closed
Jim Beam, Arkansas Valley Regional Bait Shop, Lost
Jim Beam, AZ Bait Shop Facility of Scottsdale, Suspect
Jim Beam, Beatitudes Campus, Prospect
Jane Doe, Benson Bait Shop, Prospect
New Table would look something like this after 4 cycles:
owneridname, accountid,stat010109,stat011509,stat020109,stat021509
John Doe, Allen Memorial Bait Shop,,,suspect,prospect
John Doe, Apache Junction Bait Shop, Prospect,prospect,lost,
John Doe, Archdiocesan Bait Shop Services,Closing,Closing,Closed,
Jim Beam, Arkansas Valley Regional Bait Shop, Lost,,,
Jim Beam, AZ Bait Shop Facility of Scottsdale, Suspect,Lost,,
Jim Beam, Beatitudes Campus, Prospect, Prospect, Prospect, Prospect
Jane Doe, Beaver Valley Bait Shop,,,engaged,Closing
Jane Doe, Benson Bait Shop, Prospect,prospect,prospect,engaged
Does anyone have thoughts on the most efficient way to do this? Any sample scripts from existing work?
January 8, 2009 at 6:28 am
Instead of adding an additional column every 15 days why not add a date column? This way you will still be able to track the progress(stage) of each owner/account...
Date, owneridname, accountid, salesstagecodename
January 8, 2009 at 6:54 am
I think that will work perfectly. I was more concerned about what the report would look like that I missed the obvious. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply