Hi, I want to display booking of one month. following code should display the 31 days of month august. i have added 3 booking
but it shows 33 days .how to avoid this
declare @dte as datetime ='2013-08-01'
declare @StDt as Datetime = DATEADD(dd,-(DAY(GETDATE())-1),@dte)
declare @EnDt as datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dte)+1,0))
Declare @Cnt as int = datepart(dd,@EnDt)
Declare @inc as int = 0
Create table #temp (Month_date datetime)
while @inc < @cnt
begin
insert into #temp
select DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))
set @inc = @inc + 1
end
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(bus_id)
from busmaster
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Query='SELECT CONVERT(VARCHAR(20), Month_date,106 )as tourdate ,'+ @cols +'
from (
select t.Month_date,b.bus_id,b.tour_date,b.[status],b.fare
from #temp t
left outer join Busdetails b on t.Month_date=b.tour_date
) x
pivot
(
max(status)
for bus_id in (' + @cols + ')
) p
order by Month_date'
exec(@query)
drop table #temp
Regards
Baiju