none
Restricting the result set with one record? RRS feed

  • Question

  • I've joined 3 tables Territory,Customer and CallType.

    I want to display all the records of customer with two calculated columns - called and Not called.

    If customer have atleast one calltype then Called otherwise not called.

    Now in my result set am getting more than one row for one customer but i want only one row to consider.

    How to restrict my result set to one calltype.

    Please help.

    Monday, August 13, 2012 9:15 AM

Answers

  • You can put your current query into CTE and add ROW_NUMBER() over (partition by Customer order by CallType) as Rn

    and select * from cte where Rn = 1 to select only one row per customer.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 14, 2012 12:31 PM
    Moderator

All replies

  • If you are joining, there could be more than one matching rows and hence you would be getting multiple rows. There are many ways to restrict the no. of rows returned. However, we need some sample data and the requirement to help you more.

    Murali Krishnan


    • Edited by Murali_CHN Monday, August 13, 2012 9:18 AM
    Monday, August 13, 2012 9:18 AM
  • May be this : Do a left outer JOIN with calltype table and add a CASE expression to the SELECT 

    SELECT CASE WHEN CT.CustomerID IS NULL then 'NOT Called' ELSE 'Called' END 

    FROM Customer C LEFT OUTER JOIN CallType CT ON C.CustomerID=CT.CustomerID


    Thanks and regards, Rishabh K

    Monday, August 13, 2012 9:28 AM
  • Hi

    Below is the query which i used...

    SELECT [NATIONAL],[State],[Group],Territory,Customer,Channel,Cluster,Banner,Grade,CallType,Called

    --,NotCalled

    FROM (

    SELECT DISTINCT

    'NATIONAL' AS [NATIONAL],

    SUBSTRING(T.TerritoryName, 1, CHARINDEX('0', SUBSTRING(T.TerritoryName, 1, 3) + '0', 1) - 1) AS [State],

    RG.[Description] AS [Group],

    T.TerritoryName AS Territory,

    A.AccountName AS Customer,

    ListValue_5.ListShortDesc AS Channel,

    ListValue_4.ListShortDesc AS Cluster,

    APG.PrimaryGroupName AS Banner,

    ListValue_3.ListShortDesc AS Grade,

    ISNULL(CallList.ListShortDesc,'No Call') AS CallType,

    CASE WHEN CC.DateStart IS NOT NULL THEN 1 ELSE 0 END AS Called

    --,CASE WHEN CC.DateStart IS NULL THEN 1 ELSE 0 END AS NotCalled

    FROM Account A  (NOLOCK) 

    LEFT JOIN CallCard CC (NOLOCK) ON A.ID = CC.Account_ID AND (CC.DateStart >= @From and CC.DateEnd <= @To+1)

    LEFT JOIN (SELECT Ln.ListCode,Lv.ListLookupID, Lv.ListShortDesc

               FROM ListValue Lv WITH (NOLOCK)

                INNER JOIN ListName Ln WITH (NOLOCK) ON Ln.Id = Lv.Listname_id

                WHERE Ln.Listcode = 'CallType') AS CallList ON CC.CallTypeID = CallList.ListLookupID

    LEFT JOIN ListValue AS ListValue_3 ON A.GradeID = ListValue_3.ListLookupID

    LEFT JOIN AccountPrimaryGroup APG (NOLOCK) ON A.PrimaryGroup_ID = APG.ID

    LEFT JOIN ListValue AS ListValue_4 ON A.ChannelID = ListValue_4.ListLookupID

    LEFT JOIN ListValue AS ListValue_5 ON A.AccountTypeID = ListValue_5.ListLookupID

    INNER JOIN TerritoryAccount AS TA (NOLOCK) ON TA.Account_ID = A.ID

    INNER JOIN Territory AS T (NOLOCK) ON T.ID = TA.Territory_ID

    INNER JOIN RepTerritory RT (NOLOCK) ON RT.Territory_ID = T.ID

    INNER JOIN Rep R (NOLOCK) ON R.ID = RT.Rep_ID

    INNER JOIN RepGroupDetail RGD (NOLOCK) ON RGD.ManagerRep_ID = R.ID

    INNER JOIN RepGroup RG (NOLOCK) ON RG.ID = RGD.RepGroup_ID

    WHERE (ListValue_3.ListName_ID = 8) AND (ListValue_4.ListName_ID = 7) AND (ListValue_5.ListName_ID = 4)

    AND (SUBSTRING(T.TerritoryName, 1, CHARINDEX('0', SUBSTRING(T.TerritoryName, 1, 3) + '0', 1) - 1) LIKE (@State))

    AND (T.TerritoryName IN (@Territory))

    AND ((CallList.ListShortDesc IN (@CallType)) OR (CallList.ListShortDesc IS NULL))

    --AND (ListValue_3.ListShortDesc IN (@Grade))

    --AND (ListValue_5.ListShortDesc IN (@Channel))

    --AND (ListValue_4.ListShortDesc IN (@Cluster))

    --AND (APG.PrimaryGroupName IN (@Banner))

    UNION

    SELECT DISTINCT

    'NATIONAL' AS [NATIONAL],

    SUBSTRING(T.TerritoryName, 1, CHARINDEX('0', SUBSTRING(T.TerritoryName, 1, 3) + '0', 1) - 1) AS [State],

    RG.[Description] AS [Group],

    T.TerritoryName AS Territory,

    A.AccountName AS Customer,

    ListValue_5.ListShortDesc AS Channel,

    ListValue_4.ListShortDesc AS Cluster,

    APG.PrimaryGroupName AS Banner,

    ListValue_3.ListShortDesc AS Grade,

    ISNULL(CallList.ListShortDesc,'No Call') AS CallType,

    0 AS Called

    --,1 AS NotCalled

    FROM Account A  (NOLOCK) 

    LEFT JOIN CallCard CC (NOLOCK) ON A.ID = CC.Account_ID AND (CC.DateStart >= @From and CC.DateEnd <= @To+1)

    LEFT JOIN (SELECT Ln.ListCode,Lv.ListLookupID, Lv.ListShortDesc

                FROM ListValue Lv WITH (NOLOCK)

                INNER JOIN ListName Ln WITH (NOLOCK) ON Ln.Id = Lv.Listname_id

                WHERE Ln.Listcode = 'CallType') AS CallList ON CC.CallTypeID = CallList.ListLookupID

    LEFT JOIN ListValue AS ListValue_3 ON A.GradeID = ListValue_3.ListLookupID

    LEFT JOIN AccountPrimaryGroup APG (NOLOCK) ON A.PrimaryGroup_ID = APG.ID

    LEFT JOIN ListValue AS ListValue_4 ON A.ChannelID = ListValue_4.ListLookupID

    LEFT JOIN ListValue AS ListValue_5 ON A.AccountTypeID = ListValue_5.ListLookupID

    INNER JOIN TerritoryAccount AS TA (NOLOCK) ON TA.Account_ID = A.ID

    INNER JOIN Territory AS T (NOLOCK) ON T.ID = TA.Territory_ID

    INNER JOIN RepTerritory RT (NOLOCK) ON RT.Territory_ID = T.ID

    INNER JOIN Rep R (NOLOCK) ON R.ID = RT.Rep_ID

    INNER JOIN RepGroupDetail RGD (NOLOCK) ON RGD.ManagerRep_ID = R.ID

    INNER JOIN RepGroup RG (NOLOCK) ON RG.ID = RGD.RepGroup_ID

    WHERE (ListValue_3.ListName_ID = 8) AND (ListValue_4.ListName_ID = 7) AND (ListValue_5.ListName_ID = 4)

    AND (SUBSTRING(T.TerritoryName, 1, CHARINDEX('0', SUBSTRING(T.TerritoryName, 1, 3) + '0', 1) - 1) LIKE (@State))

    AND (T.TerritoryName IN (@Territory))

    AND ((CallList.ListShortDesc NOT IN (@CallType)) )

    --AND (ListValue_3.ListShortDesc IN (@Grade))

    --AND (ListValue_5.ListShortDesc IN (@Channel))

    --AND (ListValue_4.ListShortDesc IN (@Cluster))

    --AND (APG.PrimaryGroupName IN (@Banner))

    )AA

    Actually my aim is to list all customers irrespective of the CallType and then display Called as 1 for the customers whose callType matched with the one selected by the user and Not called as 0 for the other customers whose Call Type is other than what user selected.

    Now, from the above query I am getting two records for one customer as this customer have more than one call Type. so I want to restrict it to consider only one.

    Result set:

    National    State    Group    Territory     Customer     Channel      cluster      Banner     Grade     Call type    Called

    US              WA       AAA+       WA           SAM            STORAGE     SU           SUPER      A            SalesCall     1

    US              WA       AAA+       WA           SAM            STORAGE     SU           SUPER      A            Telephone   1

    Monday, August 13, 2012 9:31 AM
  • You can put your current query into CTE and add ROW_NUMBER() over (partition by Customer order by CallType) as Rn

    and select * from cte where Rn = 1 to select only one row per customer.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 14, 2012 12:31 PM
    Moderator