Thanks John and Thom. - I will try both solutions and report back.

I think I have figured it out:

select k.ProductKey, cost, avgSaleAmount from #headerTable k
outer apply(
select * from #saleDateTable a
outer apply (
    select...

Thanks Phil. I will keep IF Code in mind next time.

Desired results are such:

ProductKey  AvgSaleAmount (within Sale Period)
1               ...

I see a similar solution in this post:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9834a8f9-2acf-4d2d-b4a7-78ad8be15d0a/how-to-produce-the-effect-of-a-date-range-inner-join-without-actually-doing-one?forum=transactsql

how should I go about constructing a calendar table in my case?

I dont think creating a calendar table from the base table will work.

I created a temp table from the base table:
select distinct startdate, enddate
into #calendar
from...

hi Lynn,

I don't have anything at this point. As I am struggling with how to actually write SQL for this.

I was looking for some example that...

Here is the DDL and insert statements for the sample data:

create table dateRangeTest(
    saleid int,
    salemarkdown decimal(3,2),
    startdate date,
    enddate date,
    saleinpreviousyear varchar(5),
    previousyearsaleID int
)

insert...

Just so I can explain it to the management what a read exactly means and of course for my own self.

so how can I best interpret this? what does number of read mean? chunks of data in bytes? how big of chunk if that's the case.

how can I explain...

Can I do that at database level?

Infact the issue that we run into most often is that some of these folks in prod have db_reader access whereas in dev they want full control as dbo so...