CTE Line Number Segment

Question

I'm struggling to find the right logic for my CTE.

Some background information:

A task is generated in the system for all members with a sales role. This basically inserts a few entries into my Task table. The Task tab has, among others, the following columns: AssignedTo and RequestedBy - while AssignedTo will have each of the sale identifiers. Currently, all sellers can see the task, because no one claimed it:

 ApprovalStatusId EntityType EntityId AssignedTo RequestedBy 18 | FooBar | 281 | 4 | 6 18 | FooBar | 281 | 9 | 6 18 | FooBar | 281 | 17 | 6 18 | FooBar | 281 | 26 | 6 18 | FooBar | 281 | 39 | 6 

Now that the task has been generated for each seller, one of them does not matter who can respond to the task, which will change AssignedTo , as well as the status of the task:

 ApprovalStatusId EntityType EntityId AssignedTo RequestedBy 18 | FooBar | 281 | 4 | 6 18 | FooBar | 281 | 9 | 6 18 | FooBar | 281 | 17 | 6 18 | FooBar | 281 | 26 | 6 18 | FooBar | 281 | 39 | 6 17 | FooBar | 281 | 26 | 6 1 | FooBar | 281 | 6 | 6 

As can be seen from the table above, user 26 responded to the task and changed the status. Once this was done, the system redirected the task back to the original request, which was user 6 - the status also changed to indicate this action.

Questions:

• Currently, only one user (Id 4) sees a task assigned to all sales users.

What I tried:

In address # 1:

 ;WITH cte AS ( SELECT task.*, stat.Name AS StatusName, ROW_NUMBER() OVER (PARTITION BY EntityId, EntityType ORDER BY ModifiedData DESC) AS rn FROM dbo.Task task INNER JOIN dbo.ApprovalStatus stat on task.ApprovalStatusId = stat.ApprovalStatusId ) SELECT * FROM cte WHERE AssignedTo = @resourceId AND StatusName like 'Pending%' AND rn = 1 

So, one of the problems with this SQL fragment is AND rn = 1 , because it causes the problem raised in step 1, but if I delete it and the status changes, it will not receive a new task element. On the other hand, it also causes this problem.

I also tried changing the partition to: PARTITION BY EntityId, EntityType, AssignedTo ORDER BY ModifiedData DESC , but the problem with this is that in most cases, rn will be generated, which in most cases is 1, which will not lead to the creation of the correct result in sales group.

[Update]:

Task table definition:

Sample data:

 Comments InsertDate ModifiedData CommentUserId ApprovalStatusId EntityType EntityId TenantId AssignedTo RequestedBy ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |4 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |6 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |9 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |17 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |26 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |39 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |67 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |10073 |42 ... | 2017-03-20 11:18:06.343| 2017-03-20 11:18:06.343| NULL | 18 | FooBar | 75 |7 |10164 |42 

Required Conclusion:

The sales user who claimed the task should be the only one who can see it. In other words, the sales user should be able to see this record (visible in the second example of the table) - all other sales users do not see anything

 17 | FooBar | 281 | 26 | 6 

Answer 1

Your question would be better if you showed the desired results, but I think you might want to use a different ranking window function other than row_number() , like dense_rank()

 ;WITH cte AS ( SELECT task.*, stat.Name AS StatusName, dense_rank() OVER (PARTITION BY EntityId, EntityType ORDER BY ModifiedData DESC) AS dr FROM dbo.Task task INNER JOIN dbo.ApprovalStatus stat on task.ApprovalStatusId = stat.ApprovalStatusId ) SELECT * FROM cte WHERE AssignedTo = @resourceId AND StatusName like 'Pending%' AND dr = 1 

When multiple records are first inserted into your task table, rather than returning your cte:

 +------------------+------------+----------+------------+-------------+----+ | ApprovalStatusId | EntityType | EntityId | AssignedTo | RequestedBy | rn | +------------------+------------+----------+------------+-------------+----+ | 18 | FooBar | 281 | 4 | 6 | 1 | | 18 | FooBar | 281 | 9 | 6 | 2 | | 18 | FooBar | 281 | 17 | 6 | 3 | | 18 | FooBar | 281 | 26 | 6 | 4 | | 18 | FooBar | 281 | 39 | 6 | 5 | +------------------+------------+----------+------------+-------------+----+ 

He will return:

 +------------------+------------+----------+------------+-------------+----+ | ApprovalStatusId | EntityType | EntityId | AssignedTo | RequestedBy | dr | +------------------+------------+----------+------------+-------------+----+ | 18 | FooBar | 281 | 4 | 6 | 1 | | 18 | FooBar | 281 | 9 | 6 | 1 | | 18 | FooBar | 281 | 17 | 6 | 1 | | 18 | FooBar | 281 | 26 | 6 | 1 | | 18 | FooBar | 281 | 39 | 6 | 1 | +------------------+------------+----------+------------+-------------+----+ 

So, this will help to solve the problem number 1, where only one of the users sees the pending task.