Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
divan
Posting Yak Master
153 Posts |
 Posted - 2013-10-15 : 10:16:11
|
| Good Morning I have a table called Register that has the following in itPolicy_number, Policy_date_time, Portfolio_set, Status..The rule for the table is that the last record for each portfolio_set for a policy the status needs to be 'A' but there have been instances that the last record status is 'I' I need to identify the record that is out of place..In the example below record number 2.examplepolicy_number Policy_date_time Portfolio_set, status12345 1/1/2011 1 I12345 1/2/2011 1 A12345 1/3/2011 1 I12345 1/4/2011 1 II need to identify that the second record is in the wrong place... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-15 : 10:40:14
|
| [code]SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY policy_number ORDER BY policy_date_time DESC) AS RNFROM Register)s WHERE RN > 1 AND [status] = 'A'[/code] |
 |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-10-15 : 13:07:33
|
| This script is listing all the records that have a status 'A'.. like in the following example I do not want to list the following record 12345 3/4/2011 2 A since this is in the right sequence... policy_number Policy_date_time Portfolio_set, status12345 1/1/2011 1 I12345 1/2/2011 1 A12345 1/3/2011 1 I12345 3/4/2011 2 I12345 3/1/2011 2 I12345 3/2/2011 2 I12345 3/3/2011 2 I12345 3/4/2011 2 A |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-15 : 14:49:18
|
| Include Portfolio_set in the partition by clause. |
|
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-10-15 : 15:34:15
|
| Thanks worked great... |
|
|
|
|
|
|
Find a record out of place