Trick is to create a new temp table that holds all the dates between the given two dates. Please find the query below. I havent really created the tables and executed it.
DECLARE @StartDate DATETIME , @EndDate DATETIME, @Date DATETIME
DECLARE @AllDates TABLE (Date DATETIME)
SET @StartDate = '2012-01-01'
SET @EndDate = '2012-01-05'
SET @Date = @StartDate
WHILE (@Date <= @EndDate)
BEGIN
INSERT INTO @AllDates
VALUES (@Date)
SET @Date = DATEADD(dd, 1, @Date)
END
SELECT ad.Date
, c.cat_code
,ISNULL(ri.occupied,0) occupied
,ISNULL(ri.Total,0) Total
,ISNULL(ri.available,0) available
,ISNULL(ri.confirmed,0) confirmed
,ISNULL(ri.Tentative,0) Tentative
,c.cstid
FROM @AllDates ad
JOIN Category c ON 1=1
LEFT JOIN RoomInventory ri ON c.cstid = ri.cstId AND c.cat_code = ri.cat_code