Ok i read your comment now, then the firts version i wrote is ok, i just allows up steps. I just accepts Single, Married, (Divorced or Widowed). i does not...

What do you think, you like it?

;WITH CivilStatus (StatusSequenceNumber,[Marital_Status])

AS

(

SELECT 1,'SINGLE' UNION

SELECT 2,'MARRIED' UNION

SELECT 3,'DIVORCED' UNION

SELECT 3,'WIDOWED'

),

PersonCivilStatus

AS

(

SELECT d.empno,d.marital_status,d.status_change_date,

Sequence = row_number() over (partition by empno order by status_change_date),

StatusSequenceNumber

FROM #temp_delete_duplicate d

INNER JOIN CivilStatus cs

ON cs.Marital_Status =...

The idea is to order the status changes of a employee chronologically. Now, every status has the "Step Value", if two consetive items are different by 1, then sequence...

I understand what you mean now. Yes man, that is rigth, that number is not actually an Id, is a step value, the 3 is because widowed and divorced can...

let me checlk. One minute

By the way, im not trying to probe that my point of view is right or better, im just giving a different perspective of what thing migth look like, if...

Employee number 5 is another example of this, according to the example he is ok being originally widowed, but if two days later some computer operator changes is status to...

I make the following.

;WITH CivilStatus (StatusSequenceNumber,[Marital_Status])

AS

(

SELECT 1,'SINGLE' UNION

SELECT 2,'MARRIED' UNION

SELECT 3,'DIVORCED' UNION

SELECT 3,'WIDOWED'

),

PersonCivilStatus

AS

(

SELECT d.empno,d.marital_status,d.status_change_date,

Sequence = row_number() over (partition by empno order by status_change_date),

StatusSequenceNumber

FROM #temp_delete_duplicate d

INNER JOIN CivilStatus...

Ohh dude i think i understood the problem wrong, i made the query to delete the whole employee entries, not just the incorrect status changes.

Ok, here it is:

;WITH CivilStatus (StatusSequenceNumber,[Marital_Status])

AS

(

SELECT 1,'SINGLE' UNION

SELECT 2,'MARRIED' UNION

SELECT 3,'DIVORCED' UNION

SELECT 3,'WIDOWED'

),

PersonCivilStatus

AS

(

SELECT d.empno,d.marital_status,d.status_change_date,

Sequence = row_number() over (partition by empno order by status_change_date),

StatusSequenceNumber

FROM #temp_delete_duplicate d

INNER JOIN CivilStatus...

c'mon IBar you just need

delete from mytable where empno in

( the last query i sent you)

well, no comments, im out..

Hi,

This is better and stick to the "at least two marital status" clause.

;WITH CivilStatus (StatusSequenceNumber,[Marital_Status])

AS

(

SELECT 1,'SINGLE' UNION

SELECT 2,'MARRIED' UNION

SELECT 3,'DIVORCED' UNION

SELECT 3,'WIDOWED'

),

PersonCivilStatus

AS

(

SELECT d.empno,d.marital_status,d.status_change_date,

...

Now this is better. If the first marital status recorded can be anything (not just single), delete the last condition of the where clause.

;WITH CivilStatus (StatusSequenceNumber,[Marital_Status])

AS

(

SELECT 1,'SINGLE' UNION

SELECT 2,'MARRIED' UNION

SELECT...