[AccessD] Is a cursor the way to go

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

-- 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
-- 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
> --