How to calculate sum of a value with only “day of week” using a date-range?

I have 2 tables namely

1. Item table with details of item_id, store_id, offer_start_Date and offer_end_date
2. Store table has store_id, day_of_week, store_hours

The structure of both tables are follows – 1) Item_Table :

Store ID	Item ID	offer_start_Date	offer_end_date
NY0001	FMC0001	2021-10-30	2021-11-04
NY0001	FMC0002	2021-11-08	Null
NY0002	FMC0003	2021-11-02	2021-11-02
NY0002	FMC0004	2021-09-01	2021-10-10

2) Store Table :

Store ID	Day of Week	store Hours
NY0001	Monday	12
NY0001	Tuesday	12
NY0001	Wednesday	12
NY0001	Thursday	12
NY0001	Friday	0
NY0001	Saturday	14
NY0001	Sunday	0
NY0002	Monday	8
NY0002	Tuesday	8
NY0002	Wednesday	8
NY0002	Thursday	8
NY0002	Friday	8
NY0002	Saturday	8
NY0002	Sunday	8

The need is to identify how many hours the item was available in the store during the given date range.

Let’s consider we need to find the total available hours between “2021-11-01” and “2021-11-10” for an item_id. The expected result is as follows (When offer_end_date is not available consider it as today)-

Store ID	Item ID	offer_start_Date	offer_end_date	Total_available_hours
NY0001	FMC0001	2021-10-30	2021-11-04	48
NY0001	FMC0002	2021-11-08	Null	36
NY0002	FMC0003	2021-11-02	2021-11-02	8
NY0002	FMC0004	2021-09-01	2021-10-10	0

Explanation :

1. FMC0001 – Hours available only from ‘2021-11-01’ until ‘2021-11-04’ (4 full days)”
2. FMC0002 – Hours available only between ‘2021-11-07’ and ‘2021-11-10′(From Sunday to Wednesday)”
3. FMC0003 – Hours available for 1 full day
4. FMC0004 – Out of query date range

Answer

Here is a way to do this

create table item_table(store_id varchar(10),item_id varchar(50),offer_start_date date,offer_end_date date);

insert into item_table
select 'NY0001','FMC0001','2021-10-30','2021-11-04' union all
select 'NY0001','FMC0002','2021-11-08',Null union all
select 'NY0002','FMC0003','2021-11-02','2021-11-02' union all
select 'NY0002','FMC0004','2021-09-01','2021-10-10';

create table store_table(store_id varchar(10),day_of_week varchar(50),store_hours int);

insert into store_table
select 'NY0001','Monday',   12         union all
select 'NY0001','Tuesday',    12 union all
select 'NY0001','Wednesday',     12 union all
select 'NY0001','Thursday', 12 union all
select 'NY0001','Friday',    0 union all
select 'NY0001','Saturday', 14 union all
select 'NY0001','Sunday',    0 union all
select 'NY0002','Monday',    8 union all
select 'NY0002','Tuesday',     8 union all
select 'NY0002','Wednesday',      8 union all
select 'NY0002','Thursday',  8 union all
select 'NY0002','Friday',    8 union all
select 'NY0002','Saturday',  8 union all
select 'NY0002','Sunday',    8;

with data
  as (
select a.store_id
      ,a.item_id
      ,a.offer_start_date
      ,a.offer_end_date
      ,dateadd(day,m.rnk,a.offer_start_date) as days_involved     
      ,s.day_of_week
      ,s.store_hours
  from item_table a
cross apply (select * 
               from (select row_number() over(order by (select null))-1 as rnk
                       from master..spt_values
                     )x
              where x.rnk<=datediff(day,offer_start_date,isnull(offer_end_date,getdate()))
             )m
  join store_table s
    on a.store_id=s.store_id
   and s.day_of_week=datename(weekday,dateadd(day,m.rnk,a.offer_start_date))
       ) 
select store_id,item_id,offer_start_date,offer_end_date
      ,sum(case when days_involved between '2021-11-01' and '2021-11-10' then 
                     store_hours
                else 0 
            end) as total_work_hours
  from data
group by store_id,item_id,offer_start_date,offer_end_date  
order by store_id,item_id

+==========+=========+==================+================+==================+
| store_id | item_id | offer_start_date | offer_end_date | total_work_hours |
+==========+=========+==================+================+==================+
| NY0001   | FMC0001 | 2021-10-30       | 2021-11-04     | 48               |
+----------+---------+------------------+----------------+------------------+
| NY0001   | FMC0002 | 2021-11-08       | (null)         | 36               |
+----------+---------+------------------+----------------+------------------+
| NY0002   | FMC0003 | 2021-11-02       | 2021-11-02     | 8                |
+----------+---------+------------------+----------------+------------------+
| NY0002   | FMC0004 | 2021-09-01       | 2021-10-10     | 0                |
+----------+---------+------------------+----------------+------------------+

demo

https://sqlize.online/sql/mssql2017/7f6721a9dbc954661d0c2db44c089bc4/