Solved

SQL Merge Data

Posted on 2011-03-01
16
368 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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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 video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 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