Restricting the result set with one record?

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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