Here is a short continuation of my previous post on a “budget app” database structure. Based on my desired features from the last post, I’ve got some views for displaying net worth and one for displaying the categories with the budgets.

/*Ths is simply the current net worth. Not so useful*/
CREATE OR ALTER VIEW dbo.NetWorthDisplay
AS
SELECT		IIF( GROUPING( AD.AccountTypeGroup ) = 1, 'Net Worth', AD.AccountTypeGroup ) AS AccountTypeGroup,
            SUM( AD.CurrentBalance ) AS CurrentBalance
FROM		DBO.AccountsDisplay AS AD
GROUP BY	ROLLUP ( AD.AccountTypeGroup )
GO

/*This one is much more useful, showing the net worth as it changes by month.
I need to create the Months table first.*/
CREATE TABLE dbo.Months (
				MonthStartDate	DATE NOT NULL,
				MonthEndDate	DATE NOT NULL,
				DaysInMonth		INT NOT NULL,
				CONSTRAINT PK_Months PRIMARY KEY CLUSTERED ( MonthStartDate )
				)

INSERT INTO dbo.Months( MonthStartDate, MonthEndDate, DaysInMonth )
SELECT		M.MonthStartDate,
			EOMONTH( M.MonthStartDate ) AS MonthEndDate,
			DATEDIFF( DAY, M.MonthStartDate, EOMONTH( M.MonthStartDate ) ) + 1 AS DaysInMonth
FROM		(
			/*I decided to start from Jan 1st 2021 as that is when my personal Bluecoin records start*/
			SELECT		DATEADD( MONTH, ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ), '2021-01-01' ) AS MonthStartDate
			FROM		SYS.objects AS O
			) AS M
GO

CREATE OR ALTER VIEW dbo.NetWorthDisplayByMonth
AS
SELECT		IIF( GROUPING( TY.AccountTypeGroup ) = 1, 'Net Worth', TY.AccountTypeGroup ) AS AccountTypeGroup,
			M.MonthStartDate,
			ISNULL( SUM( A.StartingBalance + ISNULL( T.TotalAmount, 0 ) ), 0 ) AS CurrentBalance
FROM		(
			SELECT		MonthStartDate
			FROM		dbo.Months
			WHERE		DATEDIFF( MONTH, MonthStartDate, GETDATE() ) BETWEEN 0 AND 12
			) AS M
				CROSS JOIN	dbo.AccountTypes AS TY
				LEFT JOIN	(
							SELECT		*
							FROM		dbo.Accounts
							WHERE		DeletedFlag = 0
							) AS A
					ON			A.AccountTypeID = TY.AccountTypeID
					AND			A.OpeningDate <= M.MonthStartDate
				OUTER APPLY	(
							SELECT	SUM( Amount ) AS TotalAmount
							FROM	dbo.Transactions
							WHERE	DeletedFlag = 0
							AND		AccountID = A.AccountID
							AND		TransactionDate <= M.MonthStartDate
							) AS T
GROUP BY	ROLLUP ( TY.AccountTypeGroup ),
			M.MonthStartDate
GO

I wanted to be able to handle budgets like the app, which means you can select a time window (to start with I’ll handle Weekly, Fortnightly, Monthly, Quarterly, and Annually) and an amount. Then, the view to display the budget per category will calculate how to scale the budget amount based on the timewindow.

/*Create our budgets table first*/
DROP TABLE IF EXISTS dbo.Budgets

CREATE TABLE dbo.Budgets (
				BudgetID	INT IDENTITY(1,1) NOT NULL,
				TimeWindowType	VARCHAR(20) NOT NULL,
				BudgetAmount	DECIMAL(10,2) NOT NULL,
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_Budgets PRIMARY KEY CLUSTERED ( BudgetID )
				)

/*Then we need to alter the Categories table to link to a budget*/
DROP TABLE IF EXISTS dbo.Categories

CREATE TABLE dbo.Categories (
				CategoryID		INT IDENTITY(1,1) NOT NULL,
				CategoryName	VARCHAR(200) NOT NULL,
				CategoryGroup	VARCHAR(200) NOT NULL,
				BudgetID		INT NULL, /*In the app, you can set a budget at a category level or a category group level. Not sure yet how I'd handle it*/
				LastModifiedDatetimeUTC	DATETIME2(0) NOT NULL,
				DeletedFlag				BIT NOT NULL,
				CONSTRAINT PK_CategoryID PRIMARY KEY CLUSTERED ( CategoryID )
				)

/*Now let's create a generic table valued function to show all categories and compare the spending with the budget between any two dates*/
CREATE OR ALTER FUNCTION dbo.CategoriesAndBudgetDisplay (
	@ComparisonStartDate DATE,
	@ComparisonEndDate DATE
	)
RETURNS TABLE
AS
RETURN (
	SELECT		C.CategoryName,
				C.CategoryGroup,
				T.Currency,
				T.TotalTransactionAmount,
				B.BudgetID,
				B.TimeWindowType,
				B.BudgetAmount
	FROM		dbo.Categories AS C
					LEFT JOIN	(
								SELECT		Currency,
											SUM( Amount ) AS TotalTransactionAmount,
											CategoryID
								FROM		dbo.Transactions
								WHERE		DeletedFlag = 0
								AND			TransactionDate BETWEEN @ComparisonStartDate AND @ComparisonEndDate
								GROUP BY	Currency,
											CategoryID
								) AS T
						ON			T.CategoryID = C.CategoryID
				LEFT JOIN	(
							SELECT		BudgetID,
										TimeWindowType,
										/*For now, I will keep it simply an assume 365 days a year*/
										CASE TimeWindowType	WHEN 'Annually'		THEN -BudgetAmount * ( 1 + DATEDIFF( DAY, @ComparisonStartDate, @ComparisonEndDate ) ) / 365
															WHEN 'Quarterly'	THEN -BudgetAmount * ( 1 + DATEDIFF( DAY, @ComparisonStartDate, @ComparisonEndDate ) ) / 365 * 4
															WHEN 'Monthly'		THEN -BudgetAmount * ( 1 + DATEDIFF( DAY, @ComparisonStartDate, @ComparisonEndDate ) ) / 365 * 12
															WHEN 'Fortnightly'	THEN -BudgetAmount * ( 1 + DATEDIFF( DAY, @ComparisonStartDate, @ComparisonEndDate ) ) / 365 * 26
															WHEN 'Weekly'		THEN -BudgetAmount * ( 1 + DATEDIFF( DAY, @ComparisonStartDate, @ComparisonEndDate ) ) / 365 * 52
																				ELSE NULL
										END AS BudgetAmount
							FROM		dbo.Budgets
							WHERE		DeletedFlag = 0
							) AS B
					ON			B.BudgetID = C.BudgetID
	WHERE		C.DeletedFlag = 0
	AND			C.CategoryGroup NOT IN ( '(New Account)', '(Transfer)' )
)
GO

I’ve also built one stored procedure that’s for updating, deleting, or creating new accounts. I only have basic error handling - capture it to an error table and re-throw. If there was an application on top of this, it would need to do something with that error.

/*Create a simple error table to save any errors for now. A real app would handle it better*/
DROP TABLE IF EXISTS dbo.Errors

CREATE TABLE dbo.Errors (
				ErrorMessage		NVARCHAR(4000) NOT NULL,
				ErrorLine			INT NULL,
				ErrorNumber			INT NOT NULL,
				ErrorProcedure		NVARCHAR(128) NULL,
				ErrorSeverity		INT NOT NULL,
				ErrorState			INT NOT NULL,
				ErrorDatetimeUTC	DATETIME2(0) NOT NULL,
				INDEX IDX_Errors_DT CLUSTERED ( ErrorDatetimeUTC )
				)
GO

/*I am using default values for most of the parameters since if you are deleting an account, you only need the account ID*/
CREATE OR ALTER PROC dbo.CreateUpdateDeleteAccount
	@AccountID INT,
	@AccountName VARCHAR(200) = '',
	@AccountTypeID INT = 1,
	@OpeningDate DATE = '2021-01-01',
	@StartingBalance DECIMAL(10,2) = 0,
	@DefaultCurrency VARCHAR(3) = 'AUD',
	@Notes NVARCHAR(2000) = '',
	@Delete BIT = 0
AS
BEGIN

	SET XACT_ABORT ON
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRY

	/*At this early stage, I'm not sure yet how I would handle errors, so I will save them in an error table*/
	IF @Delete = 1
		UPDATE	A
		SET		A.DeletedFlag = 1,
				A.LastModifiedDatetimeUTC = GETUTCDATE()
		FROM	dbo.Accounts AS A
		WHERE	A.AccountID = @AccountID

	ELSE
	BEGIN
	/*I wanted a robust way to handle upserts - update the row if it exists, insert a new row if it doesn't. I've used a solution from
	https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/ for this.
	I'm going to assume that inserts are more common, but apparently this pattern does quite well for both*/
	BEGIN TRAN

		UPDATE	A
		SET		A.AccountName = @AccountName,
				A.AccountTypeID = @AccountTypeID,
				A.OpeningDate = @OpeningDate,
				A.StartingBalance = @StartingBalance,
				A.DefaultCurrency = @DefaultCurrency,
				A.Notes = @Notes,
				A.LastModifiedDatetimeUTC = GETUTCDATE()
		FROM	dbo.Accounts AS A
		WHERE	A.AccountID = @AccountID

		/*So, basically, try to update an existing record, and if that affects 0 rows, insert a new one*/
		INSERT dbo.Accounts( AccountName, AccountTypeID, OpeningDate, StartingBalance, DefaultCurrency, Notes, LastModifiedDatetimeUTC, DeletedFlag )
		SELECT	@AccountName,
				@AccountTypeID,
				@OpeningDate,
				@StartingBalance,
				@DefaultCurrency,
				@Notes,
				GETUTCDATE(),
				0 AS DeletedFlag
		WHERE	@@ROWCOUNT = 0

	COMMIT TRAN
	END

	END TRY
	BEGIN CATCH

		INSERT INTO dbo.Errors( ErrorMessage, ErrorLine, ErrorNumber, ErrorProcedure, ErrorSeverity, ErrorState, ErrorDatetimeUTC )
		VALUES( ERROR_MESSAGE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE(), GETUTCDATE() )

		;THROW

	END CATCH

END

Bluecoin features I’m missing from my database:

  • Budgets
    • By Category
    • By Category group
  • Display net worth
  • Project net worth
  • Display running balance of accounts
  • Display current balance of account
  • Handling non-default currencies in the running totals, budgets, etc
  • Stored procedures for:
    • Creating, deleting, or updating accounts
    • Creating, deleting, or updating categories
    • Creating, deleting, or updating transactions
    • Creating, deleting, or updating budgets
  • Implementing reminders