SQL按月、按用户、按地点计算居住天数

Question

我正在对一个康复组织进行查询，其中租户（客户/患者）第一次到达时住在一栋大楼里，随着治疗的进展，他们搬到另一栋大楼，当他们接近治疗结束时，他们住在一栋大楼里。第三栋大楼。

出于资金目的，我们需要知道租户每个月在每栋建筑中度过了多少个夜晚。 我可以使用 DateDiff 获取总晚数，但如何获取每栋大楼每个月每个客户的总晚数？

例如，John Smith 在 A 楼 9/12-11/3；搬至 B 楼 11/3-15；搬到 C 楼并仍然在那里：11/15 - 今天

哪个查询返回的结果显示他在以下地方度过的夜晚数： A 楼于 9 月、10 月和 11 月。 11月B座 11 月 C 栋

两张桌子上有客户姓名、大楼名称以及入住日期和退房日期，

CREATE TABLE [dbo].[clients](
[ID] [nvarchar](50) NULL,
[First_Name] [nvarchar](100) NULL,
[Last_Name] [nvarchar](100) NULL
) ON [PRIMARY]

--populate w/ two records  
insert into clients (ID,First_name, Last_name)
values ('A2938', 'John', 'Smith')

insert into clients (ID,First_name, Last_name)
values ('A1398', 'Mary', 'Jones')




CREATE TABLE [dbo].[Buildings](
[ID_U] [nvarchar](50) NULL,
[Move_in_Date_Building_A] [datetime] NULL,
[Move_out_Date_Building_A] [datetime] NULL,
[Move_in_Date_Building_B] [datetime] NULL,
[Move_out_Date_Building_B] [datetime] NULL,
[Move_in_Date_Building_C] [datetime] NULL,
[Move_out_Date_Building_C] [datetime] NULL,
[Building_A] [nvarchar](50) NULL,
[Building_B] [nvarchar](50) NULL,
[Building_C] [nvarchar](50) NULL
) ON [PRIMARY]


-- Populate the tables with two records
insert into buildings (ID_U,Move_in_Date_Building_A,Move_out_Date_Building_A, Move_in_Date_Building_B,
Move_out_Date_Building_B, Move_in_Date_Building_C, Building_A, Building_B, Building_C)
VALUES ('A2938','2010-9-12', '2010-11-3','2010-11-3','2010-11-15', '2010-11-15', 'Kalgan', 'Rufus','Waylon')


insert into buildings (ID_U,Move_in_Date_Building_A,Building_A)
VALUES ('A1398','2010-10-6', 'Kalgan')

谢谢您的帮助。

Answer 1

我会使用正确规范化的数据库模式，您的建筑物表没有这样的用处。拆分之后我相信得到你的答案会很容易。

---

编辑（和更新）：这是一个 CTE，它将采用这个奇怪的表结构并将其拆分为更规范化的形式，显示用户 ID、建筑物名称、迁入和迁出日期。通过对您想要的数据进行分组（并使用 DATEPART() 等），您应该能够获得您需要的数据。

