CODEWITHSUNDEEP
sql-servert-sql
Robert
Robert

Reputation: 13

Output a row for each day between two dates

Imagine a hotel where occupants move-in/out into different rooms. This information is stored in the database as a single row in our Occupancy table.

For instance, Occupant 1000 arrived on 3/3 and checked out on 3/5 into bed 100. They came back again on 4/2 and stayed in bed 101. Occupant 1001 stayed for 1 night in bed 100 on 4/1.

I need to generate a row for each day between the move-in and move-out dates by occupant and by bed.

Here's what the data currently looks like:

+------------+-------+------------+-------------+
| OccupantID | BedID | MoveInDate | MoveOutDate |
+------------+-------+------------+-------------+
|       1000 |   100 | 3/3/2016   | 3/5/2016    |
|       1000 |   101 | 4/2/2016   | 4/3/2016    |
|       1001 |   100 | 4/1/2016   | 4/1/2016    |
+------------+-------+------------+-------------+

Here's what the desired output would look like.

+------------+-------+----------+
| OccupantID | BedID |   Date   |
+------------+-------+----------+
|       1000 |   100 | 3/3/2016 |
|       1000 |   100 | 3/4/2016 |
|       1000 |   100 | 3/5/2016 |
|       1000 |   101 | 4/2/2016 |
|       1000 |   101 | 4/3/2016 |
|       1001 |   100 | 4/1/2016 |
+------------+-------+----------+

Upvotes: 1

Views: 984

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

try this,no need of any partition function.

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

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use following TSQL to do this:

SQL demo here

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

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this with the help of a Tally Table:

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

ONLINE DEMO

Upvotes: 1

Related Questions