Having total amount at every row level

Question

Post reply

Having total amount at every row level

akmerve

SSC Enthusiast

Points: 194
More actions
March 9, 2010 at 8:14 am

#139276

Hello,
I'd like to have total value of amount paid (PAID) iterated at every row level. For instance, by using the query shown below, I would be able to filter the result and my RANGES_1 table would become like this shown below.
SELECT DISTINCT * FROM RANGES_1 R1
WHERE EXISTS (SELECT 1
FROM RANGES_1 R2
WHERE R1.ID = R2.ID
AND R1.CODE = R2.CODE
AND ABS(R1.DOS - R2.DOS)< 6
AND ABS(R1.DOS - R2.DOS) <> 0
)
ORDER BY ID, DOS
(Thanks Nabha for this).
RANGES_1 table after the query made.
-----------------------------------------------------
Idcodedospaid
1123208.42
11232010.75
11232510.75
11232506.18
11232555
4224433.5
4224453.5
Is there any way that I can use SUM() or any other way that the total field displays the total values at every row level like shown below:
Id code dospaid total
1 123208.42 8.42
1 1232010.75 19.17
1 123 2510.75 29.92
1 123 2506.18 36.10
11232555.0041.10
4 224 43 3.5.00 44.6
4 224 45 3.5.0048.1
Thank you in advance.
Akmerve
TABLE RANGES_1.
CREATE TABLE [dbo].[Ranges_1](
[Id] int NOT NULL,
[varchar](3) NOT NULL,
[dos] int NOT NULL,
[paid] float NOT NULL,
) ON [PRIMARY]
INSERT INTO Ranges_1 VALUES (1, '123', '20', '10.75');
INSERT INTO Ranges_1 VALUES (1, '123', '308', '7.01');
INSERT INTO Ranges_1 VALUES (1, '123', '255', '5.00');
INSERT INTO Ranges_1 VALUES (1, '123', '20', '8.42');
INSERT INTO Ranges_1 VALUES (1, '123', '25', '10.75');
INSERT INTO Ranges_1 VALUES (1, '123', '250', '6.18');
INSERT INTO Ranges_1 VALUES (1, '123', '300', '4.32');
INSERT INTO Ranges_1 VALUES (2, '124', '200', '5.00');
INSERT INTO Ranges_1 VALUES (3, '145', '230', '7.01');
INSERT INTO Ranges_1 VALUES (4, '224', '43', '3.50');
INSERT INTO Ranges_1 VALUES (4, '224', '45', '3.50');
INSERT INTO Ranges_1 VALUES (4, '224', '45', '3.50');

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Looks like you're talking about the RunningTotal problem.

The best resource I found so far is the following article by Jeff Moden:

http://www.sqlservercentral.com/articles/T-SQL/68467/

Please read it carefully especially in terms of the required clustered index as well as the optins added to the "quirky update".

If done correctly it'll run showing an awesome performance.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

try this DECLARE @Ranges_1 TABLE (

[ctr] int identity ,

[Id] int NOT NULL,

[varchar](3) NOT NULL,

[dos] int NOT NULL,

[paid] float NOT NULL

)

INSERT INTO @Ranges_1 VALUES (1, '123', '20', '10.75');

INSERT INTO @Ranges_1 VALUES (1, '123', '308', '7.01');

INSERT INTO @Ranges_1 VALUES (1, '123', '255', '5.00');

INSERT INTO @Ranges_1 VALUES (1, '123', '20', '8.42');

INSERT INTO @Ranges_1 VALUES (1, '123', '25', '10.75');

INSERT INTO @Ranges_1 VALUES (1, '123', '250', '6.18');

INSERT INTO @Ranges_1 VALUES (1, '123', '300', '4.32');

INSERT INTO @Ranges_1 VALUES (2, '124', '200', '5.00');

INSERT INTO @Ranges_1 VALUES (3, '145', '230', '7.01');

INSERT INTO @Ranges_1 VALUES (4, '224', '43', '3.50');

INSERT INTO @Ranges_1 VALUES (4, '224', '45', '3.50');

select * from @Ranges_1

select s.[Id] ,s. ,s.[dos] ,s.[paid],f.[total]

FROM @Ranges_1 s OUTER APPLY

(select sum(t.paid) as [total] from @Ranges_1 t

where t.[ctr] <= s.[ctr] ) f

i needed to add [ctr] column.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Bhuvnesh (3/10/2010)
try this DECLARE @Ranges_1 TABLE ( [ctr] int identity , [Id] int NOT NULL,
[varchar](3) NOT NULL,
[dos] int NOT NULL,
[paid] float NOT NULL
)
INSERT INTO @Ranges_1 VALUES (1, '123', '20', '10.75');
INSERT INTO @Ranges_1 VALUES (1, '123', '308', '7.01');
INSERT INTO @Ranges_1 VALUES (1, '123', '255', '5.00');
INSERT INTO @Ranges_1 VALUES (1, '123', '20', '8.42');
INSERT INTO @Ranges_1 VALUES (1, '123', '25', '10.75');
INSERT INTO @Ranges_1 VALUES (1, '123', '250', '6.18');
INSERT INTO @Ranges_1 VALUES (1, '123', '300', '4.32');
INSERT INTO @Ranges_1 VALUES (2, '124', '200', '5.00');
INSERT INTO @Ranges_1 VALUES (3, '145', '230', '7.01');
INSERT INTO @Ranges_1 VALUES (4, '224', '43', '3.50');
INSERT INTO @Ranges_1 VALUES (4, '224', '45', '3.50');
INSERT INTO @Ranges_1 VALUES (4, '224', '45', '3.50');
select * from @Ranges_1
select s.[Id] ,s. ,s.[dos] ,s.[paid],f.[total]
FROM @Ranges_1 s OUTER APPLY
(select sum(t.paid) as [total] from @Ranges_1 t
where t.[ctr] <= s.[ctr] ) f
i needed to add [ctr] column.

The performance of triangular joins deteriorates rapidly with rowcount, see this excellent article[/url] by Jeff Moden.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Thanks for the article referral, Chris. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

akmerve (3/9/2010)
Hello,
I'd like to have total value of amount paid (PAID) iterated at every row level. For instance, by using the query shown below, I would be able to filter the result and my RANGES_1 table would become like this shown below.
SELECT DISTINCT * FROM RANGES_1 R1
WHERE EXISTS (SELECT 1
FROM RANGES_1 R2
WHERE R1.ID = R2.ID
AND R1.CODE = R2.CODE
AND ABS(R1.DOS - R2.DOS)< 6
AND ABS(R1.DOS - R2.DOS) <> 0
)
ORDER BY ID, DOS
(Thanks Nabha for this).
RANGES_1 table after the query made.
-----------------------------------------------------
Idcodedospaid
1123208.42
11232010.75
11232510.75
11232506.18
11232555
4224433.5
4224453.5
Is there any way that I can use SUM() or any other way that the total field displays the total values at every row level like shown below:
Id code dospaid total
1 123208.42 8.42
1 1232010.75 19.17
1 123 2510.75 29.92
1 123 2506.18 36.10
11232555.0041.10
4 224 43 3.5.00 44.6
4 224 45 3.5.0048.1
Thank you in advance.
Akmerve
TABLE RANGES_1.
CREATE TABLE [dbo].[Ranges_1](
[Id] int NOT NULL,
[varchar](3) NOT NULL,
[dos] int NOT NULL,
[paid] float NOT NULL,
) ON [PRIMARY]
INSERT INTO Ranges_1 VALUES (1, '123', '20', '10.75');
INSERT INTO Ranges_1 VALUES (1, '123', '308', '7.01');
INSERT INTO Ranges_1 VALUES (1, '123', '255', '5.00');
INSERT INTO Ranges_1 VALUES (1, '123', '20', '8.42');
INSERT INTO Ranges_1 VALUES (1, '123', '25', '10.75');
INSERT INTO Ranges_1 VALUES (1, '123', '250', '6.18');
INSERT INTO Ranges_1 VALUES (1, '123', '300', '4.32');
INSERT INTO Ranges_1 VALUES (2, '124', '200', '5.00');
INSERT INTO Ranges_1 VALUES (3, '145', '230', '7.01');
INSERT INTO Ranges_1 VALUES (4, '224', '43', '3.50');
INSERT INTO Ranges_1 VALUES (4, '224', '45', '3.50');
INSERT INTO Ranges_1 VALUES (4, '224', '45', '3.50');

Let me ask this... how often is the underlying data going to be updated?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

akmerve SSC Enthusiast Points: 194 More actions · Answer 6

Thank you all for the suggestions, and I appreciated the feedbacks.

Kind regards,

Akmerve