Search code examples
sqlsql-servert-sqlwindow-functions

Row_Number() OVER PARTITION BY based on a value in column

enter image description here

Table Name: L

I'm currently working with a dataset that aims to offer insights on customer support cases. In the example above, you can see the progression of case 123376 from reopened to answered.

When I use the partition function ROW_NUMBER() OVER (PARTITION BY L.CaseNumber, L.[Status], L.NextDate ORDER BY L.[Date] ASC) then I get the Row_Number as above. However, once the case status is answered and the next date is NULL, then I want to delete the rows for that case that come after, meaning I only want to keep the first row for Next Date NULL (in a single case number) and delete the others. Is there a way to have row numbers over partition so that the result looks like this or any other approach? enter image description here

Solution

  • I am wondering if the following expression might do what you want - at least, I think that it should work for your sample data:

    DENSE_RANK() OVER (
    PARTITION BY L.CaseNumber, L.[Status], L.NextDate 
    ORDER BY COALESCE(l.NexDate, L.[Date])
)