How Do I use CTE to Generate Running Totals From More than Two Tables

Question

How Do I use CTE to Generate Running Totals From More than Two Tables

abiodunajai 396

Please I need your assistance on my Query below to Generate Running Totals with 'Brought Forward Balance' depending on the date period of Captured. The base Date is '01/01/2021'. Any date period captured beyond that must show a 'B/F' on the First Row before adding other subsequent Rows.

My Table Structures are below


CREATE TABLE [dbo].InvoiceTable(
	[DebitNoteNo] [char](12) NOT NULL,
	[ClientCode] [char](10) NOT NULL,
	[TransDate] [datetime] NOT NULL,
	[PremiumAmount] [decimal](19, 4) NULL,
	[Particular] [varchar](2048) NULL,
	[RecordID] [int] IDENTITY(1,1) NOT NULL
	)
GO


INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000890', '0000000065', '2021-01-01', 'ADDITIONAL', -2418309.8628 );

VALUES ('DN0000000890', '0000000065', '2021-01-01', 'RENEWAL', -1450985.9177 );

VALUES ('DN0000000890', '0000000065', '2021-01-01', 'FIRST PREMIUM', -2418309.8628 );

VALUES ('DN0000000908', '0000000065', '2022-02-07', 'RENEWAL', -1860238.3560 );

VALUES ('DN0000000908', '0000000065', '2022-02-07', 'ADDITIONAL', -1818309.6623 );

VALUES ('DN0000000908', '0000000065', '2022-02-07', 'RENEWAL', -4218109.8634 );

VALUES ('DN0000000963', '0000000065', '2023-01-05', 'RENEWAL', -8418709.3428 );

VALUES ('DN0000000963', '0000000065', '2023-01-05', 'RENEWAL', -9412309.8723);

VALUES ('DN0000001192', '0000000065', '2023-01-23', 'FIRST PREMIUM', -1218309.5607 );

VALUES ('DN0000000989', '0000000065', '2023-02-19', 'FIRST PREMIUM', -2218609.9028 );

VALUES ('DN0000000989', '0000000065', '2023-02-19', 'FIRST PREMIUM', -6718309.2222 );

VALUES ('DN0000001192', '0000000065', '2023-03-06', 'FIRST PREMIUM', -8510309.7812 );

VALUES ('DN0000001748', '0000000065', '2023-03-10', 'FIRST PREMIUM', -7715609.2672 );


CREATE TABLE [dbo].ReceiptTable(
	[ReceiptCode] [char](12) NOT NULL,
	[ClientCode] [char](10) NOT NULL,
	[TransDate] [datetime] NOT NULL,
	[PremiumAmount] [decimal](19, 4) NULL,
	[Particular] [varchar](2048) NULL
	)

