A change request (field „pcr“) only can have one unique assigned user that shall be informed about the assignment every night.
The assigned user (field „created_by“) of a change requests (field „pcr“) is changing several times a day. Every time the user (field „created_by“) changes to another user, a table entry with the actual time stamp (field „created_on“) is inserted to a SQL table.
At the end of the day, the system shall report via email the users that have been assigned to a PCR (only one user per change request).
The following table contains entries with people wo shall be informed every evening.
A nightly executed batch job (at 23:59h every evening) shall provide the possibility to inform user1, user2 or user3 about their assigned PCR’s. Only the latest entry per PCR, that can be recognized by the timestamp (field „created_on“) represent the user assigned user at the end of the day
The SQL Statement shall output the latest entry per assigned user of each PCR. With a standard aggregate function this is not possible because the whole row entry has to be retrieved.
The Microsoft SQL Server has a proprietary function RANK(), that prints out the ranking of the PCR entry in connection with PARTITION BY and ORDER BY. The ORDER BY has to be used DESC, so that the latest entry always is on rank 1.
Because this methods only can be used within the SELECT statements fields and not withing WHERE, we need a second parent query to select everything with rank 1.
SELECT * FROM ( SELECT id, pcr, created_on, created_by, RANK () OVER ( PARTITION BY pcr ORDER BY created_on DESC ) as biggestHasRangOne FROM nightly_email )tab WHERE biggestHasRangOne=1 AND CONVERT(date, GETDATE())=CONVERT (date, created_on)
To get the smallest one (minimum) in for that day you have to use
ORDER BY created_on ASC
In the RANK() – Partition by -Syntax.