i have created temporary table and change the code
Hi All,
following is my working code.i have created temporary tables anybody can run this code
declare @dte as datetime ='2013-10-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
create table #bus_master(bus_id int,bus_name varchar(50))
insert into #bus_master values(100,'A')
insert into #bus_master values(101,'B')
insert into #bus_master values(102,'C')
insert into #bus_master values(103,'D')
insert into #bus_master values(104,'E')
insert into #bus_master values(105,'F')
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')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),@cols1 as nvarchar(max)
--select @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(bus_id)
-- from baiju.dbo.busmaster
-- FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)')
-- ,1,1,'')
--CONVERT(VARCHAR(20), Month_date,106 )
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(VARCHAR(20), Month_date,106 ))
from #temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Query='SELECT bus_id, '+ @cols +'
from (
select t.Month_date,b.tour_date,b.bus_id,b.[status]
from #Busdetails b
left outer join #temp t on t.Month_date=b.tour_date
) x
pivot
(
max(status)
for tour_date in (' + @cols + ')
) p
order by Month_date'
exec(@query)
drop table #temp
drop table #bus_master
drop table #busdetails
iam getting output as
bus_id 01 oct 2013 02 oct 2013 ......................... 31 oct 2013
102 booked NULL ... ................................. NULL
103 booked NULL .........................................NULL
100 NULL booked ..................................... NULL
here bus_id is coming from busdetails that's why itshowing only 3 record in above code.bus_id should come from #busmaster
my requirement should be
bus_id 01 oct 2013 02 oct 2013 ......................... 31 oct 2013
100 NULL booked ............................... NULL
101 NULL NULL ........................................ NULL
102 booked NULL ......................................... NULL
103 booked NULL ...................................... NULL
104 NULL NULL..................................... NULL
105 NULL NULL....................................... NULL
this is for booking site,it's showing the booking of a month.bus_id should come from #busmaster.how it is possible
Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator