Calculate Average Cost for last 7 or less days in SQL

Question

I have a SQL Server 2012 table which looks like this:

Date       Product   Cost   AvgCost
----------------------------------
4/7/2019    ProdA   3   NULL
4/9/2019    ProdA   2   NULL
4/10/2019   ProdA   4   NULL
4/24/2019   ProdA   4   NULL
4/30/2019   ProdA   1   NULL

I am trying to Calculate the value for AvgCost based on the below conditions:

1. If there are rows for the last 7 days or less then take simple average of "Cost" for those days
2. If no rows exist for last 7 days or less then simply input 1 for AvgCost

Code:

SELECT [Date], Product, Cost, oa.AvgCost
FROM [Table1] A
OUTER APPLY
    (SELECT AVG(A1.Cost) as AvgCost
     FROM [Table1] A1
     WHERE A1.Product = A.Product
       AND A1.date BETWEEN DATEADD (Day, -6, DATEADD(DAY, -1, A.date)) 
                       AND DATEADD(DAY, -1, A.date)) oa 
WHERE 
    a.date BETWEEN '04/1/2019' AND '04/30/2019'

The code only seems to work only if there are rows for exactly 7 days prior

(For ex: I am able to get the correct value for 4/8/2019 if I have rows from date 4/1/2019 - 4/7/2019)

Expected Result:

 Date    Product Cost   AvgCost
 4/7/2019   ProdA   3   1   -- no rows exist for last 7 days or 
                            --    less then 1
 4/9/2019   ProdA   2   3   -- Only 1 rows exists for last 7 days or 
                            --     less then average for that day
 4/10/2019  ProdA   4   2.5 -- Average cost for 4/7 and 4/9
 4/24/2019  ProdA   4   1   -- no rows exist for last 7 days or 
                            --    less then 1
 4/30/2019  ProdA   1   4 --Only 1 rows exists for last 7 days or 
                          --       less then average for that day

Actual Result

  Date  Product  Cost   AvgCost
  4/7/2019  ProdA   3   NULL
  4/9/2019  ProdA   2   NULL
  4/10/2019 ProdA   4   NULL
  4/24/2019 ProdA   4   NULL
  4/30/2019 ProdA   1   NULL

Answer 1

So I wound up rewriting your query a bunch to make sense to me, as well as adding in the actual code to get your example. I also added the code to get the 1 where the average is missing or less than 0. Somewhere in there I did something that made it work exactly as you specify, but I don't know where sorry.

drop table if exists #table1
create table #table1 (d date, Product nvarchar(max), Cost float, AvgCost FLOAT)
insert into #table1 values ('20190407', 'ProdA', 3, null)
insert into #table1 values ('20190409', 'ProdA', 2, null)
insert into #table1 values ('20190410', 'ProdA', 4, null)
insert into #table1 values ('20190424', 'ProdA', 4, null)
insert into #table1 values ('20190430', 'ProdA', 1, null)


SELECT [d], Product, Cost, iif(isnull(oa.AvgCost, 0) < 1, 1, oa.AvgCost) as AvgCost
FROM [#table1] A
OUTER APPLY
    (
        SELECT AVG(A1.Cost) as AvgCost
        FROM [#table1] as A1
        WHERE A1.Product = A.Product
            AND A1.d BETWEEN DATEADD(Day, -7, A.d) 
                            AND DATEADD(DAY, -1, A.d)
    ) as oa 
WHERE a.d BETWEEN '20190104' AND '20190430'

Results:

d           Product Cost    AvgCost
2019-04-07  ProdA   3       1
2019-04-09  ProdA   2       3
2019-04-10  ProdA   4       2.5
2019-04-24  ProdA   4       1
2019-04-30  ProdA   1       4