June 6, 2007 at 10:49 am
I need to get a set of results that I am eventually going to calculate medians on. The basic query is this:
SELECT op_m.mile_code,
DATEDIFF(dd, op_m.first_open_date, GETDATE()) AS DeltaT,
op_w.rep,
op_m.close_date
FROM op_milestones op_m JOIN op_opportunity op_o ON op_m.opid = op_o.opid
JOIN op_owner op_w ON op_o.opid = op_w.opid
WHERE op_m.mile_status = 'Completed'
AND op_o.type <> 'Contract Renewal'
AND op_o.type <> 'Express Order'
AND op_w.role = 'Sales'
GROUP BY op_m.mile_code,
DATEDIFF(dd, op_m.first_open_date, GETDATE()),
op_w.rep,
op_m.close_date
ORDER BY op_w.rep, op_m.mile_code, op_m.close_date DESC
What I want to end up with is the 30 most recent completed records for each rep for each mile_code. So as an example, if there are 10 reps and 5 mile_codes then I should end up with 150 records for each rep (30 for each of the 5 mile_codes) and a total of 1500 records.
Here is the beginning of what the results should look like:
mile_code DeltaT rep close_date
Approach 1 Coleman 6/5/2007 16:52
Approach 1 COLEMAN 6/5/2007 14:32
Approach 1 COLEMAN 6/5/2007 9:26
Approach 21 COLEMAN 5/31/2007 14:07
Approach 14 COLEMAN 5/31/2007 9:53
Approach 7 Coleman 5/30/2007 16:12
Approach 36 COLEMAN 5/29/2007 15:53
Approach 8 COLEMAN 5/29/2007 13:54
Approach 8 COLEMAN 5/29/2007 13:23
Approach 8 COLEMAN 5/29/2007 9:00
… (20 more records)
Interview 1 Coleman 6/5/2007 16:52
Interview 1 COLEMAN 6/5/2007 14:32
Interview 1 COLEMAN 6/5/2007 9:26
Interview 8 COLEMAN 5/29/2007 13:54
Interview 8 COLEMAN 5/29/2007 9:00
Interview 14 COLEMAN 5/25/2007 8:37
Interview 61 COLEMAN 5/24/2007 10:57
Interview 14 COLEMAN 5/23/2007 9:32
Interview 26 COLEMAN 5/18/2007 9:39
Interview 23 Coleman 5/14/2007 13:25
… (20 more records)
Followed by 30 each of the other 3 milecodes for Coleman
Approach 0 DOLAN 6/6/2007 9:27
Approach 1 DOLAN 6/5/2007 13:39
Approach 1 DOLAN 6/5/2007 12:20
Approach 1 DOLAN 6/5/2007 11:37
Approach 92 DOLAN 6/5/2007 11:12
Approach 91 DOLAN 6/5/2007 9:44
Approach 2 Dolan 6/4/2007 11:53
Approach 2 DOLAN 6/4/2007 11:11
Approach 5 Dolan 6/1/2007 16:07
Approach 177 DOLAN 6/1/2007 10:58
… (20 more records)
Interview 96 DOLAN 6/5/2007 16:38
Interview 167 DOLAN 6/5/2007 14:24
Interview 2 Dolan 6/4/2007 14:54
Interview 2 DOLAN 6/4/2007 11:11
Interview 5 Dolan 6/1/2007 16:07
Interview 27 DOLAN 6/1/2007 14:48
Interview 323 DOLAN 6/1/2007 13:49
Interview 15 Dolan 6/1/2007 11:48
Interview 224 DOLAN 5/31/2007 16:40
Interview 126 Dolan 5/30/2007 15:02
… (20 more records)
…
I am still trying some different things but I keep running into problems. Any help would be great. Thanks.
June 7, 2007 at 2:00 am
I'm not sure why you're doing a group by without aggregates. Duplicate records?
Try something like this (2005 specific)
SELECT
mile_code, DeltaT, rep, close_date FROM (
SELECT op_m.mile_code,
DATEDIFF(dd, op_m.first_open_date, GETDATE()) AS DeltaT, op_w.rep, op_m.close_date,
ROW_NUMBER() OVER (PARTITION BY rep, mile_code ORDER bY close_date DESC) AS RowNo
FROM op_milestones op_m INNER JOIN op_opportunity op_o ON op_m.opid = op_o.opid
INNER JOIN op_owner op_w ON op_o.opid = op_w.opid
WHERE op_m.mile_status = 'Completed'
AND op_o.type <> 'Contract Renewal'
AND op_o.type <> 'Express Order'
AND op_w.role = 'Sales'
/*GROUP BY op_m.mile_code,
DATEDIFF(dd, op_m.first_open_date, GETDATE()),
op_w.rep,
op_m.close_date*/
) NumberdResults
WHERE RowNo<=30
ORDER BY op_w.rep, op_m.mile_code, op_m.close_date DESC
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2007 at 3:51 pm
Thanks Gail. I actually had changed to something like this late yesterday based on the advice of some other people as well. Pretty much came up with the same thing you are suggesting here. Havn't done a whole lot with the Partition before so I learned something new.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply