SQL Server:如何计算不同容量周期的就寝天数

Question

在SQL Server 2008中，我需要生成一个SQL报告，其中包含用户可以输入Startdate、Enddate和Department name的参数;他们得到的结果是这段时间可能的床位占用。然后，我可以将其与实际(实现的(床位占用进行比较，后者提供有关部门效率的信息。

给定此表@@Temptable01：

Department_name Bed_Capacity    CAP_Startdate   CAP_Enddate
------------------------------------------------------------
Dept_ A              20           1-1-2015      31-12-2015
Dept_ A              22           1-1-2016      31-8-2016
Dept_ B              21           1-1-2015      Null
Dept_ C              30           1-1-2017      30-6-2017
Dept_ A              24           1-9-2016      Null
Dept_ C              32           1-7-2017      Null

可能的床位

占用(床位天数(可以在 select 语句中计算

，方法是使用

Bed_Capacity * (enddate – startddate)

当选择与具有不同容量的多个期间重叠的报告期间时，就会出现问题。

我正在考虑使用如下案例陈述：

 Select 
     Department_name, 
     (case when startdate between CAP_Startdate and CAP_Enddate 
                      and (enddate between CAP_Startdate and CAP_Enddate 
                           or CAP_Enddate is null) 
              then Bed_Capacity * (Enddate – CAP_Startdate)) as beddays 
From 
    @@Temptable01

这显然是不完整的(甚至不正确(，但我似乎无法找出正确的方法。不知何故，我需要确定周期与每个容量类别重叠的天数，然后乘以该容量并将它们全部相加

谁能给我任何建议？

Answer 1

你不能使用 DATEDIFF 函数计算出重叠天数吗？

DECLARE @t TABLE(
 Department NVARCHAR(10),
 BedCapacity INT,
 CAP_StartDate DATE,
 CAP_EndDate DATE)
INSERT INTO @t
VALUES
 ('Dept_ A', 20,'2015-01-01', '2015-12-31'),
 ('Dept_ A', 22,'2016-01-01', '2016-08-31'),
 ('Dept_ B', 21,'2015-01-01', NULL),
 ('Dept_ C', 30,'2017-01-01', '2017-06-30'),
 ('Dept_ A', 24,'2016-09-01', NULL),
 ('Dept_ C', 32,'2017-07-01', NULL)
DECLARE 
 @StartDate DATE = '2015-03-01',
 @EndDate DATE = '2016-02-28'
SELECT 
 *,
 t.BedCapacity * DATEDIFF(DAY, 
 CASE WHEN t.CAP_StartDate > @StartDate THEN t.CAP_StartDate ELSE @StartDate END, 
 CASE WHEN COALESCE(t.CAP_EndDate, @EndDate) < @EndDate THEN t.CAP_EndDate ELSE @EndDate END) Total
FROM @t t
WHERE t.CAP_StartDate <= @EndDate
 AND COALESCE(t.CAP_EndDate, @EndDate) >= @StartDate