RunningTotal,ForwardBalance and BeginningBal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

RunningTotal,ForwardBalance and BeginningBal

Author

Topic

folumike
Starting Member

24 Posts

Posted - 2013-05-02 : 23:34:46

I have a table of this format. I want to use Stored Procedure in MSSQL 2000 to do it

CustID TransDate TransType Debit Credit
0001 01/01/2013 BegBal 550 0
0002 03/01/2013 BegBal 700 0
0002 03/01/2013 BegBal 0 430
0001 04/01/2013 DR 1550 0
0001 04/01/2013 cR 0 2000

OUTPUT
It should get the RunningTotal,ForwardBalance and BeginningBal for each customer. The forwardBal should be shown as per the selected date.

CustID TransDate TransType Debit Credit RunningBal
0001 01/01/2013 BegBal 550 0 550
0001 04/01/2013 DR 1550 0 2100
0001 04/01/2013 cR 0 2000 100
BegBal:550
ForwardBal:

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-03 : 01:03:57

[code]--May be this?
GO
CREATE PROCEDURE CalculateRunningTotal
(
@CustId VARCHAR(10) = '0001'
,@SelectDate DATE = '04/01/2013'
,@BegBal INT OUT
,@ForwardBal INT OUT
)
AS
BEGIN
IF OBJECT_ID('tempdb..#TempTab') IS NOT NULL
BEGIN
DROP TABLE tempdb..#TempTab;
END;

SELECT CustID, TransDate, TransType, Debit, Credit
,(SELECT SUM(Debit) FROM @Banking WHERE CustID = @CustId AND TransDate <= b.TransDate)-Credit RunningTotal
INTO #TempTab
FROM @Banking b
WHERE CustID = @CustId

set @BegBal = (SELECT TOP 1 RunningTotal FROM #TempTab)
SELECT @ForwardBal = RunningTotal FROM #TempTab WHERE TransDate = @SelectDate

SELECT * FROM #TempTab
END
[/code]
DECLARE @BegBal INT, @ForwardBal INT
EXEC CalculateRunningTotal '0001', '04/01/2013', @BegBal OUT, @ForwardBal OUT
SELECT @BegBal , @ForwardBal

--
Chandu

noblemfd
Starting Member

38 Posts

Posted - 2013-05-04 : 13:52:08

quote:

Originally posted by bandi

--May be this?
GO
CREATE PROCEDURE CalculateRunningTotal
(
	@CustId VARCHAR(10) = '0001'
	,@SelectDate DATE = '04/01/2013'
	,@BegBal INT OUT
	,@ForwardBal INT OUT
)
AS
BEGIN	
	IF OBJECT_ID('tempdb..#TempTab') IS NOT NULL
	BEGIN
		DROP TABLE tempdb..#TempTab;
	END;
	
	SELECT CustID, TransDate, TransType, Debit, Credit
		,(SELECT SUM(Debit) FROM @Banking WHERE CustID = @CustId AND TransDate <= b.TransDate)-Credit RunningTotal
	INTO #TempTab
	FROM @Banking b
	WHERE CustID = @CustId
	
	set @BegBal = (SELECT TOP 1 RunningTotal FROM #TempTab)
	SELECT @ForwardBal = RunningTotal FROM #TempTab WHERE TransDate = @SelectDate

	SELECT * FROM #TempTab
END

DECLARE @BegBal INT, @ForwardBal INT
EXEC CalculateRunningTotal '0001', '04/01/2013', @BegBal OUT, @ForwardBal OUT
SELECT @BegBal , @ForwardBal

--
Chandu

Sorry the question I posted was not complete
1. I have these tables in MSSQL2000:

aslcustomer and aslcustomerregister

CREATE TABLE [dbo].[aslcustomerregister](
[custID] [nvarchar](50) NULL,
[transtype] [nvarchar](500) NULL,
[department] [nvarchar](50) NULL,
[TransDate] [datetime] NULL,
[dr] [money] NULL,
[cr] [money] NULL,
[bal] [money] NULL,
[control1] [bigint] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[aslcustomer](
[custid] [nvarchar](50) NULL,
[name] [nvarchar](100) NULL,
[department] [nvarchar](50) NULL,
[Begbal] [money] NULL,
[TransDate] [datetime] NULL,
[totaldr] [money] NULL,
[totalcr] [money] NULL,
[control1] [bigint] IDENTITY(1,1) NOT NULL,
[Bal] [money] NULL,
) ON [PRIMARY]

GO

2. I want to use STORED PROCEDURE to diplay CustomerID,TransactionDate,Department,RunningTotal,ForwardBalance and BeginningBal for each customer. Everything should be done with respect to CustomerID and department. The beginning balnace should be taken from aslcustomer with respect to CustomerID and department. tHE FORWARD BALANCE should be done from aslcustomerregister.

d1 = enddate
strSQL = "SELECT SUM(CAST(dr as money) - CAST(cr as money)) AS FORWARDBalance
& " From aslcustomerregister" _
& " Where cust2='" & ID_ & "' and CAST(tRANSdate as DATETIME)< '" & d1 & "'"

3. OUTPUT

BegBal:550
ForwardBal:

CustID TransDate Department TransType Debit Credit RunningBal
0001 01/01/2013 Account BegBal 550 0 550
0001 04/01/2013 Account DR 1550 0 2100
0001 04/01/2013 Account CR 0 2000 100
BegBal:550
ForwardBal:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources