% 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

Select allOpen in new window