Menu
I'm struggling to get to the correct logic with regards to my CTE.
Some background information:
A task is generated in the system for all the members that has the sales role. This basically inserts multiple records into my Task table. The Task tabel has amongst other, the following columns: AssignedTo and RequestedBy - whereby AssignedTo will have each of the sale members Id's. Currently all the sales people can see the task, because no one has 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 a task has been generated for each sales person, one of them, doesn't matter who, can react on the task, which will change the AssignedTo as well as the task status:
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 you can see from the above table, user 26 reacted to the task and changed the status. Once that was done, the system redirected the task back to the original requested, which was user 6 - the status also changed to indicate this action.
Issues:
What I've tried:
To 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 issues with this piece of SQL is AND rn = 1, because this is what causes the issue raised in point 1, but if I remove it and the status changes, it won't get the newest task item. On the other-hand, it also causes this issue.
I've also tried to change the partitioning to: PARTITION BY EntityId, EntityType, AssignedTo ORDER BY ModifiedData DESC, but the problem with that is, it will produce a rn that is equal to 1 in most cases, which won't produce the correct result based on the 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
Desired output:
The sales user that claimed the task should be the only one that can see it. In other words, the sales user should be able to see this record (visible in second table example) - all the other sales users should see nothing
17 | FooBar | 281 | 26 | 6
938
The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
Using SQL Server ROW_NUMBER() for pagination In this example: First, the CTE used the ROW_NUMBER() function to assign every row in the result set a sequential integer. Second, the outer query returned the rows of the second page, which have the row number between 11 to 20.
ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5). ROW_NUMBER is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE.
PARTITION BY It is an optional clause in the ROW_NUMBER function. It is a clause that divides the result set into partitions (groups of rows). The ROW_NUMBER() method is then applied to each partition, which assigns a separate rank number to each partition.
Your question would be better if you showed your desired results, but I think what you might want to do is consider using a different ranking window function other than row_number(), such as 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 the multiple records are initially inserted into your task table, instead of your cte returning:
+------------------+------------+----------+------------+-------------+----+
| 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 |
+------------------+------------+----------+------------+-------------+----+
It would 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 that would solve issue #1, where only one of the users is seeing the pending task.
63
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
