April 24, 2014 at 12:42 pm
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 #bus_master
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 #bus_master.how it is possible
April 24, 2014 at 1:48 pm
You need to include it in your subquery when creating the pivot. Something like this:
from (
select t.Month_date,b.tour_date,m.bus_id,b.[status]
from #bus_master m
LEFT
JOIN #Busdetails b ON m.bus_id = b.bus_id
left outer join #temp t on t.Month_date=b.tour_date
) x
pivot...
However, I wen't a little further to create the code needed to populate the query variable in a single step without a temp table.
declare @dte as datetime ='2013-10-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);
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) --This will get a max of 36 rows (enough for a full month)
)
SELECT @Query = 'SELECT m.bus_id ' + CHAR(13) --group columns
+ (SELECT CHAR(9) + ',MAX( CASE WHEN tour_date = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT(VARCHAR(20), Month_date,106)) + CHAR(13)
FROM Dates
WHERE Month_date BETWEEN @StDt AND @EnDt
ORDER BY Month_date
FOR XML PATH(''),TYPE).value('.','varchar(max)') --columns
+ '
FROM #bus_master m
LEFT
JOIN #Busdetails b ON m.bus_id = b.bus_id
GROUP BY m.bus_id' --source tables
PRINT @Query
EXEC sp_executesql @Query
You might note that it's not using pivot but cross tabs (Reference[/url]). And I'm building the dates assuming that you'll always have one month, but you can increase the range if needed.
Feel free to ask any questions that you have.
April 25, 2014 at 4:14 am
Hi,friend your second code is working fine.
i need one suggestion.
I want to display date in column header as 01,02,..31 instead of 01 oct 2013,02-oct 2013....31 oct 2013
i tried with datename function .but not working.
what to do for solving that.
Regards
baiju
April 25, 2014 at 5:09 am
Hi friend iam very eager to your reply.
April 25, 2014 at 8:15 am
klbaiju 94581 (4/25/2014)
Hi friend iam very eager to your reply.
You need to understand that people are in different timezones. It was 4am when you posted your reply.
To get the day number as a column header, you could use CONVERT with a different format code and a smaller length.
CONVERT( char(2), Month_date, 103)
Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx
April 25, 2014 at 8:46 am
Thank you very much .now it is complete
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply