Solved

% of duplicate recrods grouped per representative and vendor

Posted on 2009-07-15
6
217 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now