January 18, 2007 at 12:56 pm
Hi,
I'd like to have some insight on how to approach this programming issue. I'm building a budgeting system and every quarter what is required is to copy the previous quarter's data and set an editable or not editable flag to 1 or 0. This flag enables the ability to edit only current data if the flag is not set then the data is read-only.
I have all the pieces in place, however I'm trying to build the logic within a sproc that get's the previous quarter data that had the flag to 1 and copy that data. I also have a field call plan_version which I want to add to the new records. For example, below I have a list of plan version names. I currently have plan_version name as '2007 Budget', I received the go-ahead from the business next quarter to copy the data from '2007 Budget' to '2007 Q2F' and make the previous quarter read-only and current quarter to editable. How can I tackle this programmatically without manually adjusting the sproc every quarter???
Thanks,
| 1 | 2007 Budget |
| 2 | 2007 Q2F |
| 3 | 2007 Q3F |
| 4 | 2007 CH |
| 5 | 2008 Budget |
| 6 | 2008 Q2F |
| 7 | 2008 Q3F |
| 8 | 2008 CH |
| 9 | 2009 Budget |
| 10 | 2009 Q2F |
| 11 | 2009 Q3F |
| 12 | 2009 CH |
| 13 | 2010 Budget |
| 14 | 2010 Q2F |
| 15 | 2010 Q3F |
| 16 | 2010 CH |
January 18, 2007 at 2:39 pm
"How can I tackle this programmatically without manually adjusting the sproc every quarter???"
I would create your stored procedure so that it accepts the existing plan_version and new plan_version as a parameters. Your stored procedure logic will then move the data accordingly. With that said, I'm still not 100% clear as to what you are looking for. I hope this helps. If you need more help, it may help if you could post your table DDL or your esisting stored procedure and maybe some sample data.
January 19, 2007 at 6:15 am
Thanks John,
You trigger a thought process on how to handle this issue.
I'm currently in design mode.
I'm leaning on having an admin form and like you said passing the new plan version and the old plan version as parameters. That way I feel you would simply just update the flag to 0 using the old plan version parameter and pass the new parameter in the insert statement.
I would add some checks in some type of business object ensuring that the next plan version is the proper version.
Thanks again
January 19, 2007 at 10:56 am
Since you are currently in design mode, consider an alternative if you are using SQL Server 2000:
1. Partition the data such that each quarter's data is in a different table.
2. Have a view that references the current data.
3. Have a different view that provides access to all of the historical data.
4. Use the DENY security statement to prevent updates to historical budget information.
For example:
Create table Budgets_2006Q01
( FiscalYearsmallintnot null
, FiscalQuartertinyintnot null
, OtherKeys?not null
, BudgetAmtintegernot null-- whole dollars only
, constraint Budgets_2006Q01_P primary key (FiscalYear, FiscalQuarter, OtherKeys )
, constraint Budgets_2006Q01_C_FiscalYear check (FiscalYear= 2006)
, constraint Budgets_2006Q01_C_FiscalQuarter check (FiscalQuarterr=1 )
)
go
-- repeat the "create table" for the other Fiscal periods as needed changing the table name to reflect the appropriate values for the FiscalYear and FiscalQuarter and the check constraint values
-- Create two views
create view Budgets
as select * from Budgets_2006Q01
union all select * from Budgets_2006Q02
union all select * from Budgets_2006Q03
union all select * from Budgets_2006Q04
union all select * from Budgets_2007Q01
go
create view Budgets_current
as select * from Budgets_2007Q01
go
-- Insure that "old" budget information cannot be changed:
deny insert, update, delete on Budgets_2006Q01 to public.
grant insert, update, delete on Budgets_2007Q01 to SecurityGroupName
When budget information for a new quarter is to start:
1.Create a new Budgets partitioning table such as Budgets_2007Q02
2.Alter the view Budgets_current to select only from the "current" budgets table
3.Alter the view Budgets to include the new "current" budgets table
4.Deny insert, update, delete on the prior budgets table
5.Grant insert, update, delete on the new "current" budgets table to the security group.
6.Rebuild the indexes on the prior budgets table
with a fill factor of 100 (no free space)
To prevent updates, an alterntive to the DENY, is to have a trigger on each of the historical tables that rejects any attempt:
create trigger Budgets_2006Q01_StopChanges
on Budgets_2006Q01
from insert, update, delete
as
RAISERROR('Updates to Historical Budget information is not allowed',16,1)
rollback
go
SQL = Scarcely Qualifies as a Language
January 19, 2007 at 1:16 pm
Carl's solution will work quite nicely if you can swing it. Instead of moving all of your data, you change the view to point to the current data. I know that you would only need to move the data once per quarter, but this method would sure save you in the cost of physically moving the data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply