/*
Hi All.

Can any body give me an advice/solution how to solved this condition?
Table1 
id amout
1 10000
2 5000

Table2
id acc____amount status
1 123456 5000 
1 145678 3000
1 455789 2000
2 963258 6000
2 258741 6000
result:
--------
Table2
id acc___amount___Status
1 123456 5000_______S ->(table1 (10000) - this record 5000)
1 145678 3000_______S ->(table1 (10000 - previous rec 5000 - this record 3000)
1 455789 3000_______F ->(table1 (10000 - previous rec 5000 - previous record 3000 - this record 3000) Minus
2 963258 6000_______F ->Minus
2 258741 6000_______F ->Minus

Please some help me to solved this problem because i already stuck

Tq
*/

DECLARE @table1 TABLE (
	[Id] INT
	, [amount] MONEY
	)

DECLARE @table2 TABLE (
	[Id] INT
	, [acc] INT
	, [amount] MONEY
	)

INSERT @table1 ([Id], [amount])
      SELECT 1, 10000
UNION SELECT 2, 5000

INSERT @table2 ([Id], [acc], [amount])
      SELECT 1, 123456, 5000
UNION SELECT 1, 145678, 3000
UNION SELECT 1, 455789, 3000
UNION SELECT 2, 963258, 6000
UNION SELECT 2, 258741, 6000

;WITH ranks (
	[Id]
	, [account]
	, [amount]
	, [position]
	)
AS (
	SELECT
		[ID]
		, [acc]
		, [amount]
		, ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [acc] ASC)
	FROM
		@table2
	)


, runTot (
	[Id]
	, [position]
	, [acc]
	, [amount]
	, [runTot]
	, [status]
	)
AS (
	SELECT
		r.[ID]
		, r.[position]
		, r.[account]
		, r.[amount]
		, t1.[amount] - r.[amount]
		, CASE WHEN  t1.[amount] - r.[amount] >= 0 THEN 'S' ELSE 'F' END
	FROM
		ranks r
		JOIN @table1 t1 ON t1.[ID] = r.[ID]
	WHERE
		r.[position] = 1
	UNION ALL SELECT
		r.[ID]
		, t.[position]
		, t.[account]
		, t.[amount]
		, r.[runTot] - t.[amount]
		, CASE WHEN  r.[runTot] - t.[amount] >= 0 THEN 'S' ELSE 'F' END
	FROM
		ranks t
		JOIN runTot r ON
			r.[ID] = t.[ID]
			AND r.[position] = t.[position] - 1
	)
SELECT
	[Id]
	, [acc]
	, [amount]
	, [status]
FROM
	runTot
ORDER BY
	[ID]
	, [acc]

DECLARE @table1 TABLE (
	[Id] INT
	, [amount] MONEY
	)

DECLARE @table2 TABLE (
	[Id] INT
	, [acc] INT
	, [amount] MONEY,
	total money
	)

INSERT @table1 ([Id], [amount])
      SELECT 1, 10000
UNION SELECT 2, 5000

INSERT @table2 ([Id], [acc], [amount])
      SELECT 1, 123456, 5000
UNION SELECT 1, 145678, 3000
UNION SELECT 1, 455789, 3000
UNION SELECT 2, 963258, 6000
UNION SELECT 2, 258741, 6000

declare @total money, @id int

update @table2
set total=@total,
@total=case when @id=id then coalesce(@total,amount)+amount else amount   end,@id=id

select 
	t2.id,t2.acc,t2.amount,
	case when total<=t1.amount then 'S' else 'F' end as [status] 
from 
	@table1 as t1
		inner join @table2 as t2 on t1.id=t2.id

quote:

---

Originally posted by Transact Charlie

Quirky update = THE BLACK ARTS! (which I really should try and learn some day but.... man they are just wrong!)

Slightly off topic but,

is there any way in a quirky update to control the order. You cant use an order by for update statement or derived table....?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---