Share via

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

abiodunajai 371 Reputation points
2023-03-18T00:19:28.1366667+00:00

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

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

Best regards

Abiodunajai

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-03-22T03:06:13.75+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-03-20T09:50:58.83+00:00

    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.