Solved

% of duplicate recrods grouped per representative and vendor

Posted on 2009-07-15
6
216 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
Comment Utility
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
Comment Utility
I think you nailed it.  % are a bit whack though.  i have pctCallbacks as high as 2603
0
 

Author Comment

by:acardullo
Comment Utility
Oh and i cant have any results where the vendor field is empty
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Accepted Solution

by:
Bobaran98 earned 500 total points
Comment Utility
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
Comment Utility
Youve done it again, Bob!.  Your a credit to this board!
0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
:-)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

11 Experts available now in Live!

Get 1:1 Help Now