February 1, 2013 at 12:26 pm
Ok, so this is pretty cool. I wish i knew how it worked.
It looks like the sample code had an extra ;WITH Actions (message) AS ( so I removed it.
I know that a cursor would be incredibly less inefficient but i am not sure what you mean about "one row". This is going to run across a table. Can't i step through each record and parse through the text (not sure what that would look like yet).
So how would I apply this code across the dataset (a join of two tables, Actions and Users, joined on Actions.UserID = Users.UserID?)
I assume that i would replace the SELECT '' that contains the sample text with a SELECT TextHTML FROM Actions? And then maybe SELECT FirstName FROM Users under the Operators(username) AS? But then what ties the datasets together to find out of the name associated to the UserID on the Actions record matches the name found in the brackets?
Argh.
PK
February 1, 2013 at 12:30 pm
What I mean by one row is your sample data is only 1 row. It is a long string with a bunch of html tags in it.
I added another twist to this so that you don't have to know the operators name. Instead it assumes the first name it finds is the person you want to exclude. The only change is the Operators cte pulls the data dynamically instead of being hardcoded.
,Operators (username) AS
(
select top 1
left(substring(message, charindex('[', message) + 1, len(message)), charindex(']', substring(message, charindex('[', message) + 2, len(message)), 1))
from Transcript
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2013 at 1:53 pm
OK. Yeah, i thought about making that assumption as well. At this point I am not sure if that is a fair assumption though.
Here is what i have come up with so far with the cursor. So far it actually seems to be working. One thing i need to work out yet is how to get to the end of that first customer chat line. For now i am just finding the first non matching name, and they backing up 13 chars from that bracket position to get the BR tag and the timestamp, and then grabbing everything after that. While this may not be the most efficient, I am super stoked that I have managed to get this close to the goal!
DECLARE @CursorID as int;/*For Cursor */
DECLARE @CursorFirstName as varchar(max);/*For Cursor */
DECLARE @CursorTextHTML as nvarchar(max);/*For Cursor */
DECLARE db_cursor CURSOR FOR
SELECT A.ID, U.FirstName, A.TextHTML
from CustServ.Multimedia.Actions A INNER JOIN CustServ.Multimedia.Users U on A.Agent = U.ID
WHERE A.[Type] = 9 AND A.CreationTime >= '2012-10-26 00:00:00' AND A.TimeAllocated > 0 AND A.TextHTML IS NOT NULL
--and A.ID = 6831190
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CursorID, @CursorFirstName, @CursorTextHTML
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @LftBracketOccurences as int = len(@CursorTextHTML) - len(replace(@CursorTextHTML, '[', ''))
DECLARE @TextHTMLTemp as nvarchar(max) = @CursorTextHTML
DECLARE @BracketName as varchar(max) = SUBSTRING(@TextHTMLTemp, CHARINDEX('[', @TextHTMLTemp , 1) + 1, CASE WHEN (CHARINDEX(']', @TextHTMLTemp , 0) - CHARINDEX('[', @TextHTMLTemp, 0)) > 0 THEN CHARINDEX(']', @TextHTMLTemp, 0) - CHARINDEX('[', @TextHTMLTemp, 0) - 1 ELSE 0 END)
DECLARE @LeftPointer as int = 0
DECLARE @RightPointer as int = 0
DECLARE @LftBracketPos as int = charindex('[', @TextHTMLTemp, @LeftPointer)
DECLARE @RtBracketPos as int = charindex(']', @TextHTMLTemp, @RightPointer)
DECLARE @LoopCounter as int = 0
DECLARE @FoundIt as int = 0
WHILE (@LoopCounter < @LftBracketOccurences) and (@FoundIt < 1)
BEGIN
IF (@CursorFirstName = @BracketName)
BEGIN
--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @CursorFirstName as RecordName, @BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos
SET @LeftPointer = @LftBracketPos +1
SET @RightPointer = @RtBracketPos +1
SET @LftBracketPos = charindex('[', @TextHTMLTemp, @LeftPointer)
SET @RtBracketPos = charindex(']', @TextHTMLTemp, @RightPointer)
SET @BracketName = SUBSTRING(@TextHTMLTemp, @LftBracketPos, @RtBracketPos-@LftBracketPos)
--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @CursorFirstName as RecordName,@BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos
END
ELSE
BEGIN
SELECT substring(@TextHTMLTemp,@LftBracketPos-13, len(@TextHTMLTemp)) -- need to find way to get end of string!!!
SET @FoundIt = 1
END
--select @CursorID, @LftBracketOccurences as LftBracketOccurences, @BracketName as BracketName, @LftBracketPos as LftBracketPos, @RtBracketPos as RtBracketPos
SET @LoopCounter = @LoopCounter + 1
END --while
FETCH NEXT FROM db_cursor INTO @CursorID, @CursorFirstName, @CursorTextHTML
END
/* closes and cleans up after cursor */
CLOSE db_cursor
DEALLOCATE db_cursor
February 1, 2013 at 1:59 pm
I am happy you are figuring this out. I am however sad that you were shown so many non-cursor based ways to do this and yet you chose to use a cursor.
That is sort of like getting to the race track without a car, you were scared to drive a Ferrari because you have never driven one, then you were taught how to drive it and you said "no thanks, I think I will stick with this Volkswagen Bug, it is slower than molasses on the tundra but it is comfortable".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2013 at 2:11 pm
Well that burst my bubble. I have not given up on the suggestions you gave. I just don't understand them and cannot figure out how to make them worth with my dataset. Yes, this is due to my lack of knowledge. I wanted to at least have SOMETHING that works, and that I understand, in my pocket. That does not mean that this is the code that I will go with. I have never used WITH or CROSS APPLY before and I don't know if i will be able to deploy the DelimitedSplit8K function in our environment.
I very much do appreciate your help, but this project is on a timeline and like I say, I wanted something that was at least functional as a fall back. I will need to discuss all of my options with my boss. He may understand your code and recommend going with it and may be able to explain it, but I wanted something to show that I have at least been working on this within my skill set.
Can you explain (or refer me to a reference) that would help explain what is going on in your example? I mean, even if it did work perfectly in my situation, I don't like using code that I don't fully understand.
PK
February 1, 2013 at 2:45 pm
paul.j.kemna (2/1/2013)
Well that burst my bubble.
My apologies. I did not want to burst your bubble or indicate that the work you put together is not good. It just isn't going to be nearly as fast.
I have not given up on the suggestions you gave. I just don't understand them and cannot figure out how to make them worth with my dataset. Yes, this is due to my lack of knowledge. I wanted to at least have SOMETHING that works, and that I understand, in my pocket. That does not mean that this is the code that I will go with. I have never used WITH or CROSS APPLY before and I don't know if i will be able to deploy the DelimitedSplit8K function in our environment.
WITH is just the beginning of a CTE (common table expression). It is basically just an inline view.
http://msdn.microsoft.com/en-us/library/ms175972.aspx
http://msdn.microsoft.com/en-us/library/ms190766.aspx
Check out the link in my signature about splitting strings. Show that to your boss. Sit down together and go over that article. I can't tell you how many times I use that function, and so many times in ways it was never intended to be used. The basis for it is the tally table. The tally table has to be one of the most flexible concepts.
Can you explain (or refer me to a reference) that would help explain what is going on in your example? I mean, even if it did work perfectly in my situation, I don't like using code that I don't fully understand.
+1,000,000 here. I applaud you for not just blindly using something that some guy on the internet says will work!!!
I will do my best to try to explain what is going on here.
;WITH Actions (message) AS
(
SELECT '< br>20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?< br>< br>20:32:31 [Marina Galofaro] What is the mailing address to mail a check?< br>< br>20:32:49 [Rachel] I''m happy to provide you with the mailing address.< br>< br>20:33:22 [Marina Galofaro] O kay, Thank you.< br>< br>20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.< br>< br>20:34:05 [Rachel] Please click <a href="http://company.custhelp.com/app/answers/detail/a_id/1050/kw/payment" target="_blank">here</a> to view how to mail a payment.< br>< br>20:35:07 [Rachel] Is there anything else I can help with, Marina? < br>< br>20:35:56 [Marina Galofaro] No, Thank you!< br>< br>20:36:00 [Marina Galofaro] Session Disconnected< br>'
)
This cte is simply a convenient way to hold your data while working on your problem. In the real world you would have this in a table. We don't have that table so we frequently use a CTE instead.
,Transcript(Message) as
(
select Item
from Actions
cross apply dbo.DelimitedSplit8K(replace(replace(message, '< br>< br>', char(10)), '< br>', ''), char(10))
)
This bit of code is taking the raw data from the previous step and parsing it into rows to make it easier to work with. Your raw data has everything strung end to end which makes splitting it apart a lot more difficult. You can view the contents of any of these CTEs by replacing the last select statement with something like "select * from Transcript".
,Operators (username) AS
(
select top 1
left(substring(message, charindex('[', message) + 1, len(message)), charindex(']', substring(message, charindex('[', message) + 2, len(message)), 1))
from Transcript
)
This is another CTE that is derived from the table created in Transcript. It just finds the portion between [ and ]. Truth be told as I am breaking this apart I should have included ItemNumber as a column in Transcript so I could use it to order by in here. The idea was to get the first name in the list.
When you see these CTEs defined with things like Operators(username) what that means is the CTE has a single column and the name is username. You don't have to define them like that but if you don't you have to make sure the columns inside are named.
I think the only remaining piece left here is APPLY. Paul White has a great 2 article series on APPLY.
http://www.sqlservercentral.com/articles/APPLY/69953/%5B/url%5D
http://www.sqlservercentral.com/articles/APPLY/69954/%5B/url%5D
As I walked through this code I changed up the names inside of each CTE which I hope will demonstrate more clearly what is going on here. Notice that I let the column names of the select inside the Transcript CTE define the names instead of naming them in the definition.
;WITH Actions (RawData) AS
(
SELECT '< br>20:32:12 [Rachel] Thank you for contacting Our Company. How may I assist you?< br>< br>20:32:31 [Marina Galofaro] What is the mailing address to mail a check?< br>< br>20:32:49 [Rachel] I''m happy to provide you with the mailing address.< br>< br>20:33:22 [Marina Galofaro] O kay, Thank you.< br>< br>20:33:28 [Rachel] One moment please while I provide a link with directions to mail your payment.< br>< br>20:34:05 [Rachel] Please click <a href="http://company.custhelp.com/app/answers/detail/a_id/1050/kw/payment" target="_blank">here</a> to view how to mail a payment.< br>< br>20:35:07 [Rachel] Is there anything else I can help with, Marina? < br>< br>20:35:56 [Marina Galofaro] No, Thank you!< br>< br>20:36:00 [Marina Galofaro] Session Disconnected< br>'
)
,Transcript as
(
select Item, ItemNumber
from Actions
cross apply dbo.DelimitedSplit8K(replace(replace(RawData, '< br>< br>', char(10)), '< br>', ''), char(10))
)
,Operators (username) AS
(
select top 1
left(substring(Item, charindex('[', Item) + 1, len(Item)), charindex(']', substring(Item, charindex('[', Item) + 2, len(Item)), 1))
from Transcript t
order by t.ItemNumber
)
SELECT
operator = LEFT(operator, CHARINDEX(']', operator)-1)
,TextOfMessage = LTRIM(RIGHT(operator, LEN(operator)-CHARINDEX(']', operator)))
FROM Transcript
CROSS APPLY
(
SELECT SUBSTRING(Item, CHARINDEX('[', Item)+1, LEN(Item)) as operator
) a
LEFT JOIN Operators ON LEFT(operator, CHARINDEX(']', operator)-1) = username
WHERE username IS NULL
I would assume that your base table has multiple IM threads that you are wanting to break apart. If you added the primary key, a RowNumber or some other way to identify each thread you could use this technique to break apart the entire table in a single query. You would probably have to add a few extra pieces to handle the grouping but it shouldn't be too bad.
Hopefully I have helped make at least some of the easier to understand. If you have never used any of those techniques it is a lot to take in all at once. Give it some thought over the weekend and take another look on Monday.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2013 at 11:52 am
Thank you very much for your patience and help, Sean. I really appreciate it.
So is a CTE kind of like a temp table to hold a dataset in table format? I glanced through the links you sent, but will take deeper look into them later today.
So that first WITH statement would not necessarily exist in my final query? It is just holding the sample record that I provided? Or would you still hold each record in a CTE?
Or is that is what is happening in the next step where you use the function? Breaking it out into separate records for each piece that is broken at the delimiter?
I will try to read through the APPLY links you supplied also. In the meantime, here is some data that is more representative of the situation. Could you tweak your code to work against this data. Maybe then it will make more sense to me.
Once again, thank you.
The Actions table. (I had to spoil the HTML tags again by putting a space after <)
SELECT * INTO #Actions
FROM (
SELECT N'6828887' AS [ID], N'3700' AS [Agent], N'3614961' AS [ContactId], N'2012-11-08 15:31:14.000' AS [CreationTime], N'< BR>15:19:34 [Annabelle] Thank you for contacting Our Company. How may I help you?< BR>< BR>15:21:04 [Teachercool343 Cool343] i just placed my order yesterday but i forgot to use my two coupons when making an order of $20 or more < BR>< BR>15:21:51 [Annabelle] I am happy to assist you in redeeming your two $5.00 coupons. < BR>< BR>15:22:10 [Annabelle] Could I please get your coupons codes from you? < BR>< BR>15:24:09 [Annabelle] Are you still there? < BR>< BR>15:24:46 [Teachercool343 Cool343] Yes, abcdef and ghij they are from the welcome pack < BR>< BR>15:25:50 [Annabelle] One moment while I add those coupons to your account for you. < BR>< BR>15:26:13 [Teachercool343 Cool343] Thanks! < BR>< BR>15:26:55 [Annabelle] Thank you for your patience. May I please have the items you are wanting for you coupons? < BR>< BR>15:27:32 [Teachercool343 Cool343] 2, 8 < BR>< BR>15:28:02 [Annabelle] Thank you for the items. It will be just a moment while I add those to your account. < BR>< BR>15:28:13 [Teachercool343 Cool343] ok < BR>< BR>15:29:33 [Annabelle] I have added those to your account xxxxxxxxxx. The items you are wanting will be arriving to xxxxx, with in 10 business days with our free shipping. Anything else I can help you with? < BR>< BR>15:30:06 [Teachercool343 Cool343] no thanks < BR>< BR>15:30:11 [Annabelle] Would you like a transcript of our chat session emailed to you? < BR>< BR>15:30:49 [Teachercool343 Cool343] no < BR>< BR>15:30:56 [Annabelle] At this time I will be closing our chat. If you need further assistance in the future please feel free to contact us again by chat or by email at. Thank you.< BR>< BR>' AS [TextHTML], N'701' AS [TimeAllocated], N'9' AS [Type] UNION ALL
SELECT N'6830693' AS [ID], N'2938' AS [Agent], N'3615658' AS [ContactId], N'2012-11-08 17:55:06.000' AS [CreationTime], N'< BR>17:54:01 [Rachel] Thank you for contacting Our Company. How may I assist you?< BR>< BR>17:54:43 [Debbie Prinster] Session Disconnected< BR>< BR>' AS [TextHTML], N'127' AS [TimeAllocated], N'9' AS [Type] UNION ALL
SELECT N'6830826' AS [ID], N'2938' AS [Agent], N'3615688' AS [ContactId], N'2012-11-08 18:07:15.000' AS [CreationTime], N'< BR>18:00:30 [Rachel] Thank you for contacting Our Company. How may I assist you?< BR>< BR>18:01:32 [Sherilyn Stoffel] I received the coupon in the mail and am using it. can I also spemd the $10 reward I received for spending $20? < BR>< BR>18:01:50 [Rachel] I''m happy to help you with your coupons.< BR>< BR>18:02:11 [Rachel] Yes, you may certainly spend the $10 for an order of $20 or more.< BR>< BR>18:02:36 [Sherilyn Stoffel] and I can use the free pack too< BR>< BR>18:03:08 [Rachel] Yes, you may use all of the coupons and rewards you earn.< BR>< BR>18:03:19 [Sherilyn Stoffel] it had said it cannt be used with any other coupon and I wasnt sure if the $10 counted as another coupon< BR>< BR>18:03:27 [Sherilyn Stoffel] yay thank you< BR>< BR>18:03:58 [Rachel] Is there anything else I can help with, Sherilyn? < BR>< BR>18:04:33 [Sherilyn Stoffel] thats it thank you< BR>< BR>18:04:55 [Rachel] You''re welcome. Would you like a transcript of our chat session?< BR>< BR>18:06:20 [Rachel] Are you still there? I have not received a response for a while, so our chat session will close in 30 seconds. Please respond within this time to continue our session. < BR>< BR>18:07:05 [Rachel] At this time, I will be closing our chat. If you need further assistance in the future, please feel free to contact us again by chat or by email at. Thank you for choosing Our Company.< BR>< BR>' AS [TextHTML], N'406' AS [TimeAllocated], N'9' AS [Type] UNION ALL
SELECT N'6831190' AS [ID], N'1773' AS [Agent], N'3615834' AS [ContactId], N'2012-11-08 18:44:17.000' AS [CreationTime], N'< BR>18:38:31 [Jenifer] Thank you for contacting Our Company. How may I assist you?< BR>< BR>18:39:02 [Ann-marie Vazquez] i am placing an order for a student and i see i am being charged shipping.< BR>< BR>18:39:56 [Jenifer] I am more than happy to help you with the shipping. Are you placing the order manually from your paper copies, or is it an online order?< BR>< BR>18:40:57 [Ann-marie Vazquez] from paper. i did this once before and i was not charged shipping so i don''t know what i''m doing wrong this time.< BR>< BR>18:42:44 [Jenifer] If the order is from the paper form, the order will need to come to $20.00 or more to avoid the shipping fee. I am showing the order you placed last time had online orders, which automatically qualified you for the free shipping.< BR>< BR>18:43:21 [Ann-marie Vazquez] ok. thanks< BR>< BR>18:43:48 [Jenifer] You are welcome! Is there anything else I can help with, Ann-Marie? < BR>< BR>18:44:05 [Ann-marie Vazquez] no thanks. good night.< BR>< BR>18:44:13 [Ann-marie Vazquez] Session Disconnected< BR>< BR>' AS [TextHTML], N'357' AS [TimeAllocated], N'9' AS [Type] ) t;
The Users table:
SELECT * INTO #Users
FROM (
SELECT N'1773' AS [ID], N'Jenifer' AS [FirstName], N'Harmon' AS [LastName] UNION ALL
SELECT N'2938' AS [ID], N'Rachel' AS [FirstName], N'Michael' AS [LastName] UNION ALL
SELECT N'3700' AS [ID], N'Annabelle' AS [FirstName], N'Shkoordin-Derges' AS [LastName] ) t;
And the final dataset, a join of the two tables so we can get the Agent first name:
SELECT A.*, U.*
FROM #Actions A INNER JOIN #Users U on A.Agent = U.ID
WHERE A.[Type] = 9 AND A.CreationTime >= '2012-10-26 00:00:00' AND A.TimeAllocated > 0 AND A.TextHTML IS NOT NULL
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply