declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')
insert into #bus_master values(101,'B','lekshmi')
insert into #bus_master values(102,'C','lekshmi')
insert into #bus_master values(103,'D','krishna')
insert into #bus_master values(104,'E','krishna')
insert into #bus_master values(105,'F','krishna')
create table #busdetails( bus_id int,tour_date datetime,status varchar(10))
insert into #busdetails values(103,'2013-10-01','booked')
insert into #busdetails values(102,'2013-10-01','booked')
insert into #busdetails values(100,'2013-10-02','booked')

;WITH Dates AS(
    SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date
    FROM (VALUES(0),(0),(0),(0),(0),(0))E(N),
         (VALUES(0),(0),(0),(0),(0),(0))E2(N) 
)
SELECT @Query =  'SELECT m.bus_id  as ID ' + CHAR(13) 
    + (SELECT CHAR(9) + ',MAX( CASE WHEN tour_date = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13)
        FROM Dates
        WHERE Month_date BETWEEN @StDt AND @EnDt
        ORDER BY Month_date
        FOR XML PATH(''),TYPE).value('.','varchar(max)') 
    + '
    FROM #bus_master m
    LEFT
    JOIN busdetails b ON m.bus_id = b.bus_id  
    GROUP BY m.bus_id  '
execute(@Query)
drop table #bus_master
drop table #busdetails