Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Insert using while (No Cursors) Help ! Urgent

Author  Topic 

sushant17284
Starting Member

12 Posts
Posted - 2010-04-28 : 14:30:31
Well, the problem I am facing is a simple one , but I just can't seem to be able to get any logic to solve this out.
I hope my people at SQL Team will come to my rescue as this needs to be shown to the client by tomorrow and I just can't seem to get this work.Please help everyone.I need your inputs.

Here is the problem.
I have an EMPLOYEE_REVIEW TABLE, WHICH HAS THE FOLLOWING FIELDS:
1. EMP_REVIEW_ID
2. EMP_REVIEW_DATE
3. EMP_REVIEW_COMMENT
4. CONTENT_ID
The data in this table looks like this :



As you can notice there can be multiple review comments for each EMP_REVIEW_ID for each employee.


Now here is the issue that I am facing :
I have a table as EMPLOYEE_REPORT which looks like this ....



As shown in the image above for the EMPLOYEE_REPORT table the fields EMP_REVIEW_COMMENTS IS EMPTY.

My problem is to add a logic by which I can add the review_comments from the other table in the EMP_REVIEW_COMMENTS FIELD .
The following image shows how each row should add the comments.



As shown in the figure, for each EMP_REVIEW_ID the EMP_REVIEW_COMMENTS are added from the EMPLOYEE_REVIEW table and those comments are added with a delimiter ,when multiple comments exist for the same EMP_REVIEW_ID.

I just can't seem to get any logic to be able to iterate through the EMPLOYEE_REVIEW table and also add/merge the mutiple review comments in such a way so that they are added/inserted in the EMP_REPORT table according to the EMP_REVIEW_ID.



I tried the following
I first iterated through the EMP_REPORT table :
DECLARE @RowCount INT
DECLARE @EMP_REVIEW_ID INT
SET @RowCount = (SELECT COUNT(EMP_REVIEW_ID) FROM EMPLOYEE_REPORT)

--DECLARED COUNTER FOR WHILE LOOP
DECLARE @I INT
SET @I = 1

WHILE (@I<= @RowCount)
BEGIN
SELECT
@EMP_REVIEW_ID = EMP_REVIEW_ID
FROM EMPLOYEE_REPORT ER
WHERE ID = @COUNTER



Then in the while loop get the EMP_REVIEW_DATE in a variable
SELECT @EMP_REVIEW_DATE = EMP_REVIEW_DATE
FROM EMPLOYEE_REVIEW
WHERE EMP_REVIEW_ID = @EMP_REVIEW_ID


Then , I tried to set the comments variable

DECLARE @REVIEW_COMMENTS NVARCHAR(4000)
SELECT @REVIEW_COMMENTS = @REVIEW_COMMENTS + @EMP_REVIEW_DATE + ' - '+ EMP_REVIEW_COMMENT FROM EMPLOYEE_REVIEW WHERE EMP_REVIEW_ID = @EMP_REVIEW_ID


The problem with this solution is that the output SHOWN FOR EMP_REVIEW_ID AS 2000000 looks like this:
For example : 28 Mar 2010 - some comment | 28 Mar 2010 - new comment | 28 Mar 2010 - test comment
As you can notice , the date is always picked up as the last date for that EMP_REVIEW_ID.I am able to fetch the comments but not the Date accordingly.
I am stuck for hours.......Please help. :(
You guys are my last hope.

One last thing I would like to mention is I cannot use cursors here.The client was already having cursors to do this logic.They were using nested cursors to fetch each comment and then insert it in the EMPLOYEE_REPORT table.They want to eliminate the cursor logic and want to replace it with just SQL queries.

Your help is greatly appreciated.Please help .
Cheers,
Max

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-04-28 : 14:54:01
What version of SQL are you using?

Can you put your data is a consumable format (DDL & DML)? This link will help describe how to do that if you do not already know how:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts
Posted - 2010-04-28 : 14:59:15
Sure smells like a year-end/final school project, no??

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-04-28 : 15:01:03
shot in the dark:
UPDATE
	Report
SET
	EMP_REVIEW_COMMENTS = Review.EMP_REVIEW_COMMENTS
FROM
	EMPLOYEE_REPORT AS Report
INNER JOIN
	(
		SELECT 
			EMP_REVIEW_ID, 
			STUFF((SELECT ' | ' + CONVERT(VARCHAR(9), EMP_REVIEW_DATE, 106) + ' - ' + EMP_REVIEW_COMMENT FROM EMPLOYEE_REVIEW WHERE EMP_REVIEW_ID = ER.EMP_REVIEW_ID FOR XML PATH('')),1,1,'') + ' |' AS EMP_REVIEW_COMMENTS
		FROM 
			EMPLOYEE_REVIEW AS ER
		GROUP BY 
			EMP_REVIEW_ID
	) AS Review
	ON Report.EMP_REVIEW_ID = Review.EMP_REVIEW_ID
Go to Top of Page

sushant17284
Starting Member

12 Posts
Posted - 2010-04-29 : 00:58:31
It isn't a final year school project Mate.I am working for a huge MNC and due to security reasons, couldn't post the exact kind of tables and data I am dealing with.That's the reason I had to make everything generic and simple for SQL team guys to try and make them understand the problem I am facing

quote:
Originally posted by tosscrosby

Sure smells like a year-end/final school project, no??

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher
Go to Top of Page

sushant17284
Starting Member

12 Posts
Posted - 2010-04-29 : 01:13:59
I am using MS SQL SERVER 2008.
And I am sorry , at this point of time I can't put the data in to the DML format.As you may have noticed in the reply above , these tables are just a mere replication of the original tables for reference.
quote:
Originally posted by Lamprey

What version of SQL are you using?

Can you put your data is a consumable format (DDL & DML)? This link will help describe how to do that if you do not already know how:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2010-04-29 : 15:32:55
@sushant:

This looks like a presentation issue - why do you want to keep the data in the intial table & in the report?

if you absolutely must store values, can the Emp_Review_Comment filed be XML?

<Emp_Review>
 <Emp_Review_Comments>
  <Emp_Review_Comment>
   <Emp_Review_Comment_Date>2010-03-01</Emp_Review_Comment_Date>
   <Emp_Review_Comment>Some Comment</Emp_Review_Comment>
  </Emp_Review_Comment>
  <Emp_Review_Comment>
   <Emp_Review_Comment_Date>2010-03-10</Emp_Review_Comment_Date>
   <Emp_Review_Comment>Some New Comment</Emp_Review_Comment>
  </Emp_Review_Comment>
 </Emp_Review_Comments>
</Emp_Review>


*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site