April 4, 2015 at 2:42 am
Message Preview
Hi following is a working code
declare @dte as datetime='2015-04-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);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')
insert into #bus_master values(101,'B','lekshmi')
insert into #bus_master values(102,'C','lekshmi')
insert into #bus_master values(103,'D','krishna')
insert into #bus_master values(104,'E','krishna')
insert into #bus_master values(105,'F','krishna')
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')
;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)
)
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
GROUP BY m.bus_id '
execute(@Query)
drop table #bus_master
drop table #busdetails
iam getting the ouput correctly
my requirement is i want to write a condition
here
JOIN busdetails b ON m.bus_id = b.bus_id
i want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
when i tried this code iam getting error
how to solve this
April 4, 2015 at 3:13 am
baiju krishnan (4/4/2015)
Message PreviewHi following is a working code
declare @dte as datetime='2015-04-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);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')
insert into #bus_master values(101,'B','lekshmi')
insert into #bus_master values(102,'C','lekshmi')
insert into #bus_master values(103,'D','krishna')
insert into #bus_master values(104,'E','krishna')
insert into #bus_master values(105,'F','krishna')
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')
;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)
)
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
GROUP BY m.bus_id '
execute(@Query)
drop table #bus_master
drop table #busdetails
iam getting the ouput correctly
my requirement is i want to write a condition
here
JOIN busdetails b ON m.bus_id = b.bus_id
i want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
when i tried this code iam getting error
how to solve this
It would help to know the error you're getting.
On a guess, you need to escape the single quotes. Try changing your new code to this:
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname=''lekshmi''
That's two single quotes on either side. You have to do this because the original is inside of single quotes already.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2015 at 3:36 am
code working but it doesn't filtering the condition
April 4, 2015 at 4:05 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply