sql - Split single row to multiple rows based on date range - Amazon Redshift - Stack Overflow

I need to query data from a Redshift table that represents a room stay. A stay for multiple days will be one single row, but I need those stays to be split into multiple rows to represent a single night per row, with the roomrevenue column to be split by the number of nights (there is a column for this too).

This is an example of the table I'm talking about and the relevant fields:

stayid	arrivaldate	departuredate	lengthofstay	roomrevenueusd
3290134	3/26/17 12:00 AM	3/28/17 12:00 AM	2	76.86

I need to query data from a Redshift table that represents a room stay. A stay for multiple days will be one single row, but I need those stays to be split into multiple rows to represent a single night per row, with the roomrevenue column to be split by the number of nights (there is a column for this too).

This is an example of the table I'm talking about and the relevant fields:

stayid	arrivaldate	departuredate	lengthofstay	roomrevenueusd
3290134	3/26/17 12:00 AM	3/28/17 12:00 AM	2	76.86

This is what I actually want as the resulting query:

stayid	staydate	roomrevenueusd
3290134	3/26/17 12:00 AM	38.43
3290134	3/27/17 12:00 AM	38.43

• sql
• amazon-redshift

Share Improve this question Follow edited Jan 29 at 19:19 jp207 asked Jan 22 at 23:09 jp207jp207 1941010 bronze badges 1

• 1 Made the change. Thanks for the heads up. – jp207 Commented Jan 29 at 19:23

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

You can use recursion to split the revenue for each day.

First start with dividing roomrevenueusd by lengthofstay, then the recursion condition continues to append the individual dates till it reached the departure date.

Syntax is in Sql Server, you might need to change some of the syntax accordingly for Redshift, however DATEADD should be similar in Redshift.

Sample Query

WITH recursion_room_stay AS (
SELECT stayid, arrivaldate AS staydate,
CAST(roomrevenueusd / lengthofstay AS DECIMAL(10, 2)) AS each_roomrevenueusd,
arrivaldate, departuredate
  
FROM test
  
UNION ALL
  
SELECT  stayid,
DATEADD(day, 1, staydate),
each_roomrevenueusd,arrivaldate,
departuredate
FROM recursion_room_stay
WHERE DATEADD(day, 1, staydate) < departuredate
)
SELECT  stayid,staydate, each_roomrevenueusd
FROM recursion_room_stay
ORDER BY stayid desc, staydate
;

for the input

stayid	arrivaldate	departuredate	lengthofstay	roomrevenueusd
3290134	2025-03-26	2025-03-28	2	76.86
2	2025-01-10	2025-01-12	2	100.00

Generates

Example Fiddle

stayid	staydate	each_roomrevenueusd
3290134	2025-03-26	38.43
3290134	2025-03-27	38.43
2	2025-01-10	50.00
2	2025-01-11	50.00