Help with ranking records within years
-
Hi,
I need to create a report that shows that last time a person completed a course each year. I've figured out dense_rank() to the point where it will rank by date all the times a user completed a given course, but I can't rank within the year. If I could, then I'd just take the 1 rank in each year.
Here's the SQL:SELECT dense_rank() over
(
partitionby [user_id]
orderby [cplt_dt] desc
) AS Rank
,[cplt_dt] AS CompletionDate
,YEAR([cplt_dt]) AS YearCompleted
,[crse_cd] AS CourseCode
,[crse_nm] AS CourseName
,[user_id] AS UserID
,[last_nm] AS LastName
,[fst_nm] AS FirstName
FROM [SQL_Svr].[dbo].[Training_RCD_VW]
WHERE [crse_nm] LIKE '%records management%'AND [cplt_dt]>= '1/1/2009' AND [user_id]='XYZPDQ'
ORDER BY UserID, CompletionDate desc
This gives me:Rank CompletionDate YearCompleted CourseCode CourseName UserID LastName FirstName 1 9/19/17 0:00 2017 RMT2017 2017 Records Management Training XYZPDQ DOE JOHN 2 12/8/16 0:00 2016 RMRT201-541053 2016 Records Management Refreshe XYZPDQ DOE JOHN 2 12/8/16 0:00 2016 RMRT2016-541125 2016 Records Management Refresher Training XYZPDQ DOE JOHN 3 12/7/15 0:00 2015 SS_XXU_RM_2016 Records Management Refresher Training 2015 XYZPDQ DOE JOHN 4 10/7/14 0:00 2014 RMT2014-524750 2014 Records Management Training XYZPDQ DOE JOHN 5 12/5/13 0:00 2013 RMT2013-515645 2013 Records Management Training XYZPDQ DOE JOHN 6 10/22/12 0:00 2012 RMT2012-498251 2012 Records Management Training XYZPDQ DOE JOHN 7 12/6/11 0:00 2011 XX-YY-2012_REC-MGT-ANI 2011 Records Management Annual Refresher Training (Animated Version) XYZPDQ DOE JOHN 7 12/6/11 0:00 2011 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 8 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-TXT 2011 Records Management Annual Refresher Training (Text Only Version) XYZPDQ DOE JOHN 8 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-TXT 2011 Records Management Annual Refresher Training (Text Only Version) XYZPDQ DOE JOHN 8 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-ANI 2011 Records Management Annual Refresher Training (Animated Version) XYZPDQ DOE JOHN 8 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-ANI 2011 Records Management Annual Refresher Training (Animated Version) XYZPDQ DOE JOHN 8 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-TXT 2011 Records Management Annual Refresher Training (Text Only Version) XYZPDQ DOE JOHN 9 9/1/11 0:00 2011 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 10 8/12/11 0:00 2011 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 11 10/21/10 0:00 2010 WW-RM Records Management for All Employees XYZPDQ DOE JOHN 12 1/6/10 0:00 2010 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 13 9/18/09 0:00 2009 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 14 8/26/09 0:00 2009 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 15 8/20/09 0:00 2009 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN What I need is this:
Rank CompletionDate YearCompleted CourseCode CourseName UserID LastName FirstName 1 9/19/17 0:00 2017 RMT2017 2017 Records Management Training XYZPDQ DOE JOHN 1 12/8/16 0:00 2016 RMRT201-541053 2016 Records Management Refreshe XYZPDQ DOE JOHN 2 12/8/16 0:00 2016 RMRT2016-541125 2016 Records Management Refresher Training XYZPDQ DOE JOHN 1 12/7/15 0:00 2015 SS_XXU_RM_2016 Records Management Refresher Training 2015 XYZPDQ DOE JOHN 1 10/7/14 0:00 2014 RMT2014-524750 2014 Records Management Training XYZPDQ DOE JOHN 1 12/5/13 0:00 2013 RMT2013-515645 2013 Records Management Training XYZPDQ DOE JOHN 1 10/22/12 0:00 2012 RMT2012-498251 2012 Records Management Training XYZPDQ DOE JOHN 1 12/6/11 0:00 2011 XX-YY-2012_REC-MGT-ANI 2011 Records Management Annual Refresher Training (Animated Version) XYZPDQ DOE JOHN 1 12/6/11 0:00 2011 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 2 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-TXT 2011 Records Management Annual Refresher Training (Text Only Version) XYZPDQ DOE JOHN 2 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-TXT 2011 Records Management Annual Refresher Training (Text Only Version) XYZPDQ DOE JOHN 2 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-ANI 2011 Records Management Annual Refresher Training (Animated Version) XYZPDQ DOE JOHN 2 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-ANI 2011 Records Management Annual Refresher Training (Animated Version) XYZPDQ DOE JOHN 2 12/1/11 0:00 2011 XX-YY-2012_REC-MGT-TXT 2011 Records Management Annual Refresher Training (Text Only Version) XYZPDQ DOE JOHN 3 9/1/11 0:00 2011 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 4 8/12/11 0:00 2011 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 1 10/21/10 0:00 2010 WW-RM Records Management for All Employees XYZPDQ DOE JOHN 2 1/6/10 0:00 2010 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 1 9/18/09 0:00 2009 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 2 8/26/09 0:00 2009 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN 3 8/20/09 0:00 2009 XX_YY_RM_TRAININGV9 Overview of Federal Records Management XYZPDQ DOE JOHN How do I get there?
Thanks,
Erick -
SELECT dense_rank() over
(
partitionby [user_id], YEAR([cplt_dt])
orderby [cplt_dt] desc
) AS Rank
,[cplt_dt] AS CompletionDate
,YEAR([cplt_dt]) AS YearCompleted
,[crse_cd] AS CourseCode
,[crse_nm] AS CourseName
,[user_id] AS UserID
,[last_nm] AS LastName
,[fst_nm] AS FirstName
FROM [SQL_Svr].[dbo].[Training_RCD_VW]
WHERE [crse_nm] LIKE '%records management%'AND [cplt_dt]>= '1/1/2009' AND [user_id]='XYZPDQ'
ORDER BY UserID, CompletionDate desc
Try this.
-
Thanks!
I did look around to see if you could add multiple partitions to dense_rank(), but didn't see it in the docs I could find. - Erick Emde - Tuesday, October 10, 2017 3:20 PM
Thanks!
I did look around to see if you could add multiple partitions to dense_rank(), but didn't see it in the docs I could find.If you just Google DENSE_RANK() you'll find Microsoft's documentation on it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, October 11, 2017 6:08 AM
You're right, Steve. RTFD. I'm properly spanked. It's right there. On the OVER clause page, in the syntax section. You'd think at least one of the examples would show more than one field in the DENSE_RANK() or OVER doc pages.
- Erick Emde - Wednesday, October 11, 2017 7:43 AM
Well, ... wasn't really trying to spank anyone, but when you only look at examples, you only get limited info, because there's a direct link in that DENSE_RANK() page that sends you to the OVER clause documentation page here: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql
And that page clearly indicates that multiple value expressions can appear in the PARTITION BY clause.
Just need to be more thorough in looking at MS docs.Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - Oops! accidental double post...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply