Problem in calculation

Question

0.00/5 (No votes)

See more:

HI all !!!

I have some values in table and i need to calculate the sum as follows

MonthlyContri          ||      Arrears          ||      total      ||     MonthlyTotal
       
        1000           ||          0            ||    1000                  1000 
        1000           ||          0            ||    1000         ||       2000 
        1000           ||         2000          ||    3000         ||       5000
        1000           ||         1000          ||    2000         ||       7000

MonthlyContri +Arrears = total

Now Monthly total = Last MonthlyTotal(in the previous row) +TotalNow

for ex here Monthly total in second row = 1000 +1000 =2000
                             third row =  2000 + 3000= 5000

[edit]Code block added - OriginalGriff[/edit]

Posted 28-May-12 0:17am

ujjwal uniyal

Updated 28-May-12 0:48am

OriginalGriff

v2

Add a Solution

Comments

Maciej Los 28-May-12 6:53am

The problem was defined rather vaguely... On the first look, the question is incomplete. How we can help you, if your table does not contain field(column) with date? In my opinion you should use PIVOT table, like this:

NameOfSomething || 1 || 2 || 3 
Fruits || 1000 || 2000 || 5000 || 7000 
...

2 solutions

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)

Maciej Los · Answer 1 · 2012-05-28T01:50:00

Take a look at below example:

SQL

IF NOT OBJECT_ID(N'#Sale',N'U') IS NULL 
	DROP TABLE #Sale

CREATE TABLE #Sale (ProductName VARCHAR(30), CountOfPosition INT, TotalPrice FLOAT, SaleDate DATETIME)

INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 1005, 5000.12, '2012-01-14')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 1030, 5312.88, '2012-01-22')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 998, 4987.65, '2012-01-31')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 1012, 5100.12, '2012-02-01')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 1099, 5719.78, '2012-02-16')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 888, 4567.32, '2012-02-28')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 1005, 5000.12, '2012-03-05')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 1030, 5312.88, '2012-03-12')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
	VALUES('Fruits', 898, 4987.65, '2012-03-29')


DECLARE @cols VARCHAR(1000)
DECLARE @dt VARCHAR (2000)
DECLARE @pt VARCHAR(4000)

SET @cols = '[1],[2],[3]'

SET @dt = 'SELECT ProductName, TotalPrice, MONTH(SaleDate) AS SaleMonth ' +
		'FROM #Sale ' +
		'WHERE YEAR(SaleDate) = 2012 ' 
--EXEC(@dt)

SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
		'FROM (' + @dt + ') AS DT ' +
		'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT ' +
		'ORDER BY PT.ProductName'
EXEC (@pt)

DROP TABLE #Sale

Result:

ProductName	1	2	        3
Fruits	    15300,65	15387,22	15300,65

As you see, calculations are provided for each product (in this case only fruits) and for each month of year 2012.

To get total sum for each product, we need to change pivot query, removing ORDER BY clause:

SQL

SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
		'FROM (' + @dt + ') AS DT ' +
		'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT ' -- +
		--'ORDER BY PT.ProductName'
EXEC (@pt)

--declare variables
DECLARE @fq VARCHAR(4000)
DECLARE @cols2 VARCHAR (200)

--set variables to get total sum for months: 1-3
SET @cols2 = '[1] + [2] + [3]'
SET @fq = 'SELECT ProductName, ' + @cols + ', ' + @cols2 + ' AS TotalSum ' +
		'FROM (' + @pt + ') AS FQ'
EXEC (@fq)

More at:
Using PIVOT and UNPIVOT[^]

Search this site to find more examples.

vangapally Naveen Kumar · Answer 2 · 2012-05-28T00:37:00

Try Like this

VB

 Dim con As SqlConnection=New Sqlconnection("Your Connection string")
    Dim cmd As SqlCommand 

Dim ds As DataSet
    Dim da As SqlDataAdapter
con.open()
da = New SqlDataAdapter("select * from sample order by mtotal desc", con)
        ds = New DataSet()
        da.Fill(ds)
dim lasttotal as string =ds.tables(0).rows(0)("lasttotal").tostring()
dim total as integer=txtmcontr+arrears
dim monthlytotal as integer=convert.toint32(lasttotal)+total
cmd=New SqlCommand("insert into sample (moncontr,arrears,total,mtotal) values('"+txtmcontr+"','"+arrears
+"','"+total+"','"+monthlytotal+"')")
cmd.executenonquary()

con.close()

Problem in calculation

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0