Q:

Generating Date and Column data from a list between two dates (SQL Server)

I have a set of data I need to fill some gaps in. I cant just use a DateTable as I need a location to be included.
I would need to select all the room numbers (Select roomnum from rooms) and generate a table with the date range for each room.
What the table looks like with the missing Dates/Rooms
Date        Room  Occupents
2016-05-11  100   2
2016-05-11  102   2
2016-05-12  102   1
2016-05-12  104   3
2016-05-12  105   1
2016-05-13  101   2

The Reference Table I would like to build between two dates.
Date        Room  
2016-05-11  100
2016-05-11  101  
2016-05-11  102
2016-05-11  103  
2016-05-11  104  
2016-05-11  105
2016-05-12  100
2016-05-12  101  
2016-05-12  102
2016-05-12  104
2016-05-12  105
2016-05-13  100
2016-05-13  101
2016-05-13  102
2016-05-13  103
2016-05-13  104
2016-05-13  105

I was hoping to be able to create the reference table without a cursor. Anyone have any Ideas?

A:

Declare @Table table (Date date,Room varchar(10),Occupents int)
Insert into @Table (Date,Room,Occupents) values 
('2016-05-11','100',2),
('2016-05-11','102',2),
('2016-05-12','102',1),
('2016-05-12','104',3),
('2016-05-12','105',1),
('2016-05-13','101',2)

Select Date,Room 
 From (Select Distinct Date from @Table) A
 Join (Select Distinct Room from @Table) B on 1=1
 Order by 1,2

Returns
Date        Room
2016-05-11  100
2016-05-11  101
2016-05-11  102
2016-05-11  104
2016-05-11  105
2016-05-12  100
2016-05-12  101
2016-05-12  102
2016-05-12  104
2016-05-12  105
2016-05-13  100
2016-05-13  101
2016-05-13  102
2016-05-13  104
2016-05-13  105

