Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Merge Data

Posted on 2011-03-01
16
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 4

Assisted Solution

by:MeLindaJohnson
MeLindaJohnson earned 1000 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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