Running Total With Credit and Debit Column

Question

Hello Experts,

I am looking for such a view with running total having Credit and Debit columns in a table.

But at the same time it should show me in the last column where the credit and debit balance are settled with 0

There are example on internet but i am not getting how can i get the last column where credit and debits are settled and show records after last settled balance only. If not settled ever then it will automatically display all the records.

I know what i want but not getting how...so please guide with few snippets and high performance queries..

Answer 1

Please post table design as DDL, some sample data as DML script and the expected result, see POSTING TIPS - Code, Images, Hyperlinks, Details

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

USE [AppDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetCustomerLedger]    Script Date: 21-02-2020 21-02-2020 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Niraj Zambad>
-- ALTER date: <ALTER Date,,25 July 2017>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetCustomerLedger]
@FromDate Date,
@ToDate Date,
@suppID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


IF(@FromDate IS NOT NULL AND @ToDate IS NOT NULL)
BEGIN
--Customer Opening Balance
SELECT FORMAT ([Date], 'dd-MM-yyyy') AS [Date],[Type],[Perticulars],[Credit],[Debit] FROM
(
  SELECT GETDATE()  AS [Date],
'Opening Balance'  as [Type],'Opening Balance' AS Perticulars,
0 AS Credit,CUST.Balance as [Debit] FROM Customer AS CUST
where CUST.CustomerId=@suppID

UNION

---Sales
(
--SELECT CONVERT(VARCHAR(11), SalesInvoiceDate,106)  AS [Date],
SELECT SalesInvoiceDate  AS [Date],
'Sale'  as [Type],'Inv. No '+CONVERT(varchar,SINDT.SalesInvoiceId) as Perticulars,
0 AS Credit,SINDT.DueAmount as [Debit]
FROM SalesInvoiceDetails AS SINDT
INNER JOIN Customer AS CUST
ON CUST.CustName=SINDT.CustomerName
WHERE SINDT.SalesInvoiceDate>=@FromDate AND 
SINDT.SalesInvoiceDate<=@ToDate AND CUST.CustomerId=@suppID)

UNION 

---Service
(
SELECT SalesInvoiceDate  AS [Date],'Service'  as [Type],'Inv. No '+CONVERT(varchar,SINDT.SalesInvoiceId) as Perticulars,
0 AS [Credit],SINDT.DueAmount as Debit
FROM SalesServiceInvoiceDetails AS SINDT
INNER JOIN Customer AS CUST
ON CUST.CustName=SINDT.CustomerName
WHERE SINDT.SalesInvoiceDate>=@FromDate AND 
SINDT.SalesInvoiceDate<=@ToDate AND CUST.CustomerId=@suppID)

UNION 

---Loan
(
--SELECT CONVERT(VARCHAR(11), CUSTPAY.DateCredited,106)  AS [Date],
SELECT CUSTPAY.DateCredited  AS [Date],
'Loan' as [Type],CUSTPAY.Narration,0,CUSTPAY.DR AS [Debit]
FROM CustomerPayments AS CUSTPAY
WHERE CUSTPAY.DateCredited>=@FromDate AND 
CUSTPAY.DateCredited<=@ToDate AND CUSTPAY.CustomerId=@suppID AND CUSTPAY.DR IS NOT NULL)

UNION 

(
--SELECT CONVERT(VARCHAR(11), CUSTPAY.DateCredited,106)  AS [Date],
SELECT CUSTPAY.DateCredited  AS [Date],
'Receipt' as [Type],CUSTPAY.Narration ,CUSTPAY.CR AS [Credit],0
FROM CustomerPayments AS CUSTPAY
WHERE CUSTPAY.DateCredited>=@FromDate AND 
CUSTPAY.DateCredited<=@ToDate AND CUSTPAY.CustomerId=@suppID AND CUSTPAY.CR IS NOT NULL)

) AS Tbl
order by Year([Date]) DESC,month([Date]) DESC,day([date]) DESC
END
ELSE
BEGIN
--Customer Opening Balance


