sql running total or Balance

Question

I need running VoucherNo concatenation just like running balance or total.. Concatenate the previous VoucherNo to current VoucherNo row wise just like shown in picture

Query is:

select 
v.VoucherDate,v.VoucherNo,v.VoucherType,v.Narration,SUM(v.Debit) Debit , SUM(v.Credit) Credit,dbo.GetBalance(v.CompanyProfileId,v.AccountCode,v.VoucherDate ,SUM(v.Debit), SUM(v.Credit)) Balance
from AcVoucher v
where v.VoucherDate Between '2016-03-24' and '2016-03-30' and v.CompanyProfileId = 2 and v.AccountCode = '05010001'
group by v.VoucherNo,v.VoucherDate,v.VoucherType,v.Narration,v.CompanyProfileId,v.AccountCode

Schema :

    GO
/****** Object:  Table [dbo].[AcVoucher]    Script Date: 03/30/2016 3:47:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AcVoucher](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [CompanyProfileId] [int] NOT NULL,
    [AccountCode] [nvarchar](50) NOT NULL,
    [VoucherNo] [bigint] NOT NULL,
    [VoucherType] [nvarchar](5) NOT NULL,
    [VoucherDate] [datetime] NOT NULL,
    [Narration] [nvarchar](500) NULL,
    [Debit] [float] NOT NULL,
    [Credit] [float] NOT NULL,
    [TaxPercentage] [float] NULL,
    [DiscountPercentage] [float] NULL,
    [CreatedBy] [int] NULL,
    [CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_ACVoucher_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

There is no *previous* voucher number unless you have a column specifying the ordering. And, it is not clear what that column is. — Gordon Linoff, Mar 30 '16 at 11:47
What is wrong with the query you have? Are you getting an error? — Tab Alleman, Mar 30 '16 at 12:31
Possible duplicate of [Calculate running total / running balance](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) — Tab Alleman, Mar 30 '16 at 12:32

score 0 · Accepted Answer · answered Mar 30 '16 at 11:51

You can do this using basically the same logic as for string concatenation in SQL Server. The only difference is the where clause.

One key issue is the ordering for the concatenation. This is not apparent in the question, so I added a minid to the query and this is used (in reverse order) for selecting the ids to bring together:

with v as (
      select v.VoucherDate, v.VoucherNo, v.VoucherType, v.Narration,
             SUM(v.Debit) as Debit , SUM(v.Credit) as Credit, 
             dbo.GetBalance(v.CompanyProfileId, v.AccountCode, v.VoucherDate,
                            SUM(v.Debit), SUM(v.Credit)
                           ) as Balance,
             min(id) as minid
      from AcVoucher v
      where v.VoucherDate Between '2016-03-24' and '2016-03-30' and
            v.CompanyProfileId = 2 and v.AccountCode = '05010001'
      group by v.VoucherNo, v.VoucherDate, v.VoucherType,v.Narration, v.CompanyProfileId, v.AccountCode
     )
select v.*,
       stuff((select ',' + cast(v2.VoucherNo as varchar(8000))
              from v v2
              where v2.minid >= v.minid
              for xml path ('')
             ), 1, 1, '') as RunningConcat
from v;

sql running total or Balance

1 Answers1