Salakhetdinov Shamil
mcp2004 at mail.ru
Sun May 27 01:53:31 CDT 2012
Correction - *comments* should be changed - see below inline: --- + fixing code fragment -- allocation is less than details totals INSERT POAllocation VALUES (123456, 141.00) INSERT PODetail VALUES (123456, 50.00, 0) INSERT PODetail VALUES (123456, 20.00, 0) INSERT PODetail VALUES (123456, 76.00, 0) GO -- allocation is greater than details totals INSERT POAllocation VALUES (789012, 253.00) INSERT PODetail VALUES (789012, 99.00, 0) INSERT PODetail VALUES (789012, 101.00, 0) GO --- - fixing code fragment Thank you. -- Shamil Sun, 27 May 2012 10:43:37 +0400 от Salakhetdinov Shamil <mcp2004 at mail.ru>: > Hi Paul -- > > It's definitely a bad design case as noted here by Arthur and others but it looks like it can be handled without cursors at all: > > -- > -- Step ONE - Create sample tables > -- > IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[POAllocation]') AND type in (N'U')) > DROP TABLE [dbo].[POAllocation] > GO > > IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PODetail]') AND type in (N'U')) > DROP TABLE [dbo].[PODetail] > GO > > CREATE TABLE POAllocation (PONumber int, Allocated money) > CREATE TABLE PODetail (PONumber int, Amount money, IsAllocated bit, RowID int IDENTITY(1,1) NOT NULL) > go > ------ + replace it with the fixing code fragment > -- allocation is greater than details totals > INSERT POAllocation VALUES (123456, 141.00) > INSERT PODetail VALUES (123456, 50.00, 0) > INSERT PODetail VALUES (123456, 20.00, 0) > INSERT PODetail VALUES (123456, 76.00, 0) > GO > -- allocation is less than details totals > INSERT POAllocation VALUES (789012, 253.00) > INSERT PODetail VALUES (789012, 99.00, 0) > INSERT PODetail VALUES (789012, 101.00, 0) > GO ------ - replace it with the fixing code fragment > -- allocation is equal to details totals > INSERT POAllocation VALUES (345678, 101.00) > INSERT PODetail VALUES (345678, 9.00, 0) > INSERT PODetail VALUES (345678, 31.00, 0) > INSERT PODetail VALUES (345678, 20.00, 0) > INSERT PODetail VALUES (345678, 30.00, 0) > INSERT PODetail VALUES (345678, 11.00, 0) > GO > -- allocation without details > INSERT POAllocation VALUES (567890, 99.00) > GO > -- zero allocation, zero details > INSERT POAllocation VALUES (901234, 0.00) > GO > > select * from POAllocation > select * from PODetail > > -- > -- STEP TWO - Test > -- > > DECLARE @AllocationTbl TABLE > ( > RowId int, > PONumber int, > Amount money, > RunningTotal money, > Allocated money, > IsAllocated bit > ) > > DECLARE @RunningTotal money > DECLARE @PONumber int > > SET @RunningTotal = 0 > set @PONumber = null > > INSERT INTO @AllocationTbl > SELECT RowId, a.PONumber, Amount, null, a.Allocated, null > FROM PODetail d, POAllocation a > where d.PONumber = a.PONumber > ORDER BY a.PONumber, RowId > > UPDATE @AllocationTbl > SET @RunningTotal = RunningTotal = > case > when (@PONumber = PONumber) then > @RunningTotal + Amount > else > Amount > end, > @PONumber = PONumber, > IsAllocated = case > when (@RunningTotal <= Allocated) > then 1 > else 0 > end > FROM @AllocationTbl > > -- SELECT * FROM @AllocationTbl > > update PODetail > set IsAllocated = a.IsAllocated > from PODetail d, @AllocationTbl a > where > d.PONumber = a.PONumber and > d.RowId = a.RowId > > -- select * from POAllocation > select * from PODetail > > -- > -- Result > -- > -- 123456 50.00 1 1 > -- 123456 20.00 1 2 > -- 123456 76.00 0 3 > -- 789012 99.00 1 4 > -- 789012 101.00 1 5 > -- 345678 9.00 1 6 > -- 345678 31.00 1 7 > -- 345678 20.00 1 8 > -- 345678 30.00 1 9 > -- 345678 11.00 1 10 > > Enjoy! :)(But verify that it will work for your case - tested here with MS SQL 2008 R2 Express Edition) > > Credit: Calculating Running Totals in SQL Server 2005, The optimal solution? > http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx > > Thank you. > > -- Shamil > > > Fri, 25 May 2012 13:35:21 +0100 от Paul Hartland <paul.hartland at googlemail.com>: > > To all, > > > > I will try and explain the problem as best as I possibly can, for this > > purpose lets say I have two tables, PODetail which holds detail records for > > a purchase order, and a table containing a value which we have currently > > allocated to a single purchase order number POAllocation > > > > PODetail > > PoNumber Amount Allocated RowID > > 123456 50.00 0 1 > > 123456 20.00 0 2 > > 123456 76.00 0 3 > > > > POAllocation > > PONumber Allocated > > 123456 141.00 > > > > What I need to do is update the Allocated field in PODetail with a value, > > the way I want to do this is get the PO number and allocated amount from > > POAllocation and then loop through the PODetail records updating the > > Allocated field, so I would come to record (rowid) 1 if the allocated > > amount is less than 50.00 then update the allocated field with that value, > > if the allocated amount is great than 50.00 then update the allocated field > > to 50.00, take 50.00 away from 141.00 to get 91.00 then report the process > > for record (rowid) 2. > > > > I am thinking/in middle of writing a cursor inside a cursor, anyone else > > any better ideas on how to do this. > > > > Thanks in advance for any help. > > > > > > -- > > Paul Hartland > > paul.hartland at googlemail.com > > -- > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >