October 28, 2008 at 3:56 am
Hi,
i use a sql 2005 database where we stored phone call historic notes. The detail of the phone call is stored on a a field from table called:
Table: 'AMGR_Notes_Tbl'
Field: 'TextCol'
The text charcter in the 'Textcol''s fieldlooks like this:
"Spoke with Joe Adams. i told him ti schedule a demo meeting.
Phone Call: Aggassi Cold Beer & Wine Store
Number Called: Incoming Call
Subject: Cold call
Result: Arranged interview
Duration: 00:00:18"
What i need:
I need to obtain this column:
Subject | Result | Duration |
Do you have an idea to design a query to obtain this result?
Regards,
Ozzy
October 28, 2008 at 7:31 am
If you are guaranteed a consistent order in the data this works:
Declare @AMGR_Notes_Tbl table (textcol varchar(max))
Insert Into @AMGR_Notes_Tbl
Select
'Spoke with Joe Adams. i told him ti schedule a demo meeting.'+
'Phone Call: Aggassi Cold Beer & Wine Store' +
'Number Called: Incoming Call' +
'Subject: Cold call' + CHar(10) + Char(13) +
'Result: Arranged interview' +
'Duration: 00:00:18'
Select
Substring(textcol, CharIndex('Subject', TextCol) + Len('Subject') + 1,
CharIndex('Result', TextCol)-CharIndex('Subject', TextCol) - Len('Subject') - 1) as subject,
Substring(textcol, CharIndex('Result', TextCol) + Len('Result') + 1,
charIndex('Duration', TextCol) - CharIndex('Result', TextCol) - Len('Result')-1) as result,
Substring(textcol, CharIndex('Duration', TextCol) + Len('Duration') + 1,
Len(TextCol) - CharIndex('Duration', TextCol)) as duration
From
@AMGR_Notes_Tbl A
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 28, 2008 at 9:53 am
Thanks very much jack.
it works like a charm.
Great help.
regards,
Ozzy
October 28, 2008 at 10:55 am
Re-Hi Jack,
How I can modify the query to obtain the result with the same column but with all the value from a type of phone call note.
The table 'AMGR_Notes_Tbl' in sql server 2005 stored all the notes we create and modify for aour application. Notes are organised by 'Type' as 'Manual', 'Phone Call', 'History'. The 'phone call' type is equal '2' so if i want to retrieve all the 'phone call' notes, I use this query,
'select Type, TextCol, DateCol from AMGR_Notes_tbl where type =2'
SO, the thing that I would obtain is a query that give me all the values all the phonecall notes and displayed as:
DateCol |Subject | Call result | Duration |
(* the DateCol is a datetime field)
Any idea?
Many thanks in advance,
Ozzy
October 28, 2008 at 11:33 am
That would be basically the same query I gave you with datecol added to the output and a where clause.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 28, 2008 at 2:14 pm
Hi Jack,
I ad the 'DateCol' at the output but it doesn'work (name of column non valid 'DateCol').
Also, i cannot retrieve all the phone call notes. The query only extract term from one note and not for all.
Any idea?
Regards,
Ozzy
October 28, 2008 at 2:35 pm
jacopasto (10/28/2008)
I ad the 'DateCol' at the output but it doesn'work (name of column non valid 'DateCol').
Sounds like you have spelled the column name wrong, check that.
Also, i cannot retrieve all the phone call notes. The query only extract term from one note and not for all.
Huh?
Please read the articles linked in my signature line and post according to the suggestions. I can't do anymore without the information mentioned in the articles.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 28, 2008 at 3:10 pm
Ok Jack,
My Notes table looks like this:
Data_Machine_Id Sequence_Number Owner_Id Client_Id Contact_Number Type Private DateCol TimeCol NewRecord Owned_By_Id Record_Id Note_Type TextCol
--------------------------------------- --------------------------------------- ------------ ------------------------ -------------- ----------- ----------- ----------------------- ----------------------- ----------- ------------ ----------- -------------------- -------- ------------ ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
772238169 1 MASTER 001124000913063671156C 0 2 0 2002-10-08 00:00:00.000 1900-01-01 09:12:52.000 65535 MASTER 11 0 Spoke with Joe Adams. i told him ti schedule a demo meeting. Phone Call: Aggassi Cold Beer & Wine Store Number Called: Incoming Call Subject: Cold call Result: Arranged interview Duration: 00:00:18
772238169 2 MASTER 001124000913063671156C 0 2 0 2003-12-07 00:00:00.000 1900-01-01 20:55:20.000 65535 12 0 Phone Call: Aggassi Cold Beer & Wine Store. Number Called: 1 250 988.0089Phone Description: Main Result: Left Message Duration: 00:00:00
It is just a sample with two notes.
I just need to obtain these data as:
Date | Subject | Call subject | Duration
10/08/2003 arranged interview cold call 00:00:18
12/07/2002 Left message 00:00:00
Is it more convenient?
Many thanks.
Regards,
Ozzy
October 29, 2008 at 6:20 am
This should work:
[font="Courier New"]DECLARE @AMGR_Notes_Tbl TABLE (Data_Machine_Id INT, Sequence_Number INT, Owner_Id VARCHAR(10),
Client_Id VARCHAR(10), Contact_Number VARCHAR(15),
TYPE CHAR(1), Private INT, DateCol smalldatetime,
TimeCol smalldatetime, NewRecord INT, Owned_By_Id VARCHAR(10),
Record_Id INT, Note_Type INT, TEXTcol VARCHAR(MAX))
INSERT INTO @AMGR_Notes_Tbl
SELECT
772238169 AS Data_Machine_ID,
1 AS Sequence_number,
'MASTER' AS owner_id,
'001124000' AS client_Id,
'913063671156C' AS contact_number,
0 AS TYPE,
2 AS private,
'2002-10-08 00:00:00.000',
'1900-01-01 09:12:52.000',
65535,
'MASTER',
11,
0,
'Spoke with Joe Adams. i told him ti schedule a demo meeting.'+
'Phone Call: Aggassi Cold Beer & Wine Store' +
'Number Called: Incoming Call' +
'Subject: Cold call' +
'Result: Arranged interview' +
'Duration: 00:00:18'
UNION ALL
SELECT
772238169,
2,
'MASTER',
'001124000',
'913063671156C',
0,
2,
'2003-12-07 00:00:00.000',
'1900-01-01 20:55:20.000',
65535,
'MASTER',
12,
0,
'Phone Call: Aggassi Cold Beer & Wine Store.' +
' Number Called: 1 250 988.0089 ' +
'Phone Description: Main ' +
'Result: Left Message ' +
'Duration: 00:00:00'
SELECT
DateCol,
CASE
WHEN CHARINDEX('Subject', TEXTCol) > 0 THEN
SUBSTRING(TEXTcol, CHARINDEX('Subject', TEXTCol) + LEN('Subject') + 1,
CHARINDEX('Result', TEXTCol)-CHARINDEX('Subject', TEXTCol) - LEN('Subject') - 1)
ELSE NULL
END AS subject,
CASE
WHEN CHARINDEX('Result', TEXTCol) > 0 THEN
SUBSTRING(TEXTcol, CHARINDEX('Result', TEXTCol) + LEN('Result') + 1,
CHARINDEX('Duration', TEXTCol) - CHARINDEX('Result', TEXTCol) - LEN('Result')-1)
ELSE NULL
END AS result,
CASE
WHEN CHARINDEX('Duration', TEXTCol) > 0 THEN
SUBSTRING(TEXTcol, CHARINDEX('Duration', TEXTCol) + LEN('Duration') + 1,
LEN(TEXTCol) - CHARINDEX('Duration', TEXTCol))
ELSE NULL
END AS duration
FROM
@AMGR_Notes_Tbl A
[/font]
Also you may want to watch the width of your posts (code blocks and quotes) as most folks dislike the horizontal scroll.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 29, 2008 at 10:12 am
Many thanks Jack.
The sql retrieve the two notes. How i can design the query to retrieve more than two the notes (the text is different as the call result's value, the Duration and the subject)?
regards,
Ozzy
October 29, 2008 at 10:24 am
jacopasto (10/29/2008)
Many thanks Jack.The sql retrieve the two notes. How i can design the query to retrieve more than two the notes (the text is different as the call result's value, the Duration and the subject)?
regards,
Ozzy
You need to be more specific. Do you mean that the identifier (i.e., "Duration") is different? As I said in my first post, if you are guaranteed to have the same format and order
then my query works other wise you need to do something different.
In reality the design stinks. You should have columns for each of the properties, subject, duration, etc..., that you enter the data in instead of a huge varchar column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 29, 2008 at 10:55 am
Hi,
To be more specific, the format and the order of the Textcol column could be different but 'Duration', 'call result', 'Object' are nots stored in individual column but in a Text column. Only the Datecol is stored in a single column.
So for each phone call notes (Type=2), i need to extract the 'Call result' value (after the =), the 'Duration' and the 'Object='.
Do You need that i send you a picture of the table?
Thnks for your help.
Ozzy
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy