How to calculate the time an Item was in stock as SQL query

Ok since my first attempt was not to understand, I'll try again.

I'm trying to write a query with SQL that at the end gives me the total days that a physical item has been in stock, i.e. greater than 0.

The table has 3 fields
kArticle dBooked tAvailable

I want to calculate the times in which tAvailable > 0 has been.

my problem is that whenever a sale has taken place an entry is created and therefore there are many entries where tAvailable > 0.

I am only interested in the time from the goods receipt to the time when it was no longer available.

so i have to try to get rid of the useless data or make it clear to the query that the table has to be processed in chronological order and only the oldest values are interesting no matter how many values follow that are also greater than 0.

This table should serve as an example.
in the simplest case the data would look like this

what I have to do now is quite simple, I have to calculate :

when tAvailable = 0 then (dBooked) - ( pref dBooked)

and to format this into Date so the Table would / should look like this:

but what do i do when the table looks like this:

My idea was to use a helper field since iam only interested in the 01.01.2020 date and the date 20.02.2020

if tAvailable >0 and pref. tAvailable !>0 
then helper = 1 
Else
""*no entry*

Plus

if tAvailable <= 0 and pref tAvailable !<=0
then helper = 0
ELSE
""*no entry*

That would / should create this table

i just need to dont display helper = "" to get this

and now i can just do the same thing again

when Helper = 0 then (dBooked) - ( pref dBooked)

first of all i cant realy do any of that stuff above! i just tell Chat GPT what i want and he does it for me but since i have no idea what to do in SQL iam trying what i know that would be EXCEL

thats why i work with so many if

At the end i have a few problems with my aproach:

if the first row is 0, I get an error.
If the last row is not 0, I need to create one for the date the query was executed.

i need to do the math in correct order etc etc

what ever i tried before doesnt matter i just showed you to proof i tried with what i can but what i need is this:

or simply the debit value.
all days in stock added together

What i have so far is this:

WITH CTE AS (
  SELECT dBooked, tAvailable, 
         ROW_NUMBER() OVER (ORDER BY dBooked) - 
         ROW_NUMBER() OVER (PARTITION BY tAvailable ORDER BY dBooked) AS group_id
  FROM (
    SELECT dBooked, tAvailable
    FROM tArtikelHistory
    WHERE kArticle = 250
    UNION ALL
    SELECT SYSDATETIME(), 0
  ) t
),
CTE2 AS (
  SELECT MIN(dBooked) AS start_date, MAX(dBooked) AS end_date, tAvailable, group_id
  FROM CTE
  GROUP BY group_id, tAvailable
)
SELECT start_date, end_date, tAvailable, 
       LAG(end_date) OVER (ORDER BY start_date) AS prev_end_date,
       CASE WHEN tAvailable = 0 THEN DATEDIFF(day, LAG(end_date) OVER (ORDER BY start_date), end_date) END AS gap_in_days
FROM CTE2

now i need to sum the gap_in_days

What you need is to create "groups" where stock goes from > 0 to 0, and then compare the MIN date with the zero date:

Something like this perhaps:

-- Create some sample data
select kArticle, cast(dbooked as date) as dt, CAST(tAvailable AS INT) AS tAvailable
into #data
from 
(
	VALUES (250, N'2019-12-01', 1)
	,	(250, N'2019-12-10', 0)
	,	(250, N'2019-12-20', 1)
	,	(250, N'2019-12-30', 0)
	,	(250, N'2020-01-01', 5)
	,	(250, N'2020-01-10', 4)
	,	(250, N'2020-01-20', 3)
	,	(250, N'2020-01-30', 2)
	,	(250, N'2020-02-10', 1)
	,	(250, N'2020-02-20', 0)
) t (kArticle,dBooked,tAvailable)

-- Group
SELECT	CASE WHEN tAvailable = 0 THEN DATEDIFF(DAY, LastAvailableDay, dt) END AS [DAYS IN STOCK] -- Calculate diff
,	*
FROM	(
	-- Take first available day per group
	SELECT	MIN(dt) OVER(PARTITION BY kArticle, PrevZero ORDER BY dt) AS LastAvailableDay
	,	*
	FROM	(
		select	*
		-- Group from previous stock 0 until the next zero
		,	SUM(CASE WHEn prevAvailable = 0 THEN 1 ELSE 0 END) OVER(PARTITION BY kArticle ORDER BY dt) AS prevZero
		from (
			select	*
			,	LAG(tAvailable) OVER(PARTITION BY kArticle ORDER BY dt) AS prevAvailable
			from #data d
			) x
		) x
	) x
order by dt
			
drop table #data