CODEWITHSUNDEEP
sql
user2610646
user2610646

Reputation: 13

SQL - How to add a column based on multiple columns in a table

Good day everyone. I have been unable to come up with a solution and am looking for any assistance. I have a table for which I would like to add a column:

ID      Time_Stamp                       Activity_Log
1254    6/1/2013 8:00:00AM         'Ticket was opened by user A'
1254    6/2/2013 8:20:00AM         'Comment was logged by user B'
1254    6/4/2013 8:25:00AM         '**Ticket was transferred to Group 1**'
1254    6/7/2013 8:50:00AM         'Comment was logged by user B'
1254    6/11/2013 10:00:00AM       'Comment was logged by user C'
1254    6/12/2013 12:00:00PM       '**Ticket was transferred to Group 2**'
7589    6/3/2013 11:00:00PM        'Ticket was opened by user A'
7589    6/5/2013 1:00:00PM         'Comment was logged by user C'
7589    6/7/2013 2:00:00PM         'Resolution provided by user C'
7589    6/11/2013 5:00:00PM        'Ticket resolved by user C'
8526    6/3/2013 7:00:00PM        'Ticket opened by user D'
8526    6/4/2013 2:00:00PM        '**Ticket was transferred to Group 2**'
8526    6/5/2013 1:00:00PM        'Comment was logged by user G'
8526    6/8/2013 5:00:00PM        '**Ticket was resolved by user C**'

Here is what I would like the new column, Group, to look like:

ID      Time_Stamp                       Activity_Log                           Group
1254    6/1/2013 8:00:00AM         'Ticket was opened by user A'                NULL
1254    6/2/2013 8:20:00AM         'Comment was logged by user B'               NULL
1254    6/4/2013 8:25:00AM         '**Ticket was transferred to Group 1**'      Group 1
1254    6/7/2013 8:50:00AM         'Comment was logged by user B'               Group 1
1254    6/11/2013 10:00:00AM       'Comment was logged by user C'               Group 1
1254    6/12/2013 12:00:00PM       '**Ticket was transferred to Group 2**'      Group 2
7589    6/3/2013 11:00:00PM        'Ticket was opened by user A'                NULL
7589    6/5/2013 1:00:00PM         'Comment was logged by user C'               NULL
7589    6/7/2013 2:00:00PM         'Resolution provided by user C'              NULL
7589    6/11/2013 5:00:00PM        'Ticket resolved by user C'                  NULL
8526    6/3/2013 7:00:00PM        'Ticket opened by user D'                     NULL
8526    6/4/2013 2:00:00PM        '**Ticket was transferred to Group 2**'       Group 2
8526    6/5/2013 1:00:00PM        'Comment was logged by user G'                Group 2
8526    6/8/2013 5:00:00PM        '**Ticket was resolved by user C**'           Group 2

I would like to add this group column with a string if Activity_Log is LIKE '%xx%', and for each row after based on the ID and the Time_Stamp. Some of the ID's will not have the language in the activity_log and the Group column would be NULL for all rows. For the ones that do, I would like to start when the activity_log is LIKE '%transfer to Group x%' and end when the activity_log is either like '%Transfer to Group x%' or '%ticket was resolved%'. I have tried to write a query using OVER (partition by) to section off by the ID but I was not successful.

Thanks

Upvotes: 1

Views: 102

Answers (2)

mike27015
mike27015

Reputation: 676

I would go with Gordon Linoff's solution, but you can add the column that you need with an ALTER TABLE if you want to store it, if you want to show only the information then a SELECT is fine also.

ALTER TABLE tblLog
ADD Group VARCHAR(4)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can do this with a "cumulative max" function. This is supported by most databases that support window functions, with the exception of SQL Server prior to SQL Server 2012.

Here is the approach:

select t.id, t.Time_Stamp, t.Activity_Log,
       MAX(GroupName) over (partition by id order by TimeStamp) as "Group"      
from (select t.*,
             (case when Activity_Log like '%transferred to Group%'
                   then left(RIGHT(Activity_Log, 9), 7)
              end) as GroupName
      from t
     ) t;

If you actually want to add this as a column, you need to alter the table to add the column and then use an update statement.

If you database doesn't support a cumulative sum, you can still do the work with a correlated subquery:

select t.id, t.Time_Stamp, t.Activity_Log,
       (select left(RIGHT(t2.Activity_Log, 9), 7)
        from t t2
        where t2.id = t.id and
              t2.TimeStamp <= t.TimeStamp and
              Activity_Log like '%transferred to Group%'
        order by t2.TimeStamp desc
        limit 1
       ) as "Group"
from t;

Upvotes: 2

Related Questions