SELECT FORMAT ([Date], 'dd-MM-yyyy') AS [Date],[Type],[Perticulars],[Credit],[Debit] FROM
(
  SELECT 
--CONVERT(VARCHAR(11), GETDATE(),106)  AS [Date],
--FORMAT (GETDATE(), 'dd-MM-yyyy') AS [Date],
GETDATE() AS [Date],
'Opening Balance'  as [Type],'Opening Balance' AS Perticulars,
0 AS Credit,CUST.Balance as [Debit] FROM Customer AS CUST
where CUST.CustomerId=@suppID

UNION

---Sales
(SELECT 
--CONVERT(VARCHAR(11), SalesInvoiceDate,106)  AS [Date],
SalesInvoiceDate AS [Date],
'Sale'  as [Type],'Inv. No '+CONVERT(varchar,SINDT.SalesInvoiceId) as Perticulars,
0 AS Credit,SINDT.DueAmount as [Debit]
FROM SalesInvoiceDetails AS SINDT
INNER JOIN Customer AS CUST
ON CUST.CustName=SINDT.CustomerName
WHERE CUST.CustomerId=@suppID)

UNION 

---Service
(SELECT 
--CONVERT(VARCHAR(11), SalesInvoiceDate,106)  AS [Date],
SalesInvoiceDate AS [Date],
'Service'  as [Type],'Inv. No '+CONVERT(varchar,SINDT.SalesInvoiceId) as Perticulars,
0 AS [Credit],SINDT.DueAmount as Debit
FROM SalesServiceInvoiceDetails AS SINDT
INNER JOIN Customer AS CUST
ON CUST.CustName=SINDT.CustomerName
WHERE CUST.CustomerId=@suppID)

UNION 

---Loan
(SELECT 
--CONVERT(VARCHAR(11), CUSTPAY.DateCredited,106)  AS [Date],
CUSTPAY.DateCredited AS [Date],
'Loan' as [Type],CUSTPAY.Narration,0,CUSTPAY.DR AS [Debit]
FROM CustomerPayments AS CUSTPAY
WHERE CUSTPAY.CustomerId=@suppID AND CUSTPAY.DR IS NOT NULL)

UNION 

(SELECT 
--CONVERT(VARCHAR(11), CUSTPAY.DateCredited,106)  AS [Date],
CUSTPAY.DateCredited AS [Date],
'Receipt' as [Type],CUSTPAY.Narration ,CUSTPAY.CR AS [Credit],0
FROM CustomerPayments AS CUSTPAY
WHERE CUSTPAY.CustomerId=@suppID AND CUSTPAY.CR IS NOT NULL)
) AS Tbl
order by Year([Date]) DESC,month([Date]) DESC,day([date]) DESC
END

SELECT * FROM Customer WHERE CustomerId=@suppID

END

USE [AppDB]
GO

/****** Object:  Table [dbo].[CustomerPayments]    Script Date: 21-02-2020 21-02-2020 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CustomerPayments](
	[CustomerId] [int] NOT NULL,
	[DateCredited] [date] NOT NULL,
	[AccountType] [varchar](50) NOT NULL,
	[AccountId] [int] NOT NULL,
	[TransactionId] [int] NOT NULL,
	[CR] [decimal](18, 2) NULL,
	[DR] [decimal](18, 2) NULL,
	[Narration] [varchar](200) NULL,
	[PaymentIds] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Answer 3

You were asked to post CREATE TABLE statements for your tables, and you did. But we don't have much use for your code. We need INSERT statements with sample data and the desired result given the sample. This helps us to understand what you are asking for.

We also need to know which version of SQL Server you are using.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

hi

When customer pay that shoud so to credit column or something different logic for credit values

Answer 5

Hello NirajZ,

Actually, we need to know the code of your table and what result do you want. If your data is confidential, you can simulate a similar table  and data, so that we can do something to help you. 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].