Select average of product rate using date backtracking

Question

I'm querying monthly average rate of all products, average of all Friday rates of that month. My table and data script is:

CREATE TABLE [dbo].[Product_Entry](
    [ProductCode] [varchar](10) NOT NULL,
    [Rate] [decimal](18, 0) NULL,
    [RateDate] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050101', CAST(56 AS Decimal(18, 0)), CAST(N'2019-04-05' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050102', CAST(60 AS Decimal(18, 0)), CAST(N'2019-04-05' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050103', CAST(65 AS Decimal(18, 0)), CAST(N'2019-04-04' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050101', CAST(50 AS Decimal(18, 0)), CAST(N'2019-04-12' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050102', CAST(64 AS Decimal(18, 0)), CAST(N'2019-04-11' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050103', CAST(70 AS Decimal(18, 0)), CAST(N'2019-04-12' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050101', CAST(55 AS Decimal(18, 0)), CAST(N'2019-04-15' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050102', CAST(50 AS Decimal(18, 0)), CAST(N'2019-04-16' AS Date))
GO
INSERT [dbo].[Product_Entry] ([ProductCode], [Rate], [RateDate]) VALUES (N'050103', CAST(68 AS Decimal(18, 0)), CAST(N'2019-04-17' AS Date))
GO

So I make a function which take Month & Year and returns all fridays

 CREATE PROCEDURE [dbo].[GetallFridaysinMonth] (  
 @month VARCHAR(2) = NULL  
 ,@year VARCHAR(5) = NULL  
 )  
AS  
BEGIN  
 SELECT Fridays = DATEADD(yy, DATEDIFF(yy, 0, '' + @year + '-' + @month + '-' + '01'), n.num)  
 INTO #t  
 FROM (  
  SELECT TOP 366 num = ROW_NUMBER() OVER (  
    ORDER BY a.NAME  
    ) - 1  
  FROM dbo.syscolumns a  
   ,dbo.syscolumns b  
  ) n  
 WHERE DATENAME(weekday, DATEADD(yy, DATEDIFF(yy, 0, '' + @year + '-' + @month + '-' + '01'), n.num)) = 'Friday'  

 SELECT Fridays  
 FROM #t  
 WHERE datepart(month, Fridays) = @month  

 DROP TABLE #t  
END 

Then use this function to query averages

BEGIN  
  CREATE TABLE #t121 (Fridays DATETIME,id INT IDENTITY(1, 1));  

  INSERT INTO #t121 (Fridays)  
  EXEC dbo.GetallFridaysinMonth @month,@year;  

   SELECT ProductCode  
    ,convert(DECIMAL(18), avg(Rate)) AS AverageRate  
   FROM dbo.product_entry  
   WHERE RateDate IN (  
     SELECT Fridays  
     FROM #t121  
     )  
   GROUP BY ProductCode  
  DROP TABLE #t121  
END  

This works very well until my client modify requirement and say if there is no rate in Friday then check in Thursday, if not in Thursday then check in Wednesday means backtrack to Saturday to find that Weeks Rate.

Now if there is only 2 or 3 products then I can use Case condition but don't know how to backtrack for all products which are over 250.

My expected result of demo data is

ProductCode   AverageRate
-------------------------
050101      54
050102      58
050103      68

After round off.

Please help me to solve this. Thanks.

Answer 1

These could be the minimum changes to achieve the new requirements:

begin
    create table #t121 (Fridays datetime);

    insert into #t121 (Fridays)
    exec dbo.GetallFridaysinMonth @month, @year;

    with AVGDaily as (
        select ProductCode, RateDate, AVG(Rate) as AVGDay
        from dbo.Product_Entry
        where month(RateDate)=@month and year(RateDate)=@year
        group by ProductCode, RateDate
    )
    select ProductCode
        ,  convert(decimal(18), AVG(AVGRate)) as AverageRate
    from (
        select distinct t.Fridays, AVGDaily.ProductCode, 
        AVGRate=(
            select top (1) AVGDay
            from AVGDaily i2
            where ProductCode = AVGDaily.ProductCode
                and i2.RateDate between
                    DATEADD(DD, -6, t.Fridays) and t.Fridays
            order by RateDate desc)
        from AVGDaily, #t121 as t) g
    group by ProductCode
    drop table #t121
end

and maybe the proc could be improved:

CREATE Proc GetallFridaysinMonth(@month varchar(2), @year varchar(4), @dw tinyint=5) as 
declare @dateStart datetime, @maxDDinMM tinyint
select @dateStart = cast(right('20'+@year,4)+right('0'+@month,2)+'01' as datetime)
    ,  @maxDDinMM = DATEDIFF(DD, @dateStart, DATEADD(MM, 1, @dateStart)) - 1;
with ADD_cte as (
    select 0 as AddDays
    union all
    select AddDays + 1 from ADD_cte where AddDays < @maxDDinMM
)
select DATEADD(DD, AddDays, @dateStart) as SalesDay
from ADD_cte
where (DATEPART(DW, DATEADD(DD, AddDays, @dateStart)) + @@DATEFIRST + 5) % 7 + 1 = @dw