Search code examples
sqlsql-serverdaterangedate-range

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
Solution

  • 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/