Answered by:
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.
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- Proposed as answer by Janos BerkeMVP Tuesday, August 14, 2012 12:35 PM
- Marked as answer by amber zhangModerator Monday, August 20, 2012 2:00 AM
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
-
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
-
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
-
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- Proposed as answer by Janos BerkeMVP Tuesday, August 14, 2012 12:35 PM
- Marked as answer by amber zhangModerator Monday, August 20, 2012 2:00 AM