% of duplicate recrods grouped per representative and vendor

Some helpfull soul has pointed me in the right direction with the code below to calcluate the % of records with a duplicate serial number in a given table.  Now i want to take this further and calcuolate the % of duplicate serial numbers per representative which is a field in the source view.
I want to end up with one result record per Vendor and then grouped by Representative with the % of duplicate serial #.
The reason i need this si to do some service KPI's to see how many times a representative has to go back to the same equipment to fix it in a 90 day period (which is already filltered using getdate in the view)

To complicate things further there needs to another tier in the sp that groups by Vendor # which is also a field in the view
the tiers need to be
Representative vendor # Duplicate %
The two new field names exact names are  REPRESENTATIVE  AND  VENDOR

Thank you for looking
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Anthony Cardullo
-- Create date: 07/14/09
-- Description:	<Description,,>
-- EXEC [dbo].[usp_CallBackPerSerialNum90Days]
--============================================
	ALTER PROCEDURE [dbo].[usp_CallBackPerSerialNum90Days]
        -- Add the parameters for the stored procedure here
        
AS
 
SET NOCOUNT ON
 
 
/* declare all variables */
DECLARE @numTotal int
DECLARE @numUnique int
DECLARE @numUnqDupes int
DECLARE @numDupes int
DECLARE @numSingles int
DECLARE @pctDupeEntries float
DECLARE @pctUnqDupes float
DECLARE @pctExtra float
 
 
/* get counts from database */
 
SET @numTotal = (SELECT COUNT(*) FROM ServiceCalls90DaysNoVendor)
 
SET @numUnique = (SELECT COUNT(DISTINCT SERIAL) FROM ServiceCalls90DaysNoVendor)
 
SET @numUnqDupes = (SELECT COUNT(*)
        FROM (SELECT SERIAL, COUNT(SERIAL) as NumSERIAL FROM ServiceCalls90DaysNoVendor GROUP BY SERIAL) TT
        WHERE TT.NumSERIAL > 1
  )
 
SET @numDupes = (SELECT COUNT(*) FROM ServiceCalls90DaysNoVendor WHERE SERIAL IN (
        SELECT SERIAL
        FROM (SELECT SERIAL, COUNT(SERIAL) as NumSERIAL FROM ServiceCalls90DaysNoVendor GROUP BY SERIAL) TT
        WHERE TT.NumSERIAL > 1
    )
  )
SET @pctExtra = (SELECT (CAST((@numTotal-@numUnique) as float)/CAST(@numTotal as float)))
 
 
SET @numSingles = (SELECT (@numTotal - @numDupes))
/* OR */
--SET @numSingles = (SELECT (@numUnique - @numUnqDupes))
/* OR */
--SET @numSingles = (SELECT COUNT(*) FROM
--      (SELECT SERIAL, COUNT(SERIAL) as NumSERIAL FROM ServiceCalls90DaysNoVendor GROUP BY SERIAL) TT
--      WHERE TT.NumSERIAL = 1
--  )
 
 
/* determine percentage of database entries that are non-unique */
SET @pctDupeEntries = (SELECT (CAST(@numDupes as float)/CAST(@numTotal as float)))
 
/* determine percentage of unique serial numbers that exist more than once in database */
SET @pctUnqDupes = (SELECT (CAST(@numUnqDupes as float)/CAST(@numUnique as float)))
 
 
/* choose the percentage you want, then select as your result */
SELECT CAST(@pctDupeEntries*100 as numeric(5,2)) as CallBackPerc
--SELECT @pctDupeEntries as PercentDupes
--SELECT @pctUnqDupes as PercentDupes

Open in new window

acardulloSystems Administrator. DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bobaran98Commented:
Hello again!

Unless there are errors, I think the code below should do exactly what you're wanting.  Note that the percentage I'm using doesn't exactly match the percentages we talked about before (the ones calculated in the codebox above).  Rather I'm calculating percentage of callbacks as the percentage of unique serial numbers for which the serial number exists more than once-- in other words, the percentage of calls that got at least one call back (numUnqDupes divided by numUnique).

Make sense?  Let me know if this runs!

SELECT
 
  TTT.REPRESENTATIVE, TTT.VENDOR,
  CAST(TTT.numUnqDupes as float)/CAST(TTT.numUnique as float) as pctCallbacks
 
FROM (
 
	SELECT DISTINCT
	   
	   R.REPRESENTATIVE,
	   R.VENDOR,	   
 
	   (SELECT COUNT(DISTINCT SERIAL) FROM ServiceCalls90Days WHERE REPRESENTATIVE=R.REPRESENTATIVE AND VENDOR=R.VENDOR) as numUnique,
 
	   (SELECT COUNT(*)
		FROM (SELECT SERIAL, COUNT(SERIAL) as NumSERIAL FROM ServiceCalls90Days GROUP BY SERIAL) TT
		WHERE TT.NumSERIAL > 1 AND REPRESENTATIVE=R.REPRESENTATIVE AND VENDOR=R.VENDOR
	   ) as numUnqDupes
 
	FROM ServiceCalls90Days R
 
) TTT
 
ORDER BY
 
   TTT.REPRESENTATIVE ASC, TTT.VENDOR ASC

Open in new window

0
acardulloSystems Administrator. DBAAuthor Commented:
I think you nailed it.  % are a bit whack though.  i have pctCallbacks as high as 2603
0
acardulloSystems Administrator. DBAAuthor Commented:
Oh and i cant have any results where the vendor field is empty
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Bobaran98Commented:
Yeah, 2603 doesn't sound quite right, I agree. :-)  I misplaced one of my WHERE clauses above... try the below and see if it works.  I've also added in the non-empty vendor field check too.
SELECT
 
  TTT.REPRESENTATIVE, TTT.VENDOR,
  CAST(TTT.numUnqDupes as float)/CAST(TTT.numUnique as float) as pctCallbacks
 
FROM (
 
	SELECT DISTINCT
	   
	   R.REPRESENTATIVE,
	   R.VENDOR,	   
 
	   (SELECT COUNT(DISTINCT SERIAL) FROM ServiceCalls90Days WHERE REPRESENTATIVE=R.REPRESENTATIVE AND VENDOR=R.VENDOR) as numUnique,
 
	   (SELECT COUNT(*)
		FROM (SELECT SERIAL, COUNT(SERIAL) as NumSERIAL FROM ServiceCalls90Days WHERE REPRESENTATIVE=R.REPRESENTATIVE AND VENDOR=R.VENDOR GROUP BY SERIAL) TT
		WHERE TT.NumSERIAL > 1
	   ) as numUnqDupes
 
	FROM ServiceCalls90Days R
	WHERE ISNULL(R.VENDOR,'')<>''
 
) TTT
 
ORDER BY
 
   TTT.REPRESENTATIVE ASC, TTT.VENDOR ASC

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
acardulloSystems Administrator. DBAAuthor Commented:
Youve done it again, Bob!.  Your a credit to this board!
0
Bobaran98Commented:
:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.