Can you post the whole procedure?

Yum!

Dewes van Lohuizen (8/12/2010)

---

@Sushant

Brandie is right: Holland is a province in the Netherlands, we have North Holland and South Holland.

People often says Holland and mean The Netherlands.

And football: Spain was...

;WITH OrderedRows AS (

SELECT RowNo = ROW_NUMBER() OVER (PARTITION BY acolumnlist ORDER BY acolumnlist)

FROM MyTableWithDupes

)

DELETE FROM OrderedRows WHERE RowNo > 1

Edited to show that the script includes DELETE.

Me too.

Getting tired now after...

You're...

Where does the end-date come from, for rowid 3?

khushbu (8/12/2010)

---

posting a sample output required.

Rowid deccode tarief Startdate Enddate

1 010127 4300 2005-01-01 2005-12-31

2 010127 4300 2006-01-01 2007-12-31

3 010128 4300 2008-01-01 2004-12-31

4 010229 4300 2005-01-01 2005-12-31

5 010230 4300 2006-01-01 2099-01-01...

khushbu (8/12/2010)

---

Thanks for all the help!

But may be I did not get the partition part .

No, a startdate from one deccode cannot be used as the enddate for another.

The end...

That's a description of a row, not a partition, which is a group of rows with a common value in one...

khushbu (8/12/2010)

---

e1.startdate or e2.startdate can be used, but dont know why, there is NULL in some rows, while there exists data in all the rows of s.startdate.

I know NULL...

khushbu (8/12/2010)

---

The query does not give result as desired.

The enddate for rowid = 1 should be 2005-12-31 while it gives 2004-12-31.

The query seems to be substracting 1 from the...

Dennis, I'm not sure if this will work because there's nothing to test against, but give it a try. Even if it doesn't, it should give you a few ideas:

SELECT...

If RowID has no gaps and can be reliably used to identify "the other row", then something like this should work for you:

CREATE TABLE #MyTable (

Rowid INT,

deccode CHAR(6),

tarief...

How do you know which row the enddate should be derived from?

Are there gaps in the sequence suggested by column RowID?