Solved

SQL Merge Data

Posted on 2011-03-01
16
359 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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 …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now