Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
klbaiju
Starting Member
4 Posts |
Posted - 2014-04-24 : 14:38:34
|
Hi All,following is my working code.i have created temporary tables anybody can run this codedeclare @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 = 0Create table #temp (Month_date datetime)while @inc < @cntbegininsert into #tempselect DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))set @inc = @inc + 1endcreate 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 #tempFOR 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 bleft outer join #temp t on t.Month_date=b.tour_date) xpivot(max(status)for tour_date in (' + @cols + ')) porder by Month_date'exec(@query)drop table #tempdrop table #bus_masterdrop table #busdetailsiam getting output asbus_id 01 oct 2013 02 oct 2013 ......................... 31 oct 2013102 booked NULL ... ................................. NULL103 booked NULL .........................................NULL100 NULL booked ..................................... NULLhere bus_id is coming from busdetails that's why itshowing only 3 record in above code.bus_id should come from #bus_mastermy requirement should bebus_id 01 oct 2013 02 oct 2013 ......................... 31 oct 2013100 NULL booked ............................... NULL101 NULL NULL ........................................ NULL102 booked NULL ......................................... NULL103 booked NULL ...................................... NULL104 NULL NULL..................................... NULL 105 NULL NULL....................................... NULLthis is for booking site,it's showing the booking of a month.bus_id should come from #bus_master.how it is possible |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-04-24 : 14:51:14
|
change your inner SELECT to:select t.Month_date,b.tour_date,bm.bus_id,b.[status]from #bus_master bmleft outer join #Busdetails b on b.bus_id = bm.bus_idleft outer join #temp t on t.Month_date=b.tour_date Be One with the OptimizerTG |
|
|
|
|
|
|
|