DECLARE @FromDate AS VARCHAR(20) = '21-Nov-2011 00:00:00'
DECLARE @ToDate AS VARCHAR(20) = '26-Nov-2011 23:59:59'
Declare @GuestHouseNo int=46
DECLARE @sql VARCHAR(max)=''
DECLARE @Columns VARCHAR(max)
SELECT @Columns = STUFF((SELECT '],[' + CONVERT(VARCHAR(10), MR.ROOM_NO) + ' - ' + CONVERT(VARCHAR(100), MRB.MBed_Name)
FROM MGuestHouse MGH
INNER JOIN Mroom MR ON MGH.GH_SRNO = MR.ROOM_GHOUSENO
INNER JOIN MroomBeds MRB ON MR.ROOM_SRNO = MRB.MBed_RoomSrno
WHERE MGH.GH_SRNO = @GuestHouseNo
ORDER BY MR.ROOM_NO
FOR XML path('')), 1, 2, '') + ']'
SET @sql += 'DECLARE @i AS INT=0; '
SET @sql += 'DECLARE @DT AS TABLE( '
SET @sql += 'Fdt DATETIME, '
SET @sql += 'Tdt DATETIME); '
SET @sql += 'WHILE @i < DATEDIFF(d, ''' + @FromDate + ''', ''' + @ToDate + ''') + 1 '
SET @sql += 'BEGIN '
SET @sql += 'INSERT INTO @DT '
SET @sql += 'VALUES (DATEADD(d, @i, ''' + @FromDate + '''), '
SET @sql += 'CONVERT(VARCHAR(11), DATEADD(d, @i, ''' + @FromDate + '''), 106) + '' 23:59:59'' ) '
SET @sql += 'SET @i += 1 '
SET @sql += 'END; '
SET @sql+='select dt [Date], ' + @Columns + ' '
SET @sql+='from( '
SET @sql+='SELECT CONVERT(VARCHAR(11), Fdt, 106) as DT '
SET @sql+=',CONVERT(VARCHAR(10), MRoom.ROOM_NO) + '' - '' + CONVERT(VARCHAR(100), MroomBeds.MBed_Name) AS [Room-Bed] '
SET @sql+=',CASE '
SET @sql+='WHEN TRB.trb_splroombyalloter = ''Y'' THEN (CASE '
SET @sql+='WHEN TRB.TRB_GUESTNAME IS NULL THEN ''Available'' '
SET @sql+='ELSE TRB.TRB_GUESTNAME '
SET @sql+='END + ''( '' + CASE TRB.trb_splroombyalloter '
SET @sql+='WHEN ''Y'' THEN ''*'' '
SET @sql+='ELSE '''' '
SET @sql+='END + '' ) '') '
SET @sql+='WHEN TRB.TRB_GUESTNAME IS NULL THEN ''Available'' '
SET @sql+='ELSE TRB.TRB_GUESTNAME '
SET @sql+='END AS GuestName '
SET @sql+=',Fdt,Tdt '
SET @sql+='FROM MGuestHouse '
SET @sql+='INNER JOIN MRoom ON MGuestHouse.GH_SRNO = MRoom.ROOM_GHOUSENO '
SET @sql+='INNER JOIN MroomBeds ON MRoom.ROOM_SRNO = MroomBeds.MBed_RoomSrno '
SET @sql+='CROSS JOIN @DT '
SET @sql+='LEFT OUTER JOIN TRoomBooking TRB ON MroomBeds.MBed_srno = TRB.trb_bedno '
SET @sql+='AND TRB.TRB_FROMDATE BETWEEN fdt AND Tdt '
SET @sql+='AND TRB.TRB_TODATE BETWEEN Fdt AND Tdt '
SET @sql+='WHERE MGuestHouse.GH_SRNO = '+ CAST(@GuestHouseNo as varchar) +' ) as MainTable '
SET @sql+='PIVOT (max(GuestName) FOR [Room-Bed] IN (' + @Columns + ')) AS PivotTable '
--print @sql
EXEC( @sql)
No comments:
Post a Comment