Create procedure CALENDARFY_Display (@START_DATE DATETIME,@ENDDATE DATETIME) AS
--exec CALENDARFY_Display '2011-04-01', '2012-03-31'
--DECLARE @START_DATE DATETIME
--DECLARE @ENDDATE DATETIME
--SET @START_DATE = '2011-04-01'
--SET @ENDDATE = '20120331';

delete from CALENDAR
BEGIN
WITH CTE_DATES AS
(
SELECT
    @START_DATE DateValue UNION ALL SELECT
    DateValue + 1
FROM CTE_DATES
WHERE DateValue + 1 <= @ENDDATE
)
INSERT INTO CALENDAR (Dates)
    SELECT
        CAST(DateValue AS date)
    FROM CTE_DATES
    OPTION (MAXRECURSION 0)
END
    
drop table tempp

 select 
 Dates=replace(convert(varchar(10), c.Dates, 120),'-','')+'|'+datename(weekday, c.Dates ),
 e.UserName,Descs
into tempp
 from Calendar c
left join 
(
SELECT id,Dt,u.UserName,Descs=cast(isnull([text],'') as varchar)+'-'+cast(Isnull(room_id,'') as varchar) 
from events e
left join aspnet_Users u on u.[userId]=e.[user_id] 
CROSS APPLY fnGetDatesInRange([Start_Date], End_Date)
)  e on convert(varchar(10), Dt, 120)=c.Dates
where dates between @START_DATE and @ENDDATE











DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Dates) 
                    from tempp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT UserName ,' + @cols + '
            from 
            (
                select Dates, UserName, Descs
                from tempp
            ) x
            pivot 
            (
                max(Descs)
                for Dates in (' + @cols + ')
            ) p where UserName is not null'

execute(@query)