为两个日期之间的每一天输出一行

Question

想象一下，在一家酒店里，入住者搬进/搬出不同的房间。这些信息作为占用表中的一行存储在数据库中。

例如，乘客1000于3/3到达，并于3/5入住100号床。他们在4月2日再次回来，并住在101号床上。4月1日，乘客1001在100号床上住了一晚。

我需要在入住和退房日期之间按入住者和床生成一行。

以下是目前的数据：

+------------+-------+------------+-------------+|占用人ID |床位ID |入住日期|迁出日期|+------------+-------+------------+-------------+|1000 | 100 | 2016年3月3日| 2016年5月3日||1000 | 101 | 2016年4月2日| 2016年3月4日||1001 | 100 | 2016年4月1日| 2016年1月4日|+------------+-------+------------+-------------+

以下是所需输出的样子。

+------------+-------+----------+|占用人ID |床ID |日期|+------------+-------+----------+|1000 | 100 | 2016年3月3日||1000 | 100 | 2016年3月4日||1000 | 100 | 2016年3月5日||1000 | 101 | 2016年4月2日||1000 | 101 | 2016年4月3日||1001 | 100 | 2016年4月1日|+------------+-------+----------+

Answer 1

您可以在计数表的帮助下完成此操作：

WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(N) AS(
    SELECT TOP(SELECT MAX(DATEDIFF(DAY, MoveInDate, MoveOutDate)) + 1 FROM tbl) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT
    t.OccupantID,
    t.BedID,
    Date = DATEADD(DAY, ct.N-1, t.MoveInDate)
FROM tbl t
CROSS JOIN CteTally ct
WHERE
    DATEADD(DAY, ct.N-1, t.MoveInDate) <= t.MoveOutDate
ORDER BY
    t.OccupantID, t.BedID, Date

在线演示

Answer 2

您可以使用以下TSQL来执行此操作：

SQL演示

--create table occupancy
--(OccupantID int,BedID int,MoveInDate date,MoveOutDate  date);
--insert into occupancy values
--(1000,100,'3/3/2016','3/5/2016'),
--(1000,101,'4/2/2016','4/3/2016'),
--(1001,100,'4/1/2016','4/1/2016');
create table #t(OccupantID int,BedID int,OccupiedDate date);
create table #temp (id int,OccupantID int,BedID int,MoveInDate date,MoveOutDate  date);
insert into #temp
select 
row_number() over( order by occupantId,moveinDate desc) id, occupantid,bedid,moveindate, moveoutdate from occupancy
Declare @c int
Declare @startdate date, @enddate date
Select @c=MAX(id) from #temp
WHILE(@c>0)
BEGIN
SELECT @startdate=MoveinDate, @enddate=Moveoutdate from #temp where id=@c
INSERT INTO #t
SELECT occupantid,bedid,@startdate from #temp where id=@c
    WHILE (@startdate<@enddate)
    BEGIN
        SET @startdate=DATEADD(d,1,@startdate)
        INSERT INTO #t
        SELECT occupantid,bedid,@startdate from #temp where id=@c
    END
SET @c=@c-1
END
select * from #t
drop table #temp,#t

Answer 3

试试这个，不需要任何分区函数。

declare @t table (OccupantID int,BedID int, MoveInDate date, MoveOutDate date)
insert into @t values
(1000,100,'3/3/2016','3/5/2016')
,(1000,101,'4/2/2016','4/3/2016')
,(1001,100,'4/1/2016','4/1/2016')
;WITH CTE
AS (
    SELECT OccupantID
        ,BedID
        ,MoveInDate [StayDate]
    FROM @t
    UNION ALL
    SELECT a.OccupantID
        ,a.BedID
        ,dateadd(day, 1, b.StayDate)
    FROM @t A
    INNER JOIN CTE B ON a.OccupantID = b.OccupantID
        AND a.BedID = b.BedID
    WHERE b.StayDate < a.MoveOutDate
    )
SELECT *
FROM CTE
ORDER BY OccupantID
    ,bedid