Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Customery Survey Query with no Duplicates Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 5:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 25, 2012 5:53 PM
Points: 2, Visits: 14
Hi,

I'm trying to build a customer survey query of 50 random jobs where no customer (UserID) appears more than once. I've played around with DISTINCT and GROUP BY but either get an error or still get duplicates. What can I do to achieve this?

SELECT TOP 50

jobDB.num AS JobNumber,
DateAdd(minute, 570, jobDB.entered) AS CreatedDate,
DateAdd(minute, 570, jobDB.closed) AS ClosedDate,
person.uniqueId AS UserID,
NEWID() AS Random

FROM jobDB
LEFT OUTER JOIN person ON jobDB.contactId=person.personID

WHERE jobDB.status=N'Closed'
AND DateAdd(minute, 570, jobDB.entered) >= DATEADD(d, -28, GETDATE())
AND DateAdd(minute, 570, jobDB.closed) >= DATEADD(d, -7, GETDATE())
AND person.uniqueId IS NOT NULL

ORDER BY Random

Rgds
Post #1379568
Posted Wednesday, October 31, 2012 7:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 3,609, Visits: 5,219
Take a look at using ROW_NUMBER() in MS BOL.

If you can't figure it out post some DDL and sample data (consumable form) where your query would return duplicates. Identify which duplicates you'd like removed.

With that someone can provide you a fully tested solution.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1379577
Posted Wednesday, October 31, 2012 10:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 3:55 PM
Points: 54, Visits: 83
Please follow below article helps you how to provide more clarity for your post
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1379613
Posted Wednesday, October 31, 2012 10:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 3,609, Visits: 5,219
T_Dot_Geek (10/31/2012)
Please follow below article helps you how to provide more clarity for your post
http://www.sqlservercentral.com/articles/Best+Practices/61537/


We're asking the OP to make it easier for us so let's make it easy for him by providing a clickable link, shall we?

http://www.sqlservercentral.com/articles/Best+Practices/61537/



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1379615
Posted Thursday, November 1, 2012 7:47 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:37 PM
Points: 947, Visits: 2,869
Without something to test this against and using Dwain's tip on the row_number, the following may do what you want
;WITH NewIDAllRecords AS (
SELECT
jobDB.num AS JobNumber,
DateAdd(minute, 570, jobDB.entered) AS CreatedDate,
DateAdd(minute, 570, jobDB.closed) AS ClosedDate,
person.uniqueId AS UserID,
NEWID() AS Random

FROM jobDB
LEFT OUTER JOIN person ON jobDB.contactId=person.personID

WHERE jobDB.status=N'Closed'
AND DateAdd(minute, 570, jobDB.entered) >= DATEADD(d, -28, GETDATE())
AND DateAdd(minute, 570, jobDB.closed) >= DATEADD(d, -7, GETDATE())
AND person.uniqueId IS NOT NULL
),
SequenceAllUserRecords AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Random) AS Seq,
JobNumber, CreatedDate, ClosedDate, UserID, Random
FROM NewIDAllRecords
)
SELECT TOP 50
JobNumber, CreatedDate, ClosedDate, UserID
FROM SequenceAllUserRecords
WHERE Seq = 1
ORDER BY Random

Post #1380107
Posted Thursday, November 1, 2012 9:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 25, 2012 5:53 PM
Points: 2, Visits: 14
Done the job nicely thanks.

Have noted the feedback around presenting a query and will incorporate into future posts.

Cheers
Post #1380127
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse