Search code examples
sqlsql-serverwhile-loop

SQL to fill missing value by AVG in each Group

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

enter image description here

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

enter image description here

Solution

  • 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