troubleshooting Question

for any repeated "contract_number" 1 row return where the "Qualification Percent" number is the highest.

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft SQL ServerSQL
2 Comments1 Solution66 ViewsLast Modified:
In relation to previous question.
https://www.experts-exchange.com/questions/29134757/need-a-select-case-statement-that-will-add-2-fields-after-evaluating-a-calculation-in-the-case-statement.html?headerLink=workspace_answered_questions


SELECT [contract_number]
      ,[From_Dollar]
      ,[To_Dollar]
      ,[Base_Discount_Percent]
      ,[Start_Date]
      ,[End_Date]
      ,[Sum_Of_Sales_Current]
      ,CASE	
        WHEN ISNULL(base_Discount_Percent,0) = '' THEN 'no data'
        WHEN ISNULL(Sum_Of_Sales_Current, 0) < From_Dollar THEN 'BELOW RANGE'
		WHEN ISNULL(Sum_Of_Sales_Current, 0) > To_Dollar THEN 'ABOVE RANGE'
			ELSE 'BETWEEN RANGE' 
		END AS RANGE_SCORE,
CASE	WHEN Base_Discount_Percent IS NULL THEN ''--'NONE STATED'
		WHEN End_Date <= CAST(GETDATE() AS DATE) THEN 'OUT OF DATE'
		WHEN ISNULL(Sum_Of_Sales_Current, 0) < From_Dollar THEN '0'
			ELSE Convert(nvarchar(50),Base_Discount_Percent)  
		END AS  QUALIFICATION_PERCENT     
  FROM [program].[dbo].[TIER_CAT]

Instead of returning more than 1 record for THE field(contract_number) that has more than 1 to return.
I need only to return 1 ROW.

current return:
repeated rows with a qualification percent and not out of date
desired return:
1 row with a qualification percent and not out of date
So i need: for any repeated "contract_number" and a  has no "out of date" language in "Qualification Percent"
1 row return where the "Qualification Percent" number is the highest.

Thanks
fordraiders
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros