Solved

SQL Merge Data

Posted on 2011-03-01
16
364 Views
Last Modified: 2012-05-11
I have 2 tables, one which records tracking notes, the other only displays the Problem Note.
Please help, this is what I'm trying to achieve:
Table 1 is called CaseProgress has mulitple progress notes called case_progressnote with time stamps;
Table 2 is called Cases and has a table field called case_ProblemNote

I would like to merge all the case_progressnote values from the CaseProgress table into the case_ProblemNote field in the Cases table also inserting the the Case_assigneduserid and timestamp.

I have attached a text file with the results of SQL select statement from the 2 tables (CaseProgressTBL-CaseTBL.txt):

SELECT Case_CaseProgressID, Case_caseID, Case_ProblemNote, Case_ProgressNote, Case_createddate,
Case_TimeStamp FROM CaseProgress
WHERE Case_CaseId = '72'

Select Case_CaseId, Case_ProblemNote from Cases
WHERE Case_CaseId = '72'

I would like the result to appear in the Cases - Case_ProblemNote field as follows:
Case_ProblemNote
------------------------------
Test Tracking Problem Details.

UserID-5 Timestamp: 2011-03-01 20:13:24.000
Queue Stage Tracking Note 1

UserID-5 Timestamp: 2011-03-01 20:13:48.000
Investigating Tracking Note 2

UserID-5 Timestamp: 2011-03-01 20:14:01.000
Waiting Tracking Note 3

UserID-5 Timestamp: 2011-03-01 20:15:08.000
Queue Tracking Note 4

UserID-5 Timestamp: 2011-03-01 20:15:27.000
Waiting Tracking Note 5

UserID-5 Timestamp: 2011-03-01 20:15:49.000
Queued Tracking Note 6

UserID-5 Timestamp: 2011-03-01 20:15:49.000
Re-Assigned Tracking Note 7


 CaseProgressTBL-CaseTBL.txt
0
Comment
Question by:Sonderend
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 4

Assisted Solution

by:MeLindaJohnson
MeLindaJohnson earned 250 total points
ID: 35014135

create FUNCTION [dbo].[getProgressNotes]
  (@caseid int )
RETURNS varchar(4000)
AS
BEGIN
  DECLARE @result varchar(4000)
 
  declare @out varchar(10)
  SET @result = ''

SELECT @result =  @result + 'UserId-' + rtrim(cast(Case_AssignedUserId as varchar(3))) + ' Timestamp: ' + cast(case_timestamp as varchar(20)) + char(13) +  case_progressNote + char(13)
            FROM caseprogress WHERE case_caseID = @caseID



return(@result)
END

update cases
    set  cases.progress_note = case_progressNote + char(13) +  dbo.getProgressNotes (case_caseid)
from cases

be sure to test this before implementing in production since this is an update statement
where case_caseid = 72
0
 

Author Comment

by:Sonderend
ID: 35015287
MeLindaJohnson,  thanks for replying to my post.  I will try this on a test environment a bit later today and feed back.
0
 

Author Comment

by:Sonderend
ID: 35016714
I just tried the create function in a new query and it is returning the following message:

Msg 402, Level 16, State 1, Procedure getProgressNotes, Line 11
The data types varchar and ntext are incompatible in the add operator.

Not sure which data types or fields it is referring to?  Do you need any more info?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 25

Expert Comment

by:reb73
ID: 35016888
Just helping out, change the select statement within the function as follows and retry -

SELECT @result =  @result
			+ 'UserId-' + rtrim(cast(Case_AssignedUserId as varchar(3))) 
			+ ' Timestamp: ' + cast(case_timestamp as varchar(20)) + char(13) 
			+  cast(case_progressNote as nvarchar(max)) + char(13)
FROM caseprogress WHERE case_caseID = @caseID

Open in new window

0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35017788
change the function so that the variables are nvarchar and not varchar
0
 

Author Comment

by:Sonderend
ID: 35017939
Hi reb73, thanks for helping out, I will give this a go and feed back.
0
 

Author Comment

by:Sonderend
ID: 35025393
Hi, changing the varchar to nvarchar worked and creates the function, thanks.

I am however struggling with the update script;
Original -
update cases
    set  cases.progress_note = case_progressNote + char(13) +  dbo.getProgressNotes (case_caseid)
from cases
where case_caseid = 72

My Version -
update cases
    set  cases.Case_ProblemNote = case_progressNote + char(13) +  dbo.getProgressNotes (case_caseid)
from cases
where case_caseid = 72

Error message:
Msg 207, Level 16, State 1, Line 2
Invalid column name 'case_progressNote'.

Which is true, because case_progressNote is the field on the CaseProgress table not Cases.
Would this update statement need a join? How would I script the join, left outer?
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35027045
Probably should be case_PROBLEMnote?
0
 

Accepted Solution

by:
Sonderend earned 0 total points
ID: 35035685
I have a working solution now, looks like I was going about this wrong - thinking I need to create a function in SQL to do the required updates and call the function from the application (Sage CRM) via table level script.  This apparently is not possible and the function needs to be created using the systems existing table level script functionality.

Script as follows for your info:

function PostInsertRecord()

{

var timestampStr = Date().toString();
var userFirst = eWare.GetContextInfo("user", "user_firstname");
var userLast = eWare.GetContextInfo("user", "user_lastname");

var  caseProgRec = eWare.FindRecord("CaseProgress", WhereClause);
var caseID = caseProgRec.case_caseid;
var progressNote = caseProgRec.case_progressNote;
if(progressNote != undefined) {
var caseRec = eWare.FindRecord("cases", "case_caseid="+caseID);
var existingNote = caseRec.case_problemnote;
existingNote +="\n";
existingNote += "\n ";
existingNote += timestampStr + " : ";
existingNote += "[" + userFirst + " " + userLast + "] ";
existingNote += progressNote;
strSQL = "UPDATE Cases Set case_problemnote='" + existingNote;
strSQL += "' WHERE case_caseid=" + caseID;
eWare.ExecSql(strSQL);
}

}

I'm new to experts exchange and it appears I would need to allocate points for your assistance (MelindaJohnson & reb73) which I am happy to do and appreciate the assistance, but how and how many do I allocate.  What would happen if I click accepted solution, do the points go to me??

0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35037013
I don't know.  I've never asked a question.  It doesn't matter for me.
0
 

Author Comment

by:Sonderend
ID: 35037146
Okay thanks for the help anyway.
0
 
LVL 25

Expert Comment

by:reb73
ID: 35151825
Since you did not use any of the solutions provided by others, you will not be foregoing any points as such..

If you think the experts' comments  did help, it would be nice to give them assist points. But its totally upto you unless there is an explicit objection from the contributing experts.

In this specific case I feel Melinda deserves some points for having made an earnest attempt to assist you and followed up as well, but its just my opinion!
0
 

Author Comment

by:Sonderend
ID: 35152472
Hi reb73,
I agree, I would like to give some points to you both, but how and how many do I allocate?

This is my first question and first time of having to allocate points.

Thanks.
0
 
LVL 25

Expert Comment

by:reb73
ID: 35161842
No need to give me any points, I just proferred a correction (in case you were stuck) and didn't really contribute much in terms of a solution.

You should be able to edit and re-allocate points, but just in case I'll raise an objection so that a moderator may help out.

I'd recommend that your accepted solution be retained with an assist for Melinda's post (35014135)
0
 

Expert Comment

by:WhackAMod
ID: 35275393
Starting closing process on behalf of the asker.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question