cs_source (3/1/2015)
Thank you very much! i didn't think to use a function, i do already have a date dimension table.the solution worked using the following code for those who need as well
SELECT guest_name,start_date,end_date
FROM hotel_guests t1
CROSS JOIN (SELECT date FROM date_table) t2
WHERE t2.date >= t1.start_date and t2.date <= t1.end_date
Surely and Inner join to the Dates table with a BETWEEN on the Join is far better than the Cross Join.
SELECT guest_name,start_date,end_date,date
FROM hotel_guests t1
JOIN date_table t2 on date Between Start_date and end_date
Especially if the DATE column on the date table is indexed.
I would actually consider this structure
CREATE TABLE hotel_guests
(
[GuestId] Int NOT NULL PRIMARY KEY IDENTITY(1,1) NOT FOR REPLICATION
,[GuestName] VARCHAR(255) NOT NULL
,[StartDate] DATE NOT NULL
,[Duration] SmallInt NOT NULL
)
GO
CREATE TABLE Hotel_Tasks
(
[HotelTaskId] SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1) NOT FOR REPLICATION
,[HotelTaskType] varchar(50)
)
GO
CREATE TABLE hotel_daily_visit
(
[GuestId] Int NOT NULL --FK to hotel_guests
,[VisitDate] DATE NOT NULL
,[HotelTaskId] SmallInt NOT NULL -- FK to HotelTasks
,[Comment] VARCHAR(255) NULL
)
The Hotel_Task table would include things like 'Room service', 'Cleaning', 'Mini-Bar Check', 'Linen Change', 'Maintenance' etc.
The insert would then be relatively simple as you only ever have to insert a row(s) for the current day IF the date is between the guests StartDate and the StartDate+Duration.
_________________________________________________________________________
SSC Guide to Posting and Best Practices