I have below sql query in which i need get running total (Balance row by row).
Kindly check and help me.
DECLARE @ComapnyId BIGINT,@LdegerId BIGINT, @MonthId INT, @fyId INT, @StartDate DATE, @EndDate DATE, @FyYear INT,
@OpeningBalance DECIMAL(18, 2), @ClosingBalance DECIMAL(18, 2), @OpnBalMain DECIMAL(18,2), @OpnBalMainDate DATE, @OpnBalOprtor SMALLINT
SET @LdegerId = 2
SET @MonthId = 11
SET @fyId = 1
SET @ComapnyId=1
SET @FyYear = (SELECT YEAR(EndDate) FROM dbo.mst_FinancialYears WHERE FyId = @FyId)
SET @StartDate = DATEFROMPARTS(@FyYear, @MonthId, 1)
SET @EndDate = EOMONTH(@StartDate)
SELECT @OpnBalMain=OpeningBalance,@OpnBalMainDate=OpeaningBalDate, @OpnBalOprtor=OpeaningBalOprator FROM dbo.mst_Ledgers WHERE LdgId=@LdegerId
----Atttach Opeaning Balance with debit or Credit Side
SELECT
FORMAT(vp.VoucherDate,'dd-MM-yyyy ') As VoucherDate,
LD.LedgerName AS Particulars,
VT.VoucherType AS Vch_Type,
VP.VoucherId AS Vch_No,
ISNULL(CASE WHEN VC.DrCr = 2 THEN VC.Amount ELSE 0 END, 0) AS Debit,
ISNULL(CASE WHEN VC.DrCr = 1 THEN VC.Amount ELSE 0 END, 0) AS Credit,
@OpnBalMain + ISNULL(CASE WHEN VC.DrCr = 2 THEN VC.Amount ELSE 0 END, 0) - ISNULL(CASE WHEN VC.DrCr = 1 THEN VC.Amount ELSE 0 END, 0) As Balance
FROM dbo.tr_VoucherEntryDetailsChild VC
INNER JOIN dbo.tr_VoucherEntryParent VP ON VC.VoucherId = VP.VoucherId
INNER JOIN dbo.mst_Ledgers LD ON VC.LedgerId = LD.LdgId
INNER JOIN dbo.mst_VoucherType VT ON VP.VoucherType = VT.VoucherId
WHERE VC.LedgerId = @LdegerId AND VP.VoucherDate BETWEEN @StartDate AND @EndDate AND VC.CompanyId = @ComapnyId
