with cte as
(
select *,
-- find the latest 'V' ID per ArtNo
max(case when Flag = 'V' then ID end)
over (partition by ArtNo) as Last_V_ID
from myTable
)
select *,
-- cumulative sum, but ignore all rows before the latest 'V' ID
-- includes rows when there's no 'V' ID for this ArtNo
sum(case when ID < Last_V_ID then null else Amount end)
over (partition by ArtNo
order by ID
rows unbounded preceding)
from cte
order by ArtNo, ID
See Fiddle
Edit:
To include the data before the last stocktaking and to ignore all previous stocktakings you can use this approach:
with cte as
(
select *,
-- find the latest 'V' ID per ArtNo
max(case when Flag = 'V' then ID end)
over (partition by ArtNo) as Last_V_ID
from [dbo].[Warehouse]
)
select *,
-- cumulative sum, but ignore all rows before the latest 'V' ID
-- includes rows when there's no 'V' ID for this ArtNo
sum(case when ID < Last_V_ID then null else Amount end)
over (partition by ArtNo
order by ID
rows unbounded preceding)
-- calculate in-stock based on last 'V' ID, discarding all previous 'V' rows
,sum(case when (ID < Last_V_ID and Flag <> 'V') then -Amount
when ID = Last_V_ID then Amount
end)
over (partition by ArtNo
order by ID
rows between 1 following and unbounded following)
from cte
order by ArtNo, ID
Both calculations are mutually exlusive, so you can easily combine them using COALESCE.
See Fiddle
Answer from dnoeth on Stack Overflowsql server - Running total until specific condition is met - Database Administrators Stack Exchange
SQL Conditional select - calculate running total - Stack Overflow
Running total with reset condition – SQLServerCentral Forums
sql server - Conditional running total across columns in SQL - Stack Overflow
There's no efficient solution using plain SQL (including Windowed Aggregate Functons), at least nobody found one, yet :-)
Your recursive query performs bad because it's way too complicated, this is a simplified version:
Edit: Fixed the calculation (Fiddle)
WITH ctePoints AS
(
SELECT 1 AS id
,rank
,CASE
WHEN rank >= 10 THEN 10
WHEN rank = 1 THEN 11
ELSE rank
END AS Point
,1 AS Counter
FROM dbo.BlackJack
WHERE Id = 1
UNION ALL
SELECT t2.Id
,t2.rank
,CASE WHEN t1.Point < 17 THEN t1.Point ELSE 0 END
+ CASE
WHEN t2.rank >= 10 THEN 10
WHEN t2.rank = 1 THEN 11
ELSE t2.rank
END AS Point
,CASE WHEN t1.Point < 17 THEN t1.Counter + 1 ELSE 1 END AS Counter
FROM dbo.BlackJack AS t2
INNER JOIN ctePoints AS t1 ON t2.Id = t1.Id + 1
)
SELECT ctepoints.*
,CASE
WHEN Point < 17 THEN ''
WHEN Point < 20 THEN 'S'
WHEN Point > 21 THEN 'L'
WHEN Point = 21 AND Counter = 2 THEN 'B'
ELSE 'W'
END AS DealerStatus
FROM ctePoints
It's probably still too slow, because it processes row by row.
I usually use recursive SQL to replace cursor logic (because in my DBMS it's usually much faster) but a cursor update might actually be faster (Demo):
CREATE TABLE #BlackJack
(
id INT PRIMARY KEY CLUSTERED
,Rank INT
,DealerStatus CHAR(1)
);
insert into #BlackJack (Id, Rank)
values
(1, 1),(2, 5), (3, 8), (4, 3), (5, 1), (6, 7), (7, 10), (8, 1),(9, 10), (10, 10), (11,1);
DECLARE @Counter INT = 0
,@Point INT = 0
,@id int
,@Rank int
,@DealerStatus char(1)
DECLARE c CURSOR
FOR
SELECT id, Rank
FROM #BlackJack
ORDER BY id FOR UPDATE OF DealerStatus
OPEN c
FETCH NEXT FROM c INTO @id, @Rank
WHILE @@FETCH_STATUS = 0
BEGIN
SET @counter = @counter + 1
SET @Rank = CASE
WHEN @Rank >= 10 THEN 10
WHEN @Rank = 1 THEN 11
ELSE @Rank
END
SET @Point = @Point + @Rank
SET @DealerStatus = CASE
WHEN @Point < 17 THEN ''
WHEN @Point < 20 THEN 'S'
WHEN @Point > 21 THEN 'L'
WHEN @Point = 21 AND @Counter = 2 THEN 'B'
ELSE 'W'
END
IF @Point >= 17
BEGIN
UPDATE #BlackJack
SET DealerStatus = @DealerStatus
WHERE CURRENT OF c;
SET @Point = 0
SET @Counter = 0
END
FETCH NEXT FROM c INTO @id, @Rank
END
CLOSE c
DEALLOCATE c
SELECT * FROM #BlackJack ORDER BY id
Still @lad2025's "quirky update" is the fastest way to get the expected result, but it's using an undocumented feature and if a Service Pack breaks it there's no way to complain about it :-)
This solution is based on quirky update. More info here.
LiveDemo
Data and structures:
CREATE TABLE #BlackJack
(
id INT
,Rank INT
,running_total INT
,result NVARCHAR(100)
);
CREATE CLUSTERED INDEX IX_ROW_NUM ON #BlackJack(id);
insert into #BlackJack (Id, Rank)
values (1, 1),(2, 5), (3, 8), (4, 3), (5, 1),
(6, 7), (7, 10), (8, 1),(9, 10), (10, 10), (11,1);
Main query:
DECLARE @running_total INT = 0
,@number_of_cards INT = 0
,@prev_running_total INT = 0;
UPDATE #BlackJack
SET
@prev_running_total = @running_total
,@running_total = running_total = IIF(@running_total >= 20, 0, @running_total)
+ CHOOSE(Rank,11,2,3,4,5,6,7,8,9,10,10,10,10)
,result = CASE WHEN @running_total = 20 THEN 'S'
WHEN @running_total = 21 AND @number_of_cards = 2 THEN 'B'
WHEN @running_total = 21 THEN 'W'
WHEN @running_total > 21 THEN 'L'
ELSE NULL
END
,@number_of_cards = IIF(@prev_running_total >= 20, 0, @number_of_cards) + 1
FROM #BlackJack WITH(INDEX(IX_ROW_NUM))
OPTION (MAXDOP 1);
SELECT *
FROM #BlackJack
ORDER BY id;
Warning
If you use SQL Server < 2012 you need to replace IIF and CHOOSE with CASE. I don't check all Blackjack rules, only for provided sample. If something is wrong feel free to change CASE logic.
Second I extend base table BlackJack with auxiliary columns, but you can create any new table, if needed.
The key point is to read data sequentially based on clustered key ascending and do not allow parallel execution. Before you use it in production check how it behaves with large data set.
Just wondering if better if you had a date table driving this and join your transaction table to it then you should be able to use Sum Partition By for each previous 12 months and count where Transaction <> 0... what if you join below to the table.
The other guys on this site will probably know for sure if this would work.
with years as (
select * from
(values(2006),(2007),(2008),(2009),(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019)
) as t (Year_id))
,months as (
select * from
(values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
) as t (month_id))
select Year_id,month_id,0 as [Transaction_totals]
from years
cross join months
order by 1,2
Going with the suggestion by Roger Clerkwell, I would first create a Dates table using a With Block (CTE) to then use in the remainder of your query. This would allow you to query the data based on the dates being pulled back from your dates table.
Since I work out of an Oracle Database, my solution shows how to create a dates table that produces a list of month start dates for the date range entered into the Query. It can also produce month END dates, however, I realize for this question the Month End days are not needed. I've written a countless number of reports where I have used this. With some small tweaks, this code can also produce distinct days if you are needing to query results day by day as well as all sorts of other helpful hacks once you fully understand the CONNECT BY LEVEL.
SELECT TRUNC(ADD_MONTHS('01-JUL-18', LEVEL-1), 'MM') START_DATE,
LAST_DAY(ADD_MONTHS('01-JUL-18', LEVEL-1)) END_DATE
FROM DUAL CONNECT BY LEVEL <= CEIL(MONTHS_BETWEEN('30-JUN-19', '01-JUL-18'))
;
The code will produce results that look like the below screenshot.

SQL Server, however, does not need to utilize a dual table. See THIS Stackoverflow question if you have questions about the dual table.