How to separate null and 0 value

[Julia1231]

Hi everyone,

I want to process the delta which is null in the middle of a time series by taking the next delta after the null to divide to the (count of null + 1)
Here is the data:

time	id	value	delta
01/02/2022	123	12
02/02/2022	123	15	3
03/02/2022	123	20	5
04/02/2022	123
05/02/2022	123
06/02/2022	123
07/02/2022	123	60	40
08/02/2022	123	60	0
09/02/2022	123
10/02/2022	123
01/02/2022	145	20
02/02/2022	145	50	30
03/02/2022	145	70	20
04/02/2022	145	100	30
05/02/2022	145
06/02/2022	145
07/02/2022	145	190	90
08/02/2022	145
09/02/2022	145
10/02/2022	145
01/02/2022	987	50
02/02/2022	987	100	50
03/02/2022	987	160	60
04/02/2022	987	200	40
05/02/2022	987	230	30
06/02/2022	987	280	50
07/02/2022	987	360	80
08/02/2022	987	420	60
09/02/2022	987	500	80
10/02/2022	987	550	50

Here is when I untable

time	123	145	987
01/02/2022	0	0	0
02/02/2022	3	30	50
03/02/2022	5	20	60
04/02/2022	10	30	40
05/02/2022	10	30	30
06/02/2022	10	30	50
07/02/2022	10	30	80
08/02/2022	0	0	60
09/02/2022		0	80
10/02/2022		0	50

As in the table, there is 1 record in 08/02/2022 when delta is 0. For other data where it doesn't have data, it is null --> ok
But for id=145, delta from 08 to 10/02 should be empty.
So that when I calculate the avg(delta), it will not effect.

So my question is how to distinguish the zero and the null in this case?

Thanks!

[ITWhisperer]

How did you get from your first table to your second table?