WITH User_Stays AS (
    SELECT
        ID_U,
        Building_A Building,
        Move_in_Date_Building_A Move_In,
        COALESCE(Move_out_Date_Building_A, CASE WHEN ((Move_in_Date_Building_B IS NULL) OR (Move_in_Date_Building_C<Move_in_Date_Building_B)) AND (Move_in_Date_Building_C>Move_in_Date_Building_A) THEN Move_in_Date_Building_C WHEN Move_in_Date_Building_B>=Move_in_Date_Building_A THEN Move_in_Date_Building_B END, GETDATE()) Move_Out
    FROM dbo.Buildings 
    WHERE Move_in_Date_Building_A IS NOT NULL   
    UNION ALL
    SELECT
        ID_U, 
        Building_B,
        Move_in_Date_Building_B, 
        COALESCE(Move_out_Date_Building_B, CASE WHEN ((Move_in_Date_Building_A IS NULL) OR (Move_in_Date_Building_C<Move_in_Date_Building_A)) AND (Move_in_Date_Building_C>Move_in_Date_Building_B) THEN Move_in_Date_Building_C WHEN Move_in_Date_Building_A>=Move_in_Date_Building_B THEN Move_in_Date_Building_A END, GETDATE())
    FROM dbo.Buildings 
    WHERE Move_in_Date_Building_B IS NOT NULL
    UNION ALL
    SELECT
        ID_U, 
        Building_C,
        Move_in_Date_Building_C, 
        COALESCE(Move_out_Date_Building_C, CASE WHEN ((Move_in_Date_Building_B IS NULL) OR (Move_in_Date_Building_A<Move_in_Date_Building_B)) AND (Move_in_Date_Building_A>Move_in_Date_Building_C) THEN Move_in_Date_Building_A WHEN Move_in_Date_Building_B>=Move_in_Date_Building_C THEN Move_in_Date_Building_B END, GETDATE())
    FROM dbo.Buildings
    WHERE Move_in_Date_Building_C IS NOT NULL
)
SELECT *
FROM User_Stays
ORDER BY ID_U, Move_In

对示例数据运行的此查询会产生以下输出：

ID_U     Building    Move_In                 Move_Out
-------- ----------- ----------------------- -----------------------
A1398    Kalgan      2010-10-06 00:00:00.000 2010-11-23 18:35:59.050
A2938    Kalgan      2010-09-12 00:00:00.000 2010-11-03 00:00:00.000
A2938    Rufus       2010-11-03 00:00:00.000 2010-11-15 00:00:00.000
A2938    Waylon      2010-11-15 00:00:00.000 2010-11-23 18:35:59.050

(4 row(s) affected)

如您所见，从这里开始，可以更轻松地隔离每个患者或建筑物的天数，还可以查找特定月份的记录并计算正确的停留时间那种情况。请注意，CTE 显示仍在建筑物中的患者的当前日期。

---

编辑（再次）：为了获取所有月份，包括所有相关年份的开始和结束日期，您可以使用如下的 CTE：

WITH User_Stays AS (             
        [...see above...]
    )
,
    Months AS (          
        SELECT  m.IX,
                y.[Year], dateadd(month,(12*y.[Year])-22801+m.ix,0) StartDate, dateadd(second, -1, dateadd(month,(12*y.[Year])-22800+m.ix,0)) EndDate
                FROM    (            
                    SELECT  1 IX UNION ALL 
                    SELECT  2 UNION ALL 
                    SELECT  3 UNION ALL 
                    SELECT  4 UNION ALL 
                    SELECT  5 UNION ALL 
                    SELECT  6 UNION ALL 
                    SELECT  7 UNION ALL 
                    SELECT  8 UNION ALL 
                    SELECT  9 UNION ALL 
                    SELECT  10 UNION ALL 
                    SELECT  11 UNION ALL 
                    SELECT  12 
                )
        m 
            CROSS JOIN (             
                    SELECT  Datepart(YEAR, us.Move_In) [Year] 
                    FROM    User_Stays us UNION 
                    SELECT  Datepart(YEAR, us.Move_Out) 
                    FROM    User_Stays us 
                )
        y 
    )
SELECT  * 
FROM    months;

因此，由于我们现在有可能感兴趣的所有日期范围的表格表示，我们只需将其连接在一起：

WITH User_Stays AS ([...]),
Months AS ([...])
SELECT  m.[Year],
    DATENAME(MONTH, m.StartDate) [Month],
    us.ID_U,
    us.Building,
    DATEDIFF(DAY, CASE WHEN us.Move_In>m.StartDate THEN us.Move_In ELSE m.StartDate END, CASE WHEN us.Move_Out<m.EndDate THEN us.Move_Out ELSE DATEADD(DAY, -1, m.EndDate) END) Days 
