none
Taking only one record? RRS feed

  • Question

  • Hi

    I've written below query...

    SELECT DateStart, DateEnd,

    State,

    Group, Territory, Customer, Channel,Cluster,Banner,Grade,CallType,

    SUM(CASE WHEN DateStart IS NOT NULL THEN 1 ELSE 0 END) AS Called,

    SUM(CASE WHEN DateStart IS NULL THEN 1 ELSE 0 END) AS Not Called

    FROM Customer

    LeftJoin Card ON Customer.ID = Card.CustomerID

    -----

    ----

    WHERE DateStart = @Start and DateEnd = @End

    Group BY DateStart,DateEnd,State,Group,Territory,Customer,Channel,Cluster,Banner,Grade,CallType

    ORDER BY DateStart,DateEnd,State,Group,Territory,Customer,Channel,Cluster,Banner,Grade,CallType

    In Result Set, am getting result as below...

    DateStart   DateEnd          State    Group      Territory      Customer    Channel   Cluste    Banner   Grade   CallType

    29/7/2012   30/7/2012     AAA        A              AZX                XXX            122         544         121      4232      533

    30/7/2012   31/7/2012     AAA        A              AZX                XXX            122         544         121      4232      533

    I am using this as a dataset in SSRS report and using drill down approach to see the result.

    The issue is I want to see customer once. As in the above result we are getting the same customer twice. My aim for the report is to see whether the customer called or not. I do not want how many times customer called.

    How can I display the customer XXX only once. that means irrespective of how many times i get the customer but i have display only once.

    Please suggest. Thanks in advance.

    Monday, August 6, 2012 6:32 AM

Answers

  • As you are using start date and end date in GROUP BY clause it is obvious the query returned 2 rows.

    Remove the start date, end date from GROUP BY clause and use it like below.
    SELECT Min(DateStart), Max(DateEnd),
    
    State,
    
    Group, Territory, Customer, Channel,Cluster,Banner,Grade,CallType,
    
    SUM(CASE WHEN DateStart IS NOT NULL THEN 1 ELSE 0 END) AS Called,
    
    SUM(CASE WHEN DateStart IS NULL THEN 1 ELSE 0 END) AS Not Called
    
    FROM Customer
    
    LeftJoin Card ON Customer.ID = Card.CustomerID
    
    -----
    
    ----
    
    WHERE DateStart = @Start and DateEnd = @End
    
    Group BY State,Group,Territory,Customer,Channel,Cluster,Banner,Grade,CallType
    
    ORDER BY State,Group,Territory,Customer,Channel,Cluster,Banner,Grade,CallType


    Best Regards, Venkat

    • Proposed as answer by Naomi NModerator Monday, August 6, 2012 7:31 AM
    • Marked as answer by SQL2012BI Monday, August 6, 2012 8:51 AM
    Monday, August 6, 2012 7:00 AM

All replies

  • Hi,

    Just drag a customer to row group and for fields which are same for customer use MIN or MAX..


    - Chintak (My Blog)

    Monday, August 6, 2012 6:35 AM
  • Hi Chintak,

    I've Customer in Row Groups. now how to take maximum of it. is it by writting MAX(Customer) in the expression?

    Thanks.

    Monday, August 6, 2012 6:45 AM
  • As you are using start date and end date in GROUP BY clause it is obvious the query returned 2 rows.

    Remove the start date, end date from GROUP BY clause and use it like below.
    SELECT Min(DateStart), Max(DateEnd),
    
    State,
    
    Group, Territory, Customer, Channel,Cluster,Banner,Grade,CallType,
    
    SUM(CASE WHEN DateStart IS NOT NULL THEN 1 ELSE 0 END) AS Called,
    
    SUM(CASE WHEN DateStart IS NULL THEN 1 ELSE 0 END) AS Not Called
    
    FROM Customer
    
    LeftJoin Card ON Customer.ID = Card.CustomerID
    
    -----
    
    ----
    
    WHERE DateStart = @Start and DateEnd = @End
    
    Group BY State,Group,Territory,Customer,Channel,Cluster,Banner,Grade,CallType
    
    ORDER BY State,Group,Territory,Customer,Channel,Cluster,Banner,Grade,CallType


    Best Regards, Venkat

    • Proposed as answer by Naomi NModerator Monday, August 6, 2012 7:31 AM
    • Marked as answer by SQL2012BI Monday, August 6, 2012 8:51 AM
    Monday, August 6, 2012 7:00 AM
  • Thanks Venkat. i've got what i want.
    Monday, August 6, 2012 7:21 AM
  • We are glad that the forum, and in particular Chintak and Venkats could be of help to you.

    Could you please mark the reply that answered your question as anser? Otherwise your question will show up as unanswered, you see.

    Thank you

    Monday, August 6, 2012 7:25 AM