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 |
|
Scott
Posting Yak Master
145 Posts |
 Posted - 2001-07-18 : 06:51:26
|
| I have the following 2 tables:dbo.FundsYear | Budget--------------1 | 20 0002 | 10 000dbo.ProjectPhase | Cost | Priority---------------------------1 | 15 000 | 12 | 4 000 | 23 | 5 000 | 34 | 4 000 | 4The cursor below tries to write to the table #Year1 all the phases that can be accomadated for each budgeted year. ie Table #Year1 should look like this:Phase | Cost | Priority | Year----------------------------------1 | 15 000 | 1 | 12 | 4 000 | 2 | 13 | 5 000 | 3 | 24 | 4 000 | 4 | 2---------------------------------------------------------------------------CREATE TABLE dbo.#YEAR1 ( Phase int NULL , [Cost] money NULL , Priority int NULL , [Year] int NULL ) ON [PRIMARY]DECLARE @Phase int, @Cost money, @Priority int, @Year int, @Budget moneyDeclare c2 CURSOR forSELECT[Year], BudgetFROM dbo.Fundsorder by [Year]open c2fetch next from c2into @Year, @BudgetWhile @@Fetch_Status = 0BEGIN DECLARE @Budget1a int, @Budget1b int SELECT @Budget1b = 0 SELECT @Budget1a = @Budget Declare c1 CURSOR for SELECT Phase, [Cost], Priority From dbo.Project order by Priority open c1 fetch next from c1 into @Phase, @Cost, @Priority --While @@Fetch_Status = 0 WHILE (@Budget1a > @Budget1b) Begin If @Budget1b + @Cost > @Budget1a Begin SELECT @Budget1b = @Budget1b + @Cost END Else BEGIN INSERT INTO DBO.#YEAR1 (Phase, Cost, Priority, [Year]) VALUES (@Phase, @Cost, @Priority, @Year) SELECT @Budget1b = @Budget1b + @Cost END fetch next from c1 into @Phase, @Cost, @Priority END CLOSE c1 DEALLOCATE c1fetch next from c2into @Year, @BudgetENDCLOSE c2DEALLOCATE c2select * from #YEAR1select SUM(cost) as Cost, [Year] from #YEAR1 group by [Year]drop table dbo.#YEAR1 |
|
|
|
|
|
|
|
Cursor