I have Following Table
Input Output
---------------------------------
12.22
2.22
If I pass Following Sql Statement:
Select Input,Output,Balance=sum(Input – Output) from Stock group by input,output
So The Output is:
Input Output Balance
--------------------------------------
12.22 NULL NULL
NULL 2.22 NULL
If I wants The Output Like Below:
Input Output Balance
--------------------------------------
12.22 12.22
2.22 10.00
Than What is SQL Transact Statement?
My Table Structure is as Below:
companyID int
transID int -- Primary Key (Auto Increment)
Date datetime
particulars varchar
input decimal
output decimal
Note:- I have applied here Group By function on input and Output columns which doesn’t make diference as there is transID columns which is autoincrement hence it should be display all the rows of the table.
Advertisement
Answer
I think this may work for you. First, here is how I defined my test table and test data:
declare @stock table (
transId int not null identity(1,1),
[date] date not null, -- use datetime if not using SQL Server 2008
input decimal(7,2) null,
[output] decimal(7,2) null
);
insert into @stock values
('1/23/2011', 12.22, null),
('1/23/2011', null, 2.22),
('1/24/2011', 16, null),
('1/24/2011', 3.76, null),
('1/24/2011', null, 5.50);
And here is the select I used to produce the results you specified in your question. The query produces a total for all transactions in transaction ID order for a given day. If you want to calculate the total for multiple days, then comment-out the AND b.[date] = a.[date]
line.
select
[date],
input = isnull(input,0),
[output] = isnull([output],0),
balance = (isnull(input,0)-isnull([output],0)) +
isnull((
select
sum((isnull(input,0)-isnull([output],0)))
from @stock b
where b.transId < a.transId
and b.[date] = a.[date] -- comment this for totals over multiple dates
),0)
from @stock a
order by transId;
This query gives:
date input output balance
2011-01-23 12.22 0.00 12.22
2011-01-23 0.00 2.22 10.00
2011-01-24 16.00 0.00 16.00
2011-01-24 3.76 0.00 19.76
2011-01-24 0.00 5.50 14.26
As a footnote, since the running total value will always be the same for each transaction, I would recommend adding a balance column to your table and calculating the value of the balance column upon insertion of the transaction row. In that way, you would only have to look at the balance of the last transaction to determine what the balance for the transaction being inserted should be.