INSERT INTO ReceiptTable ( [ReceiptCode],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('RC0000000687', '0000000065', '2021-01-01', '**PMT**ADDITIONAL', 6287605.643 );

VALUES ('RC0000000688', '0000000065', '2022-02-07', '**PMT**RENEWAL', 7896657.882);

VALUES ('RC0000000689', '0000000065', '2023-01-05', '**PMT**RENEWAL', 17831019.22);

VALUES ('RC0000000689', '0000000065', '2023-03-06', '**PMT**FIRST PREMIUM', 8510309.7812);

The Query scripts I have tried which I believe are cumbersome and could be greatly improved upon is pasted below.


DECLARE @EndDate DATE = '2021-01-01' -- Cut off Date for Opening balances
DECLARE @BDate DATE = '2021 -01-01' -- Transaction begin Date specify by the user
DECLARE @EDate DATE = '2023-03-16' -- Transaction End Date Specifiy by the user
DECLARE @ClientCode CHAR(10)
DECLARE @DigitalCode CHAR(3)
SET @ClientCode ='0000000065'   -- Client Code Specify by the user
SET @DigitalCode='566'

DROP TABLE IF EXISTS #Statement
;With PastBal As (

			   SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount FROM InvoiceTable WHERE TransDate >=@EndDate  AND TransDate < @BDate  AND ClientCode=@ClientCode
	UNION ALL SELECT  ReceiptCode, TransDate, ClientCode,  Particular, -Amount As Amount  FROM ReceiptTable WHERE TransDate >=@EndDate  AND TransDate < @BDate AND ClientCode=@ClientCode
    
),
OpenBalance As(
    SELECT DebitNoteNo, TransDate, ClientCode, '*BALANCE BROUGHT FORWARD* B/F' As Particular Amount  FROM PastBal
	GROUP BY DebitNoteNo, TransDate, ClientCode, Particular, Amount	
),
Stm As (
	   SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount FROM OpenBalance
	 UNION ALL SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount FROM InvoiceTable WHERE TransDate >=@BDate  AND TransDate < @EDate  AND ClientCode=@ClientCode
	UNION ALL SELECT  ReceiptCode As DebitNoteNo, TransDate, ClientCode, Particular, Amount FROM ReceiptTable WHERE TransDate >=@BDate  AND TransDate <= @EDate AND ClientCode=@ClientCode	
   )

SELECT IDENTITY(int, 1, 1) AS ROW_ID, ClientCode, COALESCE(DebitNoteNo, '') As DebitNoteNo, COALESCE(TransDate, GETDATE()) AS TransDate, COALESCE(Particular, '**BALANCE**') AS Particular, COALESCE(Amount,0) As Amount
INTO #Statement
FROM stm
ORDER BY TransDate, DebitNoteNo


SELECT  ROW_ID, ClientCode, COALESCE(DebitNoteNo, '') As DebitNoteNo, COALESCE(TransDate, GETDATE()) AS TransDate, COALESCE(a.Particular, '**BALANCE**') AS Particular, COALESCE(-Amount,0) As Amount, SUM(-Amount) OVER(ORDER BY Row_Id) AS ClientBalance 
FROM #Statement a
GROUP BY GROUPING SETS((ROW_ID, DebitNoteNo, ClientCode, TransDate, Particular, Amount), ())

Output is shown below.

runningTotal

Any effort to reduce the Query length and still achieve the output result that will

show 'B/f' balance when the date is greater that 01/01/2021
show the last row as 'Balance' with the running total last balance will e greatly appreciated.

Best regards

Abiodunajai

Erland Sommarskog 120.2K Reputation points MVP

2023-03-19T20:52:58.89+00:00
I have two pieces of advice when it comes to asking a question like this:

It's great that you include scripts, but you should also test them. They have lots of errors, and I gave up after a while when trying to fix the errors.

Be considerable about the tags you include. You had added five tags: SQL Server, SQL Server Reporting Services, SQL Server Integration Services, SQL Server Analysis Services and Azure SQL Database. But the only tag that is really applicable is SQL Server, and I and another moderator have removed the superfluous tags.
PercyTang-MSFT 12,511 Reputation points Microsoft External Staff

2023-03-20T06:32:38.3366667+00:00

Hi @abiodunajai

I tried fixing the error in the script and running the script, but the running result is a bit different from what you show.

There are a lot of words about finance in your question, which confuses me. I can't understand the script well.

Can you provide more explanation or remove the irrelevant columns and leave the core of the matter? This will make the question clearer.

abiodunajai 396

Thank you so much, Erland, and PercyTang for your assistance.

A million apologies for not testing the script. Again, thank you for removing the unnecessary tags included. Please find below the tested script for your use.



CREATE TABLE [dbo].InvoiceTable(
	[DebitNoteNo] [char](12) NOT NULL,
	[ClientCode] [char](10) NOT NULL,
	[TransDate] [datetime] NOT NULL,
	[Amount] [decimal](19, 4) NULL,
	[Particular] [varchar](2048) NULL,
	[RecordID] [int] IDENTITY(1,1) NOT NULL
	)
GO


INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000890', '0000000065', '2021-01-01', 'ADDITIONAL', -2418309.8628 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000890', '0000000065', '2021-01-01', 'RENEWAL', -1450985.9177 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000890', '0000000065', '2021-01-01', 'FIRST PREMIUM', -2418309.8628 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000908', '0000000065', '2022-02-07', 'RENEWAL', -1860238.3560 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000908', '0000000065', '2022-02-07', 'ADDITIONAL', -1818309.6623 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000908', '0000000065', '2022-02-07', 'RENEWAL', -4218109.8634 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000963', '0000000065', '2023-01-05', 'RENEWAL', -8418709.3428 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000963', '0000000065', '2023-01-05', 'RENEWAL', -9412309.8723);
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000001192', '0000000065', '2023-01-23', 'FIRST PREMIUM', -1218309.5607 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000989', '0000000065', '2023-02-19', 'FIRST PREMIUM', -2218609.9028 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000000989', '0000000065', '2023-02-19', 'FIRST PREMIUM', -6718309.2222 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000001192', '0000000065', '2023-03-06', 'FIRST PREMIUM', -8510309.7812 );
INSERT INTO InvoiceTable ( [DebitNoteNo],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('DN0000001748', '0000000065', '2023-03-10', 'FIRST PREMIUM', -7715609.2672 );


CREATE TABLE [dbo].ReceiptTable(
	[ReceiptCode] [char](12) NOT NULL,
	[ClientCode] [char](10) NOT NULL,
	[TransDate] [datetime] NOT NULL,
	[Amount] [decimal](19, 4) NULL,
	[Particular] [varchar](2048) NULL
	)

INSERT INTO ReceiptTable ( [ReceiptCode],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('RC0000000687', '0000000065', '2021-01-01', '**PMT**ADDITIONAL', 6287605.643 );
INSERT INTO ReceiptTable ( [ReceiptCode],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('RC0000000688', '0000000065', '2022-02-07', '**PMT**RENEWAL', 7896657.882);
INSERT INTO ReceiptTable ( [ReceiptCode],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('RC0000000689', '0000000065', '2023-01-05', '**PMT**RENEWAL', 17831019.22);
INSERT INTO ReceiptTable ( [ReceiptCode],  [ClientCode], [TransDate], [Particular], [Amount])
VALUES ('RC0000000689', '0000000065', '2023-03-06', '**PMT**FIRST PREMIUM', 8510309.7812);

SELECT * FROM InvoiceTable



DECLARE @EndDate DATE = '2021-01-01' -- Cut off Date for Opening balances
DECLARE @BDate DATE = '2021 -01-01' -- Transaction begin Date specify by the user
DECLARE @EDate DATE = '2023-03-16' -- Transaction End Date Specifiy by the user
DECLARE @ClientCode CHAR(10)
DECLARE @DigitalCode CHAR(3)
SET @ClientCode ='0000000065'   -- Client Code Specify by the user
SET @DigitalCode='566'

DROP TABLE IF EXISTS #Statement
;With PastBal As (

			   SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount FROM InvoiceTable WHERE TransDate >=@EndDate  AND TransDate < @BDate  AND ClientCode=@ClientCode
	UNION ALL SELECT  ReceiptCode, TransDate, ClientCode,  Particular, -Amount As Amount  FROM ReceiptTable WHERE TransDate >=@EndDate  AND TransDate < @BDate AND ClientCode=@ClientCode
    
),
OpenBalance As(
    SELECT DebitNoteNo, TransDate, ClientCode, '*BALANCE BROUGHT FORWARD* B/F' As Particular, Amount  FROM PastBal
	GROUP BY DebitNoteNo, TransDate, ClientCode, Particular, Amount	
),
Stm As (
	   SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount FROM OpenBalance
	 UNION ALL SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount FROM InvoiceTable WHERE TransDate >=@BDate  AND TransDate < @EDate  AND ClientCode=@ClientCode
	UNION ALL SELECT  ReceiptCode As DebitNoteNo, TransDate, ClientCode, Particular, Amount FROM ReceiptTable WHERE TransDate >=@BDate  AND TransDate <= @EDate AND ClientCode=@ClientCode	
   )

SELECT IDENTITY(int, 1, 1) AS ROW_ID, ClientCode, COALESCE(DebitNoteNo, '') As DebitNoteNo, COALESCE(TransDate, GETDATE()) AS TransDate, COALESCE(Particular, '**BALANCE**') AS Particular, COALESCE(Amount,0) As Amount
INTO #Statement
FROM  stm
ORDER BY TransDate, DebitNoteNo

--This section generates the running TOTal ClientBalance
SELECT  ROW_ID, ClientCode, COALESCE(DebitNoteNo, '') As DebitNoteNo, COALESCE(TransDate, GETDATE()) AS TransDate, COALESCE(a.Particular, '**BALANCE**') AS Particular, COALESCE(Amount,0) As Amount, SUM(Amount) OVER(ORDER BY Row_Id) AS ClientBalance 
FROM #Statement a
GROUP BY GROUPING SETS((ROW_ID, DebitNoteNo, ClientCode, TransDate, Particular, Amount), ())

My challenges are:

I want to include the 'Brought Forward Balance' as the 1st row, whenever any user specifies the capturing period greater than the base date of '01/01/2021' i.e( If the TransDate is >'01/01/2021') else, the 'B/F balance' should be Zero (0). For example, if a user specifies the transaction Date period '01/01/2023' to '28/02/2023' then the system should calculate backward the transactions between '01/01/2021' and '31/12/2022' and group everything together as 'Brought forward Balance'. That was what the CTE PastBal and OpenBalance in the script above handled. I believe it could be handled in a better way. Kindly modify it in the best way possible. CTE Stm pulls the OpeningBalance data and then generates the user Specified period between '01/012023' To '28/02/2023' Then I tried to include the Row_Id column to the FIELDS in the next query above and INSERT the result into a #Statement, from where I GROUP BY GROUPING SETS to the running Total ClientBalance.
I try including the '*Balance' to show in the last row but unfortunately it is not showing. Please what is the best way to implement this? I hope all my explanations are clear enough.

Thanking you in anticipation of your assistance

Best regards

Abiodunajai

Accepted answer

1 additional answer

Your answer

Erland Sommarskog 120.2K Reputation points MVP

2023-03-19T20:52:58.89+00:00

I have two pieces of advice when it comes to asking a question like this:

It's great that you include scripts, but you should also test them. They have lots of errors, and I gave up after a while when trying to fix the errors.

Be considerable about the tags you include. You had added five tags: SQL Server, SQL Server Reporting Services, SQL Server Integration Services, SQL Server Analysis Services and Azure SQL Database. But the only tag that is really applicable is SQL Server, and I and another moderator have removed the superfluous tags.
PercyTang-MSFT 12,511 Reputation points Microsoft External Staff

2023-03-20T06:32:38.3366667+00:00

Hi @abiodunajai

I tried fixing the error in the script and running the script, but the running result is a bit different from what you show.

There are a lot of words about finance in your question, which confuses me. I can't understand the script well.

Can you provide more explanation or remove the irrelevant columns and leave the core of the matter? This will make the question clearer.

Answer 1

Hi @abiodunajai

I've made some changes to your script for your new needs. You can give it a try.

DECLARE @EndDate DATE = '2021-01-01' -- Cut off Date for Opening balances
DECLARE @BDate DATE = '2021-01-01' -- Transaction begin Date specify by the user
DECLARE @EDate DATE = '2023-03-16' -- Transaction End Date Specifiy by the user
DECLARE @ClientCode CHAR(10)
DECLARE @DigitalCode CHAR(3)
SET @ClientCode ='0000000065'   -- Client Code Specify by the user
SET @DigitalCode='566'
DROP TABLE IF EXISTS #Statement
DROP TABLE IF EXISTS #temp
create table #temp(a int);
insert into #temp values(1);
;with PastBal As (
  SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount 
  FROM InvoiceTable WHERE TransDate >=@EndDate  AND TransDate < @BDate  AND ClientCode=@ClientCode
  UNION ALL 
  SELECT  ReceiptCode, TransDate, ClientCode, Particular, -Amount As Amount  
  FROM ReceiptTable WHERE TransDate >=@EndDate  AND TransDate < @BDate AND ClientCode=@ClientCode
),OpenBalance As(
    SELECT ClientCode,Sum(Amount) As Amount FROM PastBal GROUP BY ClientCode
),Stm As(
   select 'N/A' As DebitNoteNo,null as TransDate,'N/A' as ClientCode,'B/F' as Particular,
   '0' as Amount from #temp where @EndDate = @BDate
   union all
   SELECT 'N/A' As DebitNoteNo, DATEADD(day,-1, @BDate) As TransDate, ClientCode, 
   '**BROUGHT FORWARD**' As Particular, Amount FROM OpenBalance
   UNION ALL
   SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount FROM InvoiceTable WHERE TransDate >=@BDate  AND TransDate < @EDate  AND ClientCode=@ClientCode
   UNION ALL  
   SELECT  ReceiptCode As DebitNoteNo, TransDate, ClientCode, Particular, Amount FROM ReceiptTable WHERE TransDate >=@BDate  AND TransDate <= @EDate AND ClientCode=@ClientCode	
)
SELECT IDENTITY(int, 1, 1) AS ROW_ID, ClientCode, DebitNoteNo,  TransDate,  Particular, Amount
INTO #Statement
FROM stm
ORDER BY TransDate, DebitNoteNo
;with CTE as(
  SELECT ROW_ID, ClientCode, COALESCE(DebitNoteNo, '') As DebitNoteNo, 
         COALESCE(TransDate, GETDATE()) AS TransDate, 
         COALESCE(a.Particular, '**BALANCE**') AS Particular, COALESCE(Amount,0) As Amount
  FROM #Statement a 
  GROUP BY GROUPING SETS((ROW_ID, DebitNoteNo, ClientCode, TransDate, Particular, Amount), ()))
select *,SUM(Amount) OVER(ORDER BY CASE WHEN ROW_ID IS  NULL THEN  1 ELSE  0 END, ROW_ID) As ClientBalance 
from CTE ORDER BY CASE WHEN ROW_ID IS  NULL THEN  1 ELSE  0 END, ROW_ID;

Output:

User's image

Best regards,

Percy Tang

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

abiodunajai 396 Reputation points

2023-03-22T11:27:04.7133333+00:00

Thanks a million, PercyTang for your immense assistance to resolve this challenge.

Everything works fine now.

You are most appreciated.

Best regards,

Abiodunajai

Answer 2

PercyTang-MSFT 12,511 Microsoft External Staff

Hi @abiodunajai

For optimizations for CTE PastBal and OpenBalance, you can use unions, because unions also have the ability to merge the same items.

;with PastBal As (
  SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount 
  FROM InvoiceTable WHERE TransDate >=@EndDate  AND TransDate < @BDate  AND ClientCode=@ClientCode
  UNION  
  SELECT  ReceiptCode, TransDate, ClientCode, Particular, -Amount As Amount  
  FROM ReceiptTable WHERE TransDate >=@EndDate  AND TransDate < @BDate AND ClientCode=@ClientCode
),OpenBalance As(
  SELECT DebitNoteNo, TransDate, ClientCode, '*BALANCE BROUGHT FORWARD* B/F' As Particular, Amount FROM PastBal)

Regarding the last line showing '*Balance', I guess if it's caused by sorting issues. Add this sorting code at the end of the script.

order by case when ROW_ID is null then 1 else 0 end,ROW_ID

Output:

User's image

I'm not sure if this is what you want, if there are still questions, please leave a message.

Best regards,

Percy Tang

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

abiodunajai 396

Thank you so much PercyTang,

The script is working. However, please

a) I want the query to show Zero (0) as 'Brough Forward Balance' when there is no value to display.

b) How do I show the ClientBalance value to the **Balance row As marked red in the picture below.

Modify Query is below

Statement2

DECLARE @EndDate DATE = '2021-01-01' -- Cut off Date for Opening balances
DECLARE @BDate DATE = '2021-01-01' -- Transaction begin Date specify by the user
DECLARE @EDate DATE = '2023-03-16' -- Transaction End Date Specifiy by the user
DECLARE @ClientCode CHAR(10)
DECLARE @DigitalCode CHAR(3)
SET @ClientCode ='0000000065'   -- Client Code Specify by the user
SET @DigitalCode='566'

DROP TABLE IF EXISTS #Statement
;with PastBal As (
  SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount 
  FROM InvoiceTable WHERE TransDate >=@EndDate  AND TransDate < @BDate  AND ClientCode=@ClientCode
  UNION ALL 
  SELECT  ReceiptCode, TransDate, ClientCode, Particular, -Amount As Amount  
  FROM ReceiptTable WHERE TransDate >=@EndDate  AND TransDate < @BDate AND ClientCode=@ClientCode
),OpenBalance As(
    SELECT  ClientCode, Sum(Amount) As Amount  FROM PastBal
	GROUP BY  ClientCode
  ),
Stm As (
   SELECT 'N/A' As DebitNoteNo, DATEADD(day,-1, @BDate) As TransDate, ClientCode, '**BROUGHT FORWARD**' As Particular, Amount FROM OpenBalance
   UNION ALL
   SELECT  DebitNoteNo, TransDate, ClientCode, Particular, Amount As Amount FROM InvoiceTable WHERE TransDate >=@BDate  AND TransDate < @EDate  AND ClientCode=@ClientCode
   UNION ALL  
   SELECT  ReceiptCode As DebitNoteNo, TransDate, ClientCode, Particular, Amount FROM ReceiptTable WHERE TransDate >=@BDate  AND TransDate <= @EDate AND ClientCode=@ClientCode	
)

SELECT IDENTITY(int, 1, 1) AS ROW_ID, ClientCode, DebitNoteNo,  TransDate,  Particular, Amount
INTO #Statement
FROM  stm
ORDER BY TransDate, DebitNoteNo

SELECT * FROM #Statement
--This section generates the running TOTal ClientBalance
SELECT  ROW_ID, ClientCode, COALESCE(DebitNoteNo, '') As DebitNoteNo, COALESCE(TransDate, GETDATE()) AS TransDate, COALESCE(a.Particular, '**BALANCE**') AS Particular, COALESCE(Amount,0) As Amount, SUM(Amount) OVER(ORDER BY Row_Id) As ClientBalance
FROM #Statement a
GROUP BY GROUPING SETS((ROW_ID, DebitNoteNo, ClientCode, TransDate, Particular, Amount), ())
ORDER BY  CASE WHEN ROW_ID IS  NULL THEN  1 ELSE  0 END, ROW_ID

Once again, thank you so much for your assistance so far.

Best regards,

Abioduajai

PercyTang-MSFT 12,511 Reputation points Microsoft External Staff

2023-03-21T08:40:38.8333333+00:00

Hi @abiodunajai

What do you need the box marked in red to show, the total of the ClientBalance column or something else?

Question A confused me. Where do you want to replace the '0' with 'Brough Forward Balance', the '0' at the bottom of the Amount column or the '0' in the ClientBalance column or something else?

Best regards,

Percy Tang
abiodunajai 396 Reputation points

2023-03-21T09:39:29.5966667+00:00

Thank you so much PercyTang.

a) I need the Total of the ClientBalance as shown in the sketch below.

b) When the Date starts from 01/01/2021 (the base date), the first row should be as shown below. The last row - ****Balance *** should show the Client balance.

I hope the sketch above clearly defines what my requests are.

Thank you so much for your kind assistance so far.

Best regards,

Abiodunajai

Share via

How Do I use CTE to Generate Running Totals From More than Two Tables

1 additional answer

Your answer