February 6, 2012 at 5:00 am
hi all
i want update and sort this table for total running for any account customers
first table is below::::
idid_H tar Bed Bes Mande
=====================================
191390/11/09524000 0 524000
391390/11/08125000 0 125000
691390/11/080 12000 0
791390/11/1025000 0 0
1091390/11/100 85600000
1591390/11/1085900 0 0
1191390/11/0558000 0 0
i want after update and sort by id,tar this table convert to this form
i use this command my table is Account
DECLARE @mande bigint
set @mande=0
update account
set @mande=mande= @mande+(bed-bes)
where id_h=9;
how to update and sort over table for get this table?
idid_H tar Bed Bes Mande
=======================================
1191390/11/0558000 0 58000
391390/11/08125000 0 183000
691390/11/080 12000 171000
191390/11/09524000 0 695000
791390/11/1025000 0 720000
1091390/11/100 856000 -136000
1591390/11/1085900 0 -50100
please help me!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
i can not read and write english well;
February 6, 2012 at 5:37 am
I've had a go, but my suggestion is that you read Jeff's article on running totals with the quirky update method[/url].
BEGIN TRANSACTION
--Not part of the solution, this is used to create sample data based on what you've posted
CREATE TABLE yourTable (id INT, id_H INT, tar DATETIME, Bed INT, Bes INT, Mande INT)
INSERT INTO yourTable
SELECT id, id_H, tar, Bed, Bes, Mande
FROM (VALUES (1, 9, '1990/11/09', 524000, 0, 524000), --Notice I've changed your 1390 to 1990. This is because I've assumed it's
(3, 9, '1990/11/08', 125000, 0, 125000), --supposed to be a date. If not, you will need to modify this solution
(6, 9, '1990/11/08', 0, 12000, 0),
(7, 9, '1990/11/10', 25000, 0, 0),
(10, 9, '1990/11/10', 0, 856000, 0),
(11, 9, '1990/11/05', 58000, 0, 0),
(15, 9, '1990/11/10', 85900, 0, 0)) a(id, id_H, tar, Bed, Bes, Mande);
SET NOCOUNT ON
--This is where the solution starts
SELECT ISNULL(id,0) AS id, id_H, tar,
Bed, Bes, Mande, Bed AS RunningTotal,
ISNULL(RANK() OVER (ORDER BY tar, id), 0) AS Rnk,
0 AS rn
INTO #Results
FROM yourTable;
UPDATE td
SET rn = a.row
FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY Rnk) AS [row]
FROM #Results) a
INNER JOIN #Results td ON a.id = td.id
ALTER TABLE #Results ADD PRIMARY KEY (rn, id);
DECLARE @Rank INT, @RowCount INT;
SET @Rank = 1;
SET @RowCount = 1;
WHILE @RowCount > 0
BEGIN
SET @Rank = @Rank + 1;
UPDATE nxt
SET RunningTotal = (prv.RunningTotal + nxt.Bed) - nxt.Bes
FROM #Results nxt
INNER JOIN #Results prv ON prv.rn = @Rank - 1
WHERE nxt.rn = @Rank;
SET @RowCount = @@ROWCOUNT;
END;
UPDATE td
SET Mande = r.RunningTotal--, Mande = r.Rnk
FROM yourTable td
INNER JOIN #Results r ON r.id = td.id;
SELECT id, id_H, tar, Bed, Bes, Mande
FROM (SELECT id, id_H, tar, Bed, Bes, Mande,
ROW_NUMBER() OVER (ORDER BY Rnk) AS [row]
FROM (SELECT id, id_H, tar, Bed, Bes, Mande,
RANK() OVER (ORDER BY tar, id) AS Rnk
FROM yourTable) rnk) rn
ORDER BY [row]
ROLLBACK
The above code produces: -
id id_H tar Bed Bes Mande
----------- ----------- ----------------------- ----------- ----------- -----------
11 9 1990-11-05 00:00:00.000 58000 0 58000
3 9 1990-11-08 00:00:00.000 125000 0 183000
6 9 1990-11-08 00:00:00.000 0 12000 171000
1 9 1990-11-09 00:00:00.000 524000 0 695000
7 9 1990-11-10 00:00:00.000 25000 0 720000
10 9 1990-11-10 00:00:00.000 0 856000 -136000
15 9 1990-11-10 00:00:00.000 85900 0 -50100
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply