T-SQL: Running Total or Replace Text with Running Variable in Groups


Running total in groups:

DROP TABLE IF EXISTS #T1
CREATE TABLE #T1 (ID INT PRIMARY KEY, GroupID INT, TotalValue INT, RunningValue INT);

INSERT #T1 (ID, GroupID, TotalValue) 
SELECT				2, 1, 2 -- Not in Order
UNION ALL SELECT	1, 1, 1
UNION ALL SELECT	3, 1, 3
UNION ALL SELECT	4, 1, 4
UNION ALL SELECT	5, 2, 5
UNION ALL SELECT	7, 2, 7
UNION ALL SELECT	6, 2, 6;

DECLARE @TotalValue INT = 0;
DECLARE @GroupID INT = (SELECT GroupID FROM #T1 WHERE ID = 1);

UPDATE #T1
SET
	RunningValue = @TotalValue
	, @TotalValue = CASE WHEN GroupID = @GroupID THEN @TotalValue + TotalValue ELSE TotalValue END
	, @GroupID = GroupID;

SELECT *
FROM #T1;

Running text replace in groups:

DROP TABLE IF EXISTS #T2
CREATE TABLE #T2 (ID INT IDENTITY(1, 1) PRIMARY KEY, GroupID INT, GroupRank INT, [Value] NVARCHAR(128), Formula NVARCHAR(128), RunningFormula NVARCHAR(128));

INSERT #T2 (GroupID, GroupRank, [Value], Formula) 
SELECT				1, 1, N'One', N'This is {1}, this is {2}, this is {3} and this is {4}'
UNION ALL SELECT	1, 2, N'Two', N'This is {1}, this is {2}, this is {3} and this is {4}'
UNION ALL SELECT	1, 3, N'Three', N'This is {1}, this is {2}, this is {3} and this is {4}'
UNION ALL SELECT	1, 4, N'Four', N'This is {1}, this is {2}, this is {3} and this is {4}'

UNION ALL SELECT	2, 1, N'Monkey', N'We have {3}, {1} and {2}'
UNION ALL SELECT	2, 2, N'Fox',  N'We have {3}, {1} and {2}'
UNION ALL SELECT	2, 3, N'Dog',  N'We have {3}, {1} and {2}';

DECLARE @GroupID INT = (SELECT GroupID FROM #T2 WHERE ID = 1);
DECLARE @Formula NVARCHAR(128) = (SELECT Formula FROM #T2 WHERE ID = 1);

UPDATE #T2
SET
	RunningFormula = REPLACE(@Formula, CONCAT(N'{', GroupRank, N'}'), [Value])
	, @Formula =
		CASE
			WHEN GroupID = @GroupID THEN REPLACE(@Formula, CONCAT(N'{', GroupRank, N'}'), [Value])
			ELSE REPLACE(Formula, CONCAT(N'{', GroupRank, N'}'), [Value])
		END
	, @GroupID = GroupID

SELECT *
FROM #T2;

Get the result:

SELECT *
FROM
	#T2 AS T
	JOIN
	(
		SELECT
			GroupID
			, MAX(GroupRank) AS GroupRank
		FROM #T2
		GROUP BY GroupID
	) AS F
		ON T.GroupID = F.GroupID
		AND T.GroupRank = F.GroupRank;

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *