Starting Total with minimum balance after the module
Currently the code below works with a total of 50 as the limit.
Now I would like to have 50 as the minimum for the rest. How could I do it. Any hints would be really helpful. (minimum balance 50). This code is in sql 2012, need it to work sql2008 and above
Example:
- 10 10
- 20 30
- 30 60
- 40 100 --> 50 (min value of 50) 100-50 = 50
- 2 52
- 3 55
- 10 65
- 25 90
- 15 105 --> 55 (min value 50, 105-50 = 55)
- 5 60
DECLARE @Table TABLE (Id INT, ListItem INT);
INSERT INTO @Table VALUES (1, 10);
INSERT INTO @Table VALUES (2, 20);
INSERT INTO @Table VALUES (3, 30);
INSERT INTO @Table VALUES (4, 40);
INSERT INTO @Table VALUES (5, 2);
INSERT INTO @Table VALUES (6, 3);
INSERT INTO @Table VALUES (7, 10);
INSERT INTO @Table VALUES (8, 25);
INSERT INTO @Table VALUES (9, 15);
INSERT INTO @Table VALUES (10, 5);
WITH RunningTotal AS (
SELECT Id, ListItem, SUM(ListItem) OVER (ORDER BY Id) % 50 AS RT FROM @Table)
SELECT
rt.Id,
rt.ListItem,
CASE WHEN rt.RT < rt2.RT THEN rt.RT + 50 ELSE rt.RT END AS RunningTotal
FROM
RunningTotal rt
LEFT JOIN RunningTotal rt2 ON rt2.Id = rt.Id - 1
ORDER BY
rt.Id;
Edit:
I understand now. But when I try to change the values, it only works once. For example, once the cumulative value exceeds 350,000, subtract by 300,000, which means that the minimum balance is 50,000.
Any hints?
Link: http://sqlfiddle.com/#!6/c9051/1/0
Thank.
+3
source to share
1 answer
The key is that when you only want to go modulo 50, when your number is over 100. Then you will get a "remainder" above 50 which you add 50 to get the desired result
WITH RunningTotal AS (
SELECT Id, ListItem, SUM(ListItem) OVER (ORDER BY Id) AS RT FROM @Table)
SELECT Id, ListItem, RT, CASE WHEN RT > 100 THEN RT % 50 + 50 ELSE RT END AS NewRT
FROM RunningTotal
+1
source to share