none
Getting multiple values from the condition RRS feed

  • Question

  • Hi

    I've 4 tables - Customer,Territory,TerritoryAccount, and call

    I am SELECT T.State,T.Territory,C.CustomerName,call.CallType,CASE WHEN call.Datestart is not null then 1 else 0 end as called,

    CASE WHEN call.datestart is null then 1 else 0 end as notcalled

    FROM customer c

    left join call on c.ID = call.CustID

    join TerritoryAccount TA ON TA.AccountID = c.CustID

    join Territory T ON T.ID = TA.TerritoryID

    WHERE T.Territory IN (@Territory) and call.DateStart LIKE '@Datestart'

    For one customer i.e. AAA we have two calltypes - Sales call and Telephone call.

    But in result I am getting only one record i.e. sales call.

    What should I do in order to get all types of calls done by the customer.

    Please help in achieving this.

    Thanks in advance

    Wednesday, August 8, 2012 10:42 AM

Answers

  • Where are you providing date range condition here - you are checking only LIKE condition on date.

    What is the value passed to @DateStart variable.

    Thanks!

    • Marked as answer by SQL2012BI Monday, August 13, 2012 8:34 AM
    Wednesday, August 8, 2012 11:52 AM

All replies

  • Try rewrite 

    WHERE T.Territory IN (@Territory) and call.DateStart LIKE '@Datestart'

    to

    AND T.Territory IN (@Territory) and call.DateStart LIKE @Datestart

    BTW, sql server does not know 'array' so i mean you have only single value in @Territory variable, otherwise

    read http://www.sommarskog.se/arrays-in-sql.html


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


    Wednesday, August 8, 2012 10:44 AM
    Answerer
  • HI Noor !

    You may get the desired output using below query;

    SELECT    T.State,T.Territory,C.CustomerName,call.CallType,CASE WHEN call.Datestart is not null then 1 else 0 end as called
            , CASE WHEN call.datestart is null then 1 else 0 end as notcalled
    FROM customer c
    left join call on c.ID = call.CustID
    left join TerritoryAccount TA ON TA.AccountID = c.CustID
    left join Territory T ON T.ID = TA.TerritoryID
    WHERE T.Territory IN (@Territory) and call.DateStart = @Datestart



    Note : I have changed all joins to left join

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham Niaz
    Wednesday, August 8, 2012 10:47 AM
    Answerer
  • Hi Uri Dimant,

    Thanks for the reply.

    The reason why I am using IN operator is am using this query in SSRS dataset and Territory is a multi select. so depends on selection it takes the values and executes.

    My question is that in my call table there are 2 rows which are satisfying for one customer which I have in customer table.

    Now I want to see these two records for this customer in my result set.

    Issue is I am seeing only one row. What should i do to get two.

    Wednesday, August 8, 2012 10:49 AM
  • Though you are using LEFT JOIN on Call table, the records from Call table may be filtered if condition Call.DateStart LIKE '@DateStart' fails (as it is in WHERE clause).

    Check if this condition is failing for the second call type for the respective customer.

    Thanks!

    Wednesday, August 8, 2012 11:13 AM
  • Hi Deepak,

    As said Customer - AAA have made two calls one is on say 2nd Jan  and the other is on 3rd Jan.

    Now, When I want to know the call details whether customer AAA have called or not with date between 1st and 3rd Jan then I should see 2 records against the customer AAA.

    so in order to achieve it i have written above query but its returning only one record.

    Hope I am clear here.

    Thanks.

    Wednesday, August 8, 2012 11:18 AM
  • Where are you providing date range condition here - you are checking only LIKE condition on date.

    What is the value passed to @DateStart variable.

    Thanks!

    • Marked as answer by SQL2012BI Monday, August 13, 2012 8:34 AM
    Wednesday, August 8, 2012 11:52 AM