How to efficiently get an account balance for every day from Transactions table?

[madpiet]

That's not what I said. You'd write the code in SQL Server to answer the question - follow Noella's code. Then you'd connect to SQL Server and call that T-SQL code from Excel. Excel will basically return the results... then you can do whatever you want... graph it or whatever.

[Minty]

That's not what I said. You'd write the code in SQL Server to answer the question - follow Noella's code. Then you'd connect to SQL Server and call that T-SQL code from Excel. Excel will basically return the results... then you can do whatever you want... graph it or whatever.

Sorry - I was replying to JoJo, not your suggestion which is perfectly valid.
once the hard work is done in SQL server anything that can connect and read it, is laughing.

[jojowhite]

the OP can also connect and retrieve the raw data and do the processing on the
disconnected records on excel which i think is easy, either using table or PQ.

on second thought, if T-SQL query results has few records, excel can accommodate them.
on the long run, if the result is more than million, excel will crash (so my path to Access
is not in vain after all).

[NoellaG]

If the result is more than a million records to be pulled over the network, not only the DBA will make plans to lynch the OP.

[jojowhite]

after series of views (not sure if it is efficient), i manage to make it work.

[Thomasso]

Hello guys, as said before, I was away for 3 weeks, now finally back.

Thank you for all the suggestions. You are right - I have MS SQL as the back end on my local Dell server and I want it to do all the "heavy work". MS Access is the front end with only linked tables/views. I do not use Excel for this at all.

Jojo - would you be so kind and send me the SQL query that made it work? Thanks!

Tomas

[madpiet]

Hello guys, as said before, I was away for 3 weeks, now finally back.

Thank you for all the suggestions. You are right - I have MS SQL as the back end on my local Dell server and I want it to do all the "heavy work".
Tomas

Tomas,

This is ugly, but appears to work. I'm sure Noella will tear it up and fix it. =) This is everything from the beginning so people can pick apart where I may have gone wrong.

use tempdb;
go


DROP TABLE IF EXISTS Calendar;
DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS Transactions;




CREATE TABLE Calendar(TheDate DATE PRIMARY KEY);


/* this doesn't make sense... it's missing the OpenDate */
CREATE TABLE Accounts (
	AccountID INT PRIMARY KEY,
	StartingBalance DECIMAL(8,2) NOT NULL
	);




CREATE TABLE Transactions (
	TransactionID INT IDENTITY,
	TransactionDate DATE NOT NULL,
	AccountID INT NOT NULL,
	Amount DECIMAL(8,2)
	);
GO
INSERT INTO Accounts(AccountID, StartingBalance)
VALUES (14,376.94),(15,-18629.34), (22, 200.00);




DECLARE @TheDate DATE = '2025-01-01';
WHILE @TheDate < '2025-01-12'
BEGIN
	INSERT INTO Calendar(TheDate)
	VALUES (@TheDate);
	SET @TheDate = DATEADD(day,1,@TheDate);
END;




INSERT INTO Transactions(TransactionDate, AccountID, Amount)
VALUES 
('2025-01-02', 15, -1000.00),
('2025-01-02', 15, -50.00),
('2025-01-02', 15, -169.00),
('2025-01-04', 15, -700.00),
('2025-01-06', 22, -30.00),
('2025-01-07', 22, -80.00),
('2025-01-07', 15, -15800.00),
('2025-01-07', 15, -427.41),
('2025-01-08', 14, 61477.00),
('2025-01-08', 14, -36775.75);


/* get the daily totals */
/* now add the running total */
SELECT z.AccountID, z.TheDate, z.StartingBalance, z.TransactionID, z.AccountID, z.Amount AS TransactionAmount, z.rt AS RunningBalance
FROM
(
	SELECT 
			daily.AccountID,
			daily.TheDate,
			daily.StartingBalance,
			daily.TransactionID,
			daily.Amount,
			rt = daily.StartingBalance + SUM(daily.Amount) OVER (PARTITION BY daily.AccountID
									ORDER BY daily.TheDate
									ROWS BETWEEN UNBOUNDED PRECEDING
										AND CURRENT ROW)
	FROM
	(
		/* Now add transactions daily */
		SELECT xj.TheDate, xj.AccountID, xj.StartingBalance, t.TransactionID, t.Amount
		FROM /* cross join forces there to exist a date for every account */
			(SELECT a.AccountID, c.TheDate, a.StartingBalance
			FROM Accounts a
			CROSS JOIN Calendar c) xj
		LEFT JOIN Transactions t
		ON xj.AccountID = t.AccountID AND xj.TheDate = t.TransactionDate
	) daily
) z

Oh hey look! Past Me was really nice and left some helpful comments in there... "cross join forces there to exist a date for every account". Basically, a cross join does relational multiplication - it returns every combination of the values in Accounts and the values in Calendar. So then all you have to do is a running total on top of that.

In order to follow the logic, start at the bottom and read one query at a time and move upward.

[jojowhite]

if the server is Local, you can easily do the same in Ms Access.
SQL server will do the hardwork on retrieving your data and
once loaded MSA is also fast on executing your running balance query.

All the while they are guessing that you will be using Excel for
your Charting.


for your MS SQL server, aside from your Accounts and Transactions table,
i use several Views and 3 new small tables (for date generation purpose)
to accomplished this (please see attached excel workbook).
as you can see on the worksheet, i made "small" views, so i can easily manage it.

[NoellaG]

If you need to work with calendar tables, check out the following tip:

https://www.mssqltips.com/sqlservert...in-sql-server/

For reports/charting: if you work with SQL server, why not use the reporting services (SSRS) that come freely with it and publish the reports online? You can view the reports in a browser and easily download the results to Excel, pdf, XML, ect .

For the running total: here is an extremely simple example how to do it using sum(value) over
https://blog.sqlauthority.com/2014/1...in-sql-server/

For the starting balance, I would create a table where you write the balance on a yearly (quarterly, monthly?) base, so you can avoid doing calculations over the whole dataset.

[Thomasso]

If you need to work with calendar tables, check out the following tip:

https://www.mssqltips.com/sqlservert...in-sql-server/

For reports/charting: if you work with SQL server, why not use the reporting services (SSRS) that come freely with it and publish the reports online? You can view the reports in a browser and easily download the results to Excel, pdf, XML, ect .

For the running total: here is an extremely simple example how to do it using sum(value) over
https://blog.sqlauthority.com/2014/1...in-sql-server/

For the starting balance, I would create a table where you write the balance on a yearly (quarterly, monthly?) base, so you can avoid doing calculations over the whole dataset.

I already have an excellent calendar table which I found here:
https://stackoverflow.com/questions/...0-years-in-sql

CREATE SCHEMA Auxiliary
-- We put our auxiliary tables and stuff in a separate schema
-- One of the great new things in SQL Server 2005
go

CREATE FUNCTION Auxiliary.Computus
-- Computus (Latin for computation) is the calculation of the date of
-- Easter in the Christian calendar
-- http://en.wikipedia.org/wiki/Computus
-- I'm using the Meeus/Jones/Butcher Gregorian algorithm
(
    @Y INT -- The year we are calculating easter sunday for
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@a INT,
@b INT,
@c INT,
@d INT,
@e INT,
@f INT,
@g INT,
@h INT,
@i INT,
@k INT,
@L INT,
@m INT

SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b - @f + 1) / 3
SET @h = (19 * @a + @b - @d - @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END
GO


CREATE TABLE [Auxiliary].[Calendar] (
-- This is the calendar table
  [Date] datetime NOT NULL,
  [Year] int NOT NULL,
  [Quarter] int NOT NULL,
  [Month] int NOT NULL,
  [Week] int NOT NULL,
  [Day] int NOT NULL,
  [DayOfYear] int NOT NULL,
  [Weekday] int NOT NULL,
  [Fiscal_Year] int NOT NULL,
  [Fiscal_Quarter] int NOT NULL,
  [Fiscal_Month] int NOT NULL,
  [KindOfDay] varchar(10) NOT NULL,
  [Description] varchar(50) NULL,
  PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE [Auxiliary].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))
-- http://www.celko.com/

ADD CONSTRAINT [Calendar_ck] CHECK (  ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week]  BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO




SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- http://en.wikipedia.org/wiki/ISO_8601
-- thus first day of a week is monday
WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL                            -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),

DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),

Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

-- Fiscal year may be different to the actual year in Norway the are the same
-- http://en.wikipedia.org/wiki/Fiscal_year
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- http://en.wikipedia.org/wiki/List_of_holidays_by_country
    WHEN (DATEPART(DayOfYear, d.Date) = 1)          -- New Year's Day
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  -- Palm Sunday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  -- Maundy Thursday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  -- Good Friday
    OR (d.Date = Auxiliary.Computus(YEAR(Date)))    -- Easter Sunday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- Ascension Day
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- Pentecost
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- Whitmonday
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      -- Labour day
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     -- Constitution day
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    -- Cristmas day
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    -- Boxing day
    THEN 'HOLIDAY'
    WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
    WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
    ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
    WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN 'New Year''s Day'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN 'Palm Sunday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN 'Maundy Thursday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN 'Good Friday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN 'Easter Sunday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN 'Ascension Day'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN 'Pentecost'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN 'Whitmonday'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN 'Labour day'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN 'Constitution day'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN 'Cristmas day'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN 'Boxing day'
END Description

FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
     inner join Weeks w
     on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO

CREATE FUNCTION Auxiliary.Numbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS @RetNumbers TABLE
(
[Number] int PRIMARY KEY NOT NULL
)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END
GO

CREATE FUNCTION Auxiliary.iNumbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS TABLE
AS
RETURN(
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
SELECT n AS Number from Numbers
)
GO

Huh, I never realized that SQL Server has reporting services. However, I eventually want to dig into Power Bi for advanced reporting. In Access, I just want some very basic stuff like totals and basic stats.

That being said, I should definitely look into SSRS, as it's great to display in a browser. Thank you for the tip!

Tomas

[madpiet]

A recursive CTE for that? Yikes. You could cross join a couple of system tables and slap a ROW_NUMBER() on top of that and get some absurd number of records. Then you just add the ROW_NUMBER() value to your StartDate.

[Thomasso]

A recursive CTE for that? Yikes. You could cross join a couple of system tables and slap a ROW_NUMBER() on top of that and get some absurd number of records. Then you just add the ROW_NUMBER() value to your StartDate.

Well I entered 30 years worth of dates and it took less than a second, so I am quite satisfied with it