如何使用日期范圍僅計算“星期幾”的值的總和？

我有2張桌子

1. 包含 item_id、store_id、offer_start_Date 和 offer_end_date 詳細資訊的商品表
2. 商店表有 store_id、day_of_week、store_hours

兩個表的結構如下 - 1) Item_Table :

2）存盤表：

需要確定商品在給定日期范圍內在商店中可用的小時數。

假設我們需要為 item_id 找到“2021-11-01”和“2021-11-10”之間的總可用小時數。預期結果如下（當 offer_end_date 不可用時，將其視為今天）-

說明：

1. FMC0001 - 時間僅從“2021-11-01”到“2021-11-04”（4 天）"
2. FMC0002 - 僅在“2021-11-07”和“2021-11-10”之間可用（從星期日到星期三）”
3. FMC0003 - 一整天的可用時間
4. FMC0004 - 超出查詢日期范圍

uj5u.com熱心網友回復：

這是一種方法來做到這一點

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/

uj5u.com熱心網友回復：

您可以通過創建一個包含輸入日期范圍之間的所有日期的臨時表來完成此操作。

示例資料創建腳本

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)

最終的劇本是，

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

標籤：sql sql-server 日期 范围 日期范围

上一篇：將BigQuery中帶有日期文本和數字的STRING轉換為DATETIME

下一篇：在C#中找出任何年份的特定日期是否介于其他兩個日期之間