1

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

Improve this question
2
  • 2
    So...a calculated column? Also, which dbms is this? sql is a little too ambiguous. p.s. depending on the size of the table, you may want to just add a normal column, run a parser through it one-off, then use a trigger to keep the new column synced. A computed/calculated column can be very intensive in terms of added overhead. Commented Jul 23, 2013 at 13:36
  • 1
    Should writing this 'group' column be the responsibility of the app code rather than the database? It seems like the app code has all the information needed to set it correctly. Commented Jul 23, 2013 at 13:53

2 Answers 2

Reset to default
2

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.