Computing in SQL Queries

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi!

I want to compute in where the logic is somehow like this:

Table A has a column named "TotalProductSum"

ID||TotalProductSum||
11||1000.00        ||

Table B has a column named "ProductSum"

ID||Products||Value||
11||01      ||20   ||
11||02      ||35   ||
11||03      ||40   ||
11||04      ||10   ||

*ProductSum has .00 in each cell and this should be computed as TotalProductSum - ProductSum. The TotalProductSum changes once it is subtracted to product sum. For example TotalProductSum = 100 and ProductSum = 10 then the NewTotalProductSum = 90. The next value of ProductSum = 30 then the computation will change to NewTotalProductSum - ProductSum then the NewTotalProductSum of the next cell is 60.

OUTPUT TABLE:
TotalProductSum = 1000.00

ID||Products||Value||NewTotalProductSum [Value - [Previous]TotalProductSum]
11||01      ||20   ||800.00
11||02      ||35   ||765.00
11||03      ||40   ||725.00
11||04      ||10   ||715.00

TotalProductSum is the difference of Value and the Previous TotalProductSum.

If you do not get the question, feel free to comment below. Thank you.

Posted 30-Jun-14 23:18pm

mitchiee1226

Updated 1-Jul-14 14:35pm

v3

Add a Solution

Comments

Magic Wonder 1-Jul-14 5:39am

did not get the question? Kindly clarify what is your exact requirement and what you have done for that?

mitchiee1226 1-Jul-14 20:30pm

Updated the question.

[no name] 1-Jul-14 6:48am

What question is it that you are referring to?

mitchiee1226 1-Jul-14 20:31pm

The output. I want to output this type of table. Is it possible?

[no name] 1-Jul-14 20:57pm

Well okay then if that is really your question.... yes it is possible.

damodara naidu betha 1-Jul-14 8:13am

Didn't get your question. please clarify.

mitchiee1226 1-Jul-14 20:31pm

Updated the question.

Bernhard Hiller 1-Jul-14 8:44am

How does TableA look like?
How does TableB look like?
How is the table you show us above related to TabelA and TableB?

mitchiee1226 1-Jul-14 20:32pm

The table above is a sample of the output I want. Updated the question and added TableA and TableB.

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)

syed shanu · Accepted Answer · 2014-07-01T16:35:00

I have made sample for you check this hope this will help you.

SQL

-- Create Table 
CREATE TABLE [dbo].[TableA](
	[ID] [INT] NOT NULL,
	[TotalProductSum] [INT] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TableB](
	[ID] [INT] NOT NULL,
	[Products] [varchar](30) NOT NULL,
	[Value] [INT] NOT NULL
) ON [PRIMARY]


-- Insert Sample Data
INSERT INTO [dbo].[TableA]
           ([ID],[TotalProductSum])
     VALUES (11,1000)
           
           INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'10' ,80)

    INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'20' ,30)
      INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'30' ,45)
      INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'40' ,56)

-- Select query 

select * from TableA
select * from TableB

-- Here isw the select query with your result : 


SELECT A.ID,A.Products,A.Value,B.TotalproductSum- SUM(a.value) OVER (ORDER BY A.Products) NewTotalProductSum 
from  
TableB as A INNER JOIN TableA AS B
ON A.ID=B.ID
ORDER BY a.id;

The Result will be like this
Table A
11 1000
TableB
11 10 80
11 20 30
11 30 45
11 40 56

The result Query will be like this (

VB

NewTotalProductSum [Value - [Previous]TotalProductSum]

)
11 10 80 920
11 20 30 890
11 30 45 845
11 40 56 789