Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-14 : 10:55:41
|
Ed writes "Here is the problem.
We have a table that holds monthly data. The table was created with the following script:
CREATE TABLE ACCT_FLOW
(
[ACCOUNT_ID] [int] NOT NULL ,
[YYYY] [int] NOT NULL ,
[MM] [tinyint] NOT NULL ,
[ADDITION] [numeric](18, 2) NOT NULL ,
[WITHDRAWAL] [numeric](18, 2) NOT NULL ,
[NET] AS ([ADDITION] - [WITHDRAWAL]) ,
[BALANCE] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
ALTER TABLE [ACCT_FLOW] WITH NOCHECK ADD
CONSTRAINT [PK_ACCT_FLOW] PRIMARY KEY CLUSTERED
( [ACCOUNT_ID], [YYYY], [MM] ) ON [PRIMARY]
GO
ALTER TABLE [ACCT_FLOW] WITH NOCHECK ADD
CONSTRAINT [DF_ACCT_FLOW_ADDITION] DEFAULT (0)
FOR [ADDITION],
CONSTRAINT [DF_ACCT_FLOW_WITHDRAWAL] DEFAULT (0)
FOR WITHDRAWAL],
CONSTRAINT [DF_ACCT_FLOW_BALANCE] DEFAULT (0)
FOR [BALANCE]
GO
Here's the story. The data for this table comes every quarter. It consists of the ACCOUNT_ID field, the year field (YYYY), Month field (MM), the ADDITIONS and the WITHDRAWALS. You already have a BCP layout for importing the quarterly data and you have written a "BULK INSERT" process for filling the table. Now all that is left is to calculated the monthly account balance for the each of the last three months.
The formula for balance is as follows:
BALANCE(i) = BALANCE(i-1) + NET where i = month number
In other words, the balance for this month is equal to the balance of last month plus this months NET value.
How do you do it? By the way, your development manager says NO CURSORS allowed!!!!
Enjoy!!!" |
|
|
|
|
|
|
|
How do you do a cascading update?