CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
bradja
bradja

Reputation: 61

How to select a single record per segment based on priority

I have a list of Activities actioned by during each segment (StartTime, EndTime). Activities have a defined Priority (TASK = 7, CHAT = 10) with 10 being the highest.

+--------+------------------+------------------+----------+----------+
| UserID |     StartTime    |      EndTime     | ACTIVITY | PRIORITY |
+--------+------------------+------------------+----------+----------+
|  ABCD  | 2019/07/01 09:00 | 2019/07/01 09:15 |   TASK   |     7    |
+--------+------------------+------------------+----------+----------+
|  ABCD  | 2019/07/01 09:15 | 2019/07/01 09:45 |   TASK   |     7    |
+--------+------------------+------------------+----------+----------+
|  ABCD  | 2019/07/01 09:15 | 2019/07/01 09:45 |   CHAT   |    10    |
+--------+------------------+------------------+----------+----------+
|  ABCD  | 2019/07/01 09:45 | 2019/07/01 10:00 |   TASK   |     7    |
+--------+------------------+------------------+----------+----------+

I need to associate a single activity for each segment per user. That is, for the user ABCD in segment 09:15-09:45 there is currently 2 activities (TASK and CHAT). Based on their priorities, the CHAT should be assigned to this segment.

There will be multiple users with activities that can start/end at various times throughout the day and the solution will need to allow for this.

Expected Output

+--------+------------------+------------------+----------+----------+
| UserID |     StartTime    |      EndTime     | ACTIVITY | PRIORITY |
+--------+------------------+------------------+----------+----------+
|  ABCD  | 2019/07/01 09:00 | 2019/07/01 09:15 |   TASK   |     7    |
+--------+------------------+------------------+----------+----------+
|  ABCD  | 2019/07/01 09:15 | 2019/07/01 09:45 |   CHAT   |    10    |
+--------+------------------+------------------+----------+----------+
|  ABCD  | 2019/07/01 09:45 | 2019/07/01 10:00 |   TASK   |     7    |
+--------+------------------+------------------+----------+----------+

Scenario Data

DECLARE @ActivityLog TABLE (UserID VARCHAR(20), Activity VARCHAR(8), Priority INT, StartTime DATETIME, EndTime DATETIME)

INSERT INTO @ActivityLog VALUES ('ABCD', 'TASK', 7, '2019/07/01 09:00', '2019/07/01 09:15');
INSERT INTO @ActivityLog VALUES ('ABCD', 'TASK', 7, '2019/07/01 09:15', '2019/07/01 09:45');
INSERT INTO @ActivityLog VALUES ('ABCD', 'CHAT', 10, '2019/07/01 09:15', '2019/07/01 09:45');
INSERT INTO @ActivityLog VALUES ('ABCD', 'TASK', 7, '2019/07/01 09:45', '2019/07/01 10:00');

I'm stuck at this point trying to work out how to proceed further with solving this problem. Any solutions or advice will be much appreciated. Cheers.

Upvotes: 0

Views: 75

Answers (2)

B.Muthamizhselvi
B.Muthamizhselvi

Reputation: 642

select * from (
 select *, ROW_NUMBER()over(partition by UserID,StartTime, EndTime  order by PRIORITY 
 desc)as RNo from ActivityDetails)a
 where RNo=1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Using ROW_NUMBER along with TOP 1 WITH TIES we can try:

SELECT TOP 1 WITH TIES UserID, StartTime, EndTime, ACTIVITY, PRIORITY
FROM @ActivityLog
ORDER BY ROW_NUMBER() OVER (PARTITION BY UserID, StartTime, EndTime ORDER BY PRIORITY DESC);

enter image description here

Demo

ROW_NUMBER works here by finding the highest priority record for each user and timeframe.

Upvotes: 2

Related Questions