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 :

2) Store Table :

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)-

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

CodePudding user response：

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/

CodePudding user response：

You can do this by creating a temporary table of all the dates between the input date range.

Sample Data Creation Script

CREATE TABLE item_table(store_id VARCHAR(20),item_id VARCHAR(20),offer_start_date DATE,offer_end_date DATE)

INSERT INTO item_table(store_id,item_id,offer_start_date,offer_end_date)
VALUES ('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')

CREATE TABLE store_table(store_id VARCHAR(20),day_of_week VARCHAR(20),store_hour int)

INSERT INTO store_table(store_id,day_of_week,store_hour)
VALUES  ('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)

Final script would be,

-- Input date range
DECLARE @StartDate DATE, @EndDate DATE;
SELECT @StartDate = '2021-11-01', @EndDate = '2021-11-10'; 

-- Create and populate a temporary table
DECLARE @dateList TABLE (date DATE,day_name VARCHAR(20));

WITH DateList(date,day_name) AS (SELECT @StartDate AS DATE,DATENAME(dw,@StartDate) UNION ALL SELECT DATEADD(DAY,1,date), DATENAME(dw,DATEADD(DAY,1,date)) FROM DateList WHERE date < @EndDate)

INSERT INTO @dateList(date,day_name) SELECT * FROM DateList;


-- Main query
SELECT a.store_id,item_id,offer_start_date,offer_end_date,
CASE WHEN SUM(store_hour) IS NULL THEN 0 ELSE SUM(store_hour) END AS 'total_available_hours'
FROM
(
  SELECT store_id,item_id,offer_start_date,offer_end_date,date,day_name
  FROM item_table i
  LEFT JOIN @dateList ON((date BETWEEN offer_start_date AND offer_end_date) OR (offer_end_date IS NULL AND date>=offer_start_date AND date <= GETDATE()))
)a
LEFT JOIN 
(
  SELECT store_id,day_of_week,store_hour
  FROM store_table
)b ON (a.store_id = b.store_id AND a.day_name=b.day_of_week)
GROUP BY a.store_id,item_id,offer_start_date,offer_end_date;