Solved

% of duplicate recrods grouped per representative and vendor

Posted on 2009-07-15
6
221 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:acardullo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24860562
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
 

Author Comment

by:acardullo
ID: 24860775
I think you nailed it.  % are a bit whack though.  i have pctCallbacks as high as 2603
0
 

Author Comment

by:acardullo
ID: 24860786
Oh and i cant have any results where the vendor field is empty
0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
LVL 8

Accepted Solution

by:
Bobaran98 earned 500 total points
ID: 24861029
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
 

Author Comment

by:acardullo
ID: 24868425
Youve done it again, Bob!.  Your a credit to this board!
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24869331
:-)
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question