Computed and Cummulative Columns With SELECT in SQLSERVER

Question

2.00/5 (1 vote)

See more:

Hello,
I have a result of a SELECT clause from 3 Tables that gives me these columns ( I used UNION ALL and ORDER BY date each table has an Amount , and they have in common the Date column) :

(ID,Date,Amount_1,Amount_2,Amount_3)

NOW I need to Select two new Columns (6th and 7th) that will computed like this :
Amount_4 = Amount1-(Amount_2 + Amount3)
And Amount_5 (Of the Current Row) = Amount_5 (of the previous Row) - Amount_4 (Of the Current Row )

So I get finally this :

(ID,Date,Amount_1,Amount_2,Amount_3,Amount_4, Amount_5)

Thanks for Helping me

If you need more explanation please comment : )

Posted 22-Sep-13 5:35am

Member 10267912

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Answer 1 · 2013-09-22T09:13:00

Given a table like this:

CREATE TABLE [cpqa].[tbl_TT_dataSignal](
   [ID][int],
     [Date][datetime],
        [Amount_01][int],
           [Amount_02][int],
              [Amount_03][int]
     )

Add some data. Realze that this solution is "like" whnt you want:

INSERT INTO [cpqa].[tbl_TT_dataSignal]
	VALUES(1,'2013-09-22 10:50:58.400',5141000972,128,256),
			(2,'2013-02-05 09:55:53.857',2105058535,16,32),
			  (3,'2013-04-17 14:05:39.663',1317579732,512,2048),
			     (4,'2013-02-11 10:33:59.810',213575799,6144,7936)

Querying 'SELECT * FROM [cpqa].[tbl_TT_dataSignal]', one obtains:

ID	Date	Amount_01	Amount_02	Amount_03
1	2013-09-22 10:50:58.400	5141000972	128	256
2	2013-02-05 09:55:53.857	2105058535	16	32
3	2013-04-17 14:05:39.663	1317579732	512	2048
4	2013-02-11 10:33:59.810	213575799	6144	7936

(format here in QA not withstanding), now try a calculation:

SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_01]-([Amount_02]+[Amount_03]) AS[Amount_04]/*, [Amount_04] + [Amount_05] AS [Amount_05]*/  FROM [cpqa].[tbl_TT_dataSignal]

That'll give up a table thus:

ID	Date	Amount_01	Amount_02	Amount_03	Amount_04
1	2013-09-22 10:50:58.400	5141000972	128	256	5141000588
2	2013-02-05 09:55:53.857	2105058535	16	32	2105058487
3	2013-04-17 14:05:39.663	1317579732	512	2048	1317577172
4	2013-02-11 10:33:59.810	213575799	6144	7936	213561719

Lengthen stride some by creating another table. Just saying:

CREATE TABLE [cpqa].[tbl_TT_dataSignalTwo](
   [ID][int],
     [Date][datetime],
        [Amount_01][bigint],
           [Amount_02][bigint],
              [Amount_03][bigint],
				[Amount_04][bigint]
				)
INSERT INTO [cpqa].[tbl_TT_dataSignalTwo]
	SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_01]-([Amount_02]+[Amount_03]) AS[Amount_04]  FROM [cpqa].[tbl_TT_dataSignal] 
	
SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_04] FROM  [cpqa].[tbl_TT_dataSignalTwo]

And this query 'SELECT [Amount_04] FROM [cpqa].[tbl_TT_dataSignalTwo]
'
Gets:

Amount_04
~~~~~~~~~~
5141000588
2105058487
1317577172
213561719

Then finally:

SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_04], [Amount_04] + COALESCE((SELECT SUM([Amount_04]) FROM [cpqa].[tbl_TT_dataSignalTwo] b WHERE b.ID<a.ID),0) AS [Amount_05] FROM  [cpqa].[tbl_TT_dataSignalTwo] a

Gets:

ID	Date	Amount_01	Amount_02	Amount_03	Amount_04	Amount_05
1	2013-09-22 10:50:58.400	5141000972	128	256	5141000588	5141000588
2	2013-02-05 09:55:53.857	2105058535	16	32	2105058487	7246059075
3	2013-04-17 14:05:39.663	1317579732	512	2048	1317577172	8563636247
4	2013-02-11 10:33:59.810	213575799	6144	7936	213561719	8777197966

That final output might require an ORDER BY clause if the data appears in retrograde or otherwise not to spec (last column running total)