SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Customery Survey Query with no Duplicates


Customery Survey Query with no Duplicates

Author
Message
Dat120Y
Dat120Y
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18327 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
T_Dot_Geek
T_Dot_Geek
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 99
Please follow below article helps you how to provide more clarity for your post
http://www.sqlservercentral.com/articles/Best+Practices/61537/
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18327 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2896 Visits: 3318
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


Dat120Y
Dat120Y
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 14
Done the job nicely thanks.

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

Cheers
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search