FROM    Months m 
JOIN User_Stays us ON (us.Move_In < m.EndDate) AND (us.Move_Out >= m.StartDate)
ORDER BY m.[Year],
    us.ID_U,
    m.Ix,
    us.Move_In

最终产生以下输出：

Year        Month        ID_U     Building   Days
----------- ------------ -------- ---------- -----------
2010        October      A1398    Kalgan     25
2010        November     A1398    Kalgan     22
2010        September    A2938    Kalgan     18
2010        October      A2938    Kalgan     30
2010        November     A2938    Kalgan     2
2010        November     A2938    Rufus      12
2010        November     A2938    Waylon     8

Answer 2

-- 设置您想要的月份的日期

Declare @startDate datetime
declare @endDate datetime

set @StartDate = '09/01/2010'
set @EndDate = '09/30/2010'


select 
-- determine if the stay occurred during this month
    Case When @StartDate <= Move_out_Date_Building_A and @EndDate >= Move_in_Date_Building_A
         Then 
                  (DateDiff(d, @StartDate , @enddate+1) 
                   )
-- drop the days off the front
                - (Case When @StartDate <  Move_in_Date_Building_A
                       Then datediff(d, @StartDate, Move_in_Date_Building_A)
                       Else 0
                  End)
--drop the days of the end
                - (Case When @EndDate > Move_out_Date_Building_A
                       Then datediff(d, @EndDate,  Move_out_Date_Building_A)
                       Else 0
                  End)
        Else 0
    End AS Building_A_Days_Stayed
from Clients c 
inner join Buildings b
on c.id = b.id_u

Answer 3

尝试使用日期表。例如，您可以像这样创建一个：

CREATE TABLE Dates
(
  [date]    datetime,
  [year]    smallint,
  [month]   tinyint,
  [day]     tinyint
)

INSERT INTO Dates(date)
SELECT dateadd(yy, 100, cast(row_number() over(order by s1.object_id) as datetime))
FROM sys.objects s1
  CROSS JOIN sys.objects s2

UPDATE Dates
SET [year] = year(date),
    [month] = month(date),
    [day] = day(date)

只需修改初始日期数量即可满足您的需求（在我的测试实例上，上面生成的日期是从 2000-01-02 到 2015-10-26）。对于日期表，查询非常简单，如下所示：

select c.First_name, c.Last_name,
    b.Building_A BuildingName, dA.year, dA.month, count(distinct dA.day) daysInBuilding
from clients c
    join Buildings b on c.ID = b.ID_U
    left join Dates dA on dA.date between b.Move_in_Date_Building_A and isnull(b.Move_out_Date_Building_A, getDate())
group by c.First_name, c.Last_name,
    b.Building_A, dA.year, dA.month
UNION
select c.First_name, c.Last_name,
    b.Building_B, dB.year, dB.month, count(distinct dB.day)
from clients c
    join Buildings b on c.ID = b.ID_U
    left join Dates dB on dB.date between b.Move_in_Date_Building_B and isnull(b.Move_out_Date_Building_B, getDate())
group by c.First_name, c.Last_name,
    b.Building_B, dB.year, dB.month
UNION
select c.First_name, c.Last_name,
    b.Building_C, dC.year, dC.month, count(distinct dC.day)
from clients c
    join Buildings b on c.ID = b.ID_U
    left join Dates dC on dC.date between b.Move_in_Date_Building_C and isnull(b.Move_out_Date_Building_C, getDate())
group by c.First_name, c.Last_name,
    b.Building_C, dC.year, dC.month

Answer 4

如果您无法重组 Building 表，您可以创建一个查询来对其进行规范化并允许更轻松的计算：

SELECT "A" as Building, BuidlingA as Name, Move_in_Date_Building_A as MoveInDate, 
Move_out_Date_Building_A As MoveOutDate
UNION
SELECT "B", BuidlingB, Move_in_Date_Building_B, Move_out_Date_Building_B 
 UNION
SELECT "C", BuidlingC, Move_in_Date_Building_C, Move_out_Date_Building_C