Error in writing condition in a query

Question

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

Answer 1

Could you try below code?

If it doesn't work, please post the error messages that you get.

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 and m.uname=''lekshmi''  
    GROUP BY m.bus_id  '
execute(@Query)
drop table #bus_master
drop table #busdetailsc

---

Cheers,
Vaibhav Chaudhari
[MCTS], [MCP]

Answer 2

Hi vaibhav thanks for your code. it is working but not filtering the condition

it is displaying all 6 records.

i need to display only records with uname=lekshmi

Answer 3

Please execute below to see if it gives you expected result

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 where m.uname=''lekshmi''  
    GROUP BY m.bus_id  '
execute(@Query)
--select @query
drop table #bus_master
drop table #busdetails

---

Cheers,
Vaibhav Chaudhari
[MCTS], [MCP]

Answer 4

Simple change AND to WHERE here

 JOIN #busdetails b ON m.bus_id = b.bus_id   WHERE m.uname=''lekshmi''

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence