locked
Running Total With Credit and Debit Column RRS feed

  • 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..


    • Edited by NirajZ Friday, February 21, 2020 9:45 AM
    Friday, February 21, 2020 9:44 AM

All replies

  • 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]

    Friday, February 21, 2020 10:02 AM
  • 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
    
    
    

    Friday, February 21, 2020 10:11 AM
  • 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, esquel@sommarskog.se

    Friday, February 21, 2020 10:40 PM
  • hi

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

    Saturday, February 22, 2020 6:12 AM
  • 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 MSDNFSF@microsoft.com.

    Monday, February 24, 2020 2:08 AM