SQL to fill missing value by AVG in each Group

I want to fill missing date and Price with Average for below table

Sample data

if OBJECT_ID('tempdb..#mytable') is not null DROP TABLE #mytable
CREATE TABLE #mytable (CreateDate datetime, Product Varchar(4), Price money)
INSERT INTO #mytable VALUES
('2023-06-09 17:01:00.000','Tree',1),
('2023-06-09 17:02:00.000','Tree',2),
('2023-06-09 17:03:00.000','Tree',3),
('2023-06-09 17:04:00.000','Tree',4),
('2023-06-09 17:01:00.000','BB',20),
('2023-06-09 17:02:00.000','BB',40),
('2023-06-09 17:04:00.000','BB',60),
('2023-06-09 17:01:00.000','Car',20),
('2023-06-09 17:03:00.000','Car',30),
('2023-06-09 17:04:00.000','Car',50)

Step 1 is to create a complete Datetime set

if OBJECT_ID('tempdb..#DateRange') is not null DROP TABLE #DateRange
Create Table #DateRange(CreateDate datetime Primary key Clustered)
GO

Declare @startdate datetime = '2023-06-09 17:01:00', @endtime datetime = '2023-06-09 17:04:00'
While (@startdate <= @endtime)
BEGIN
Insert into #DateRange values (@startdate)
set @startdate = DATEADD(MINUTE, 1, @startdate)
END

Step 2 to fill NULL with average of Only ONE upper and Only ONE lower number , not AVG of all rows

SELECT d.CreateDate,
COALESCE(Price, AVG(Price) OVER ()) as New_Price,
m.*
From #DateRange d
LEFT OUTER JOIN #mytable m on d.CreateDate = m.CreateDate
and Product = 'BB'

My question is how loop through each Product Group so I don't need to set product = BB explicitly?

My expected result is

Try the following (if you are using SQL Server 2022):

declare @start_time datetime = '2023-06-09 17:01:00';
declare @end_time datetime = '2023-06-09 17:04:00';

with Calendar as 
(-- step1: build a calender table based on your start and end time.
  select top(datediff(minute, @start_time, @end_time) +1)
         dateadd(minute, row_number() over (order by number) -1, @start_time) dt
  from master..spt_values
)
select all_dts.dt as CreateDate,
       p.product,
       -- use first_value and last_value functions with the (ignore nulls) option to get the next and previous not null price value.
       isnull(tbl.price, 
              (isnull(first_value(tbl.price) ignore nulls over (partition by p.product order by all_dts.dt range between current row and unbounded following), 0) +
              isnull(last_value(tbl.price) ignore nulls over (partition by p.product order by all_dts.dt range between unbounded preceding and current row), 0)) /2.0
            ) as price 
from Calendar all_dts
-- step2: do a cross join between the generated dates in step1 and the distinct values of products to get all possisble combinations of (dates and products)
cross join (select distinct product from #mytable) p
-- step3: do a left join with the table to get the missing dates
left join #mytable tbl
on all_dts.dt = tbl.CreateDate and
   p.product = tbl.product
order by p.product, all_dts.dt

demo

For previous versions of the SQL server, we could implement a subquery instead of the first_value and last_value functions with the ignore null:

declare @start_time datetime = '2023-06-09 17:01:00';
declare @end_time datetime = '2023-06-09 17:04:00';

with Calendar as 
(-- step1: build a calender table based on your start and end time.
  select top(datediff(minute, @start_time, @end_time) +1)
         dateadd(minute, row_number() over (order by number) -1, @start_time) dt
  from master..spt_values
)
select all_dts.dt as CreateDate,
       p.product,
       -- Use subquery to get the next and previous not null price value.
       isnull(tbl.price, 
              (isnull((select top 1 price from #mytable mtbl where mtbl.product = p.product and mtbl.CreateDate > all_dts.dt order by CreateDate), 0) +
               isnull((select top 1 price from #mytable mtbl where mtbl.product = p.product and mtbl.CreateDate < all_dts.dt order by CreateDate desc), 0)) /2.0
            ) as price 
from Calendar all_dts
-- step2: do a cross join between the generated dates in step1 and the distinct values of products to get all possisble combinations of (dates and products)
cross join (select distinct product from #mytable) p
-- step3: do a left join with the table to get the missing dates
left join #mytable tbl
on all_dts.dt = tbl.CreateDate and
   p.product = tbl.product
order by p.product, all_dts.dt

demo

You can use a CTE.
First create list date and create list unique Product

Then join List Date_uniqueProduct with table mytable for find gap with date

Declare @startdate datetime = (select  min(CreateDate) from #mytable)
, @endtime datetime =(select  max(CreateDate) from #mytable)


;with Listunique as (
select   distinct Product
from #mytable
),

_List (date_,Product) as ( 
		select @startdate as date_,Product
		from (select distinct Product from  Listunique)a
		union all
		select  DATEADD(minute,1, date_) as date_,Product
		from _List
		where date_ <@endtime

)
select a.*,f.price
from _List a

full join #mytable b on a.date_=b.CreateDate and a.Product=b.Product
outer apply (
				select sum(ISNULL(p,0) +ISNULL(n,0)) /2 as price
				from (
						select  Price as p,0 as n from #mytable l 
						where l.Product=a.Product and l.CreateDate=DATEADD(minute,-1, a.date_)
						union 
						select 0 as p,Price as n from #mytable l 
						where l.Product=a.Product and l.CreateDate=DATEADD(minute,1, a.date_)
		        )d
		    )f
where b.CreateDate is null

Demo

Use GENERATE_SERIES to create a date range, join this list of dates to the table to identify the missing values, and then fill in the missing values using the average of only the first and last record of each group using (FIRST_VALUE and LAST_VALUE):

Declare @startdate datetime = (select min(CreateDate) from #mytable)
,@endtime datetime =(select max(CreateDate) from #mytable);

with daterange as (
  SELECT DATEADD(minute,value,@startdate) as CreateDate, Product
  FROM GENERATE_SERIES(datepart(minute, @startdate) - 1, 
                     datepart(minute, @endtime) - 1,1)
  CROSS JOIN (SELECT DISTINCT Product FROM #mytable) p
),
cte as (
  SELECT d.*, t.Price
  FROM daterange d
  LEFT JOIN #mytable t on d.CreateDate = t.CreateDate AND d.Product = t.Product
),
avg_cte as (
  select *,(COALESCE(first_value(price) ignore nulls over (partition by product order by CreateDate range between current row and unbounded following), 0) +
           COALESCE(last_value(price) ignore nulls over (partition by product order by CreateDate range between unbounded preceding and current row), 0)) /2.0 as avg_price
  from cte
)
select CreateDate, Product, avg_price
from avg_cte
where price is null

Result :

CreateDate	            Product	Price
2023-06-09 17:03:00.000	BB	    50.0000
2023-06-09 17:02:00.000	Car	    25.0000

Demo here