You are missing the hash(#) prefix for the #busdetails in the from clause

😎

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 /* change busdetails to #busdetails */

GROUP BY m.bus_id '

Another error is the difference in the date format, this query will not return any values