where clause


  • I cannot get this query to work - can you see what I am doing wrong
















       ON SV00300.Service_Call_ID = WS30702.WS_Job_Number

      INNER JOIN DAM_TechEE_Info

        ON WS30702.EMPLOYID = DAM_TechEE_Info.EMPLOYID

     WHERE SV00300.DATE1 Between (@Start_Date) and (@End_Date)



                    SV00300.CUSTNMBR in (@CUSTNMBR)

                    or SV00300.LOCATNNM in (@LOCATNNM)

                    or SV00300.Contract_Number in (@Contract_Number)



    Monday, August 26, 2013 11:55 PM


  • You posted little useful information, so there is nothing to do but guess randomly.  I suggest you first start by telling us what "doesn't work" means.  Given the variables in use, you might first try to develop, test, and refine your query using literal values that produce a known resultset; that would allow you to concentrate on the logic of the query itself and avoid any issues with variables, their usage, and report definitions.

    In addition, I can't say that your query seems to make much sense logically. You have a full outer join between SV00300 and WS20702 (nice table names, btw). You then inner join that resultset to DAM_TechEE_Info while your where clause refers to SV00300.  With that logic all your outer joins are effectively turned into inner joins.  To help illustrate that, see below - which is something you should be capable of doing yourself btw.  Note the resultset of the last query that involves the full outer join.   

    set nocount on;
    declare @SV00300 table (svid int, date1 date, sv_other varchar(10)); 
    declare @WS30702 table (wsjobnum int, empid int, wsother varchar(10)); 
    declare @DAM_TechEE_Info table (empid int, eeother varchar(10)); 
    declare @Start_Date date, @End_Date date;
    set @Start_Date = '20130701'; 
    set @End_Date = '20130715'; 
    insert @SV00300 (svid, date1, sv_other) values (1, '20130702', 'aaa'), (2,'20130704', 'bbb');
    insert @WS30702 (wsjobnum, empid, wsother) values (1, 5, '2212'), (3, 7, '2211'); 
    insert @DAM_TechEE_Info (empid, eeother) values (5, 'brah'), (7, 'dude');
    select * 
    from @SV00300 as sv full outer join @WS30702 as ws on sv.svid = ws.wsjobnum
    inner join @DAM_TechEE_Info as dte on ws.empid = dte.empid 
    where sv.date1 Between (@Start_Date) and (@End_Date);
    select * 
    from @SV00300 as sv full outer join @WS30702 as ws on sv.svid = ws.wsjobnum;

    Tuesday, August 27, 2013 1:58 PM