October 31, 2012 at 5:32 pm
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
October 31, 2012 at 7:11 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 31, 2012 at 10:50 pm
Please follow below article helps you how to provide more clarity for your post
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 31, 2012 at 10:57 pm
T_Dot_Geek (10/31/2012)
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/quote%5D
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 1, 2012 at 7:47 pm
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
November 1, 2012 at 9:27 pm
Done the job nicely thanks.
Have noted the feedback around presenting a query and will incorporate into future posts.
Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply