SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 RunningTotal,ForwardBalance and BeginningBal
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

folumike
Starting Member

24 Posts

Posted - 05/02/2013 :  23:34:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 05/03/2013 :  01:03:57  Show Profile  Reply with Quote
--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
Go to Top of Page

noblemfd
Starting Member

Nigeria
38 Posts

Posted - 05/04/2013 :  13:52:08  Show Profile  Reply with Quote
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:
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000