March 9, 2010 at 10:22 am
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.
-----------------------------------------------------
Id code dos paid
1 123 20 8.42
1 123 20 10.75
1 123 25 10.75
1 123 250 6.18
1 123 255 5
4 224 43 3.5
4 224 45 3.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 dos paid total
1 123 20 8.42 8.42
1 123 20 10.75 19.17
1 123 25 10.75 29.92
1 123 250 6.18 36.10
1 123 255 5.00 41.10
4 224 43 3. 5.00 44.6
4 224 45 3. 5.00 48.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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply