SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Really Complex Complex Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/21/2006 :  17:55:31  Show Profile  Reply with Quote


CREATE table tbl_emp_comments
(
emp_number varchar(15) not null,
bulk_download_id INT,
emp_proc_id char(3) not null,
emp_comment_date datetime not null,
emp_comment_line1 varchar(500) not null,
emp_comment_line2 varchar(500) NULL,
contact_code char(3) null,
reason_code char(3) null,
response_code char(3) null,
emp_processed_flag char(1) not null
)

INSERT INTO tbl_emp_comments
(
emp_number,bulk_download_id,emp_proc_id,emp_comment_date,emp_comment_line1,emp_comment_line2,contact_code,reason_code,response_code,emp_processed_flag
)
SELECT '07675688',1,'KTY','2005-03-11 09:52:00.000','ORIGINAL OWNER       CONDITION ON 091206   S83R',NULL,'082',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,'DR8','2005-03-11 10:43:00.000','MR. CALLED   TRAN TO OTHER',NULL,'002','052',NULL,'N' UNION ALL
SELECT '07675688',1,'DR8','2005-03-11 10:43:00.000','BORR TRAN FROM LIASON BEC HE WANTED SOMEONE IN AMER',NULL,'992',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,'L78','2005-03-11 11:14:00.000','3RD PARTY CLD',NULL,'039',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,'L78','2005-03-11 11:14:00.000','EMP-APP:JOHN W/1ST AMERICAN CREDCO REQ ACCOUNT INFO.. ADV T',NULL,'899',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,'KTY','2005-04-13 07:52:00.000','MRS STEVENS CALLED WANTED INFO ADSVED NO AUTH GAVE',NULL,'033',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,'L78','2005-04-13 8:12:00.000','QC COMPLETE',NULL,'858',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,'L78','2005-04-13 8:12:00.000','EMP-APP:TRUDY WITH AMBER FINANCIALS CALLED UP ADVD NO LOA',NULL,'877',NULL,NULL,'N' 


CREATE table tbl_contacts

(
contact_id INT NOT NULL,
contact_code char(3) not null,
)

INSERT INTO tbl_contacts
(contact_id,contact_code)
SELECT 1,082 UNION 
SELECT 2,002 UNION 
SELECT 3,039 UNION 
SELECT 4,033 UNION 
SELECT 5,877  


CREATE table tbl_reasons

(
reason_id INT NOT NULL,
reason_code char(3) not null,
)

INSERT INTO tbl_reasons
(reason_id,reason_code)
SELECT 1,052 UNION 
SELECT 2,092 UNION 
SELECT 3,234 UNION 
SELECT 4,367 



CREATE table tbl_emp_call

(
call_reference_id INT IDENTITY(1,1),
emp_number varchar(15) not null,
contact_id INT NULL,
reason_id INT null,
)



CREATE table tbl_call_comments

(
emp_number varchar(15),
call_reference_id int not null,
comments varchar(6000) not null,
create_date datetime not null,
create_user varchar(20) not null
)



tbl_emp_comments table has got employee comments on a daily basis.Now I need convert these comments on the basis of
a call.First I need to insert the records into the tbl_emp_call with the corressponding contact_id and reason_id
for a call :
Now the challenges :
1. I need to group these based on emp_proc_id and comment_date for a emp_number and bulk_download_id
 eg: Now the emp_proc_id = DR8 with  emp_comment_date =2005-03-11 10:43:00.000 has 2 comments for a single call
     out of this one comment is generated based on the contact code and the other is a free form comment.
    Now when I group I want avoid the group which has comment starting with "EMP-APP"

Lets take one group:
SELECT '07675688',1,'DR8','2005-03-11 10:43:00.000','MR. CALLED   TRAN TO OTHER',NULL,'002','052',NULL,'N' UNION ALL
SELECT '07675688',1,'DR8','2005-03-11 10:43:00.000','BORR TRAN FROM LIASON BEC HE WANTED SOMEONE IN AMER',NULL,'992',NULL,NULL,'N' UNION ALL

Now I insert one record to tbl_emp_call with emp_number = 07675688,contact_id=2,reason_code=1.
Though I have a contact_code=952 for the second one I will take only the first available contact code from the tbl_contacts
and same is the case with reason code

Now after getting the call reference_id I need to insert the comments into tbl_call_comments.

Should I use any cursor to loop through and check these conditions :





sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/22/2006 :  02:25:41  Show Profile  Reply with Quote
Iam really lost to get a query out of this.Please help
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/22/2006 :  17:28:11  Show Profile  Reply with Quote
The 2 parameters emp_number and bulk_download_id are the only input parameters.How can I utilize the cursor with this query
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/22/2006 :  20:25:30  Show Profile  Reply with Quote
I think you are not getting any help because you did such a poor job of explaining what you are trying to do and what particular thing you need help with. Also, the formatting of the post makes it hard to read.

Basically, you just made it too much work to try to help you.






CODO ERGO SUM
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/22/2006 :  21:52:45  Show Profile  Reply with Quote
Thanks for the response : Sorry I could not explain it properly, Hope this will help

Note : There is one more field in tbl_emp_comments called emp_occurence INT and its unique for a bulk_download_id and emp_number

Please use this script :


CREATE table tbl_emp_comments
(
emp_number varchar(15) not null,
bulk_download_id INT NOT NULL,
emp_occurence INT NOT NULL,
emp_proc_id char(3) not null,
emp_comment_date datetime not null,
emp_comment_line1 varchar(500) not null,
emp_comment_line2 varchar(500) NULL,
contact_code char(3) null,
reason_code char(3) null,
response_code char(3) null,
emp_processed_flag char(1) not null
)

INSERT INTO tbl_emp_comments
(
emp_number,bulk_download_id,emp_occurence,emp_proc_id,emp_comment_date,emp_comment_line1,emp_comment_line2,contact_code,reason_code,response_code,emp_processed_flag
)
SELECT '07675688',1,5,'KTY','2005-03-11 09:52:00.000','ORIGINAL OWNER CONDITION ON 091206 S83R',NULL,'082',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,6,'DR8','2005-03-11 10:43:00.000','MR. CALLED TRAN TO OTHER',NULL,'002','052',NULL,'N' UNION ALL
SELECT '07675688',1,7,'DR8','2005-03-11 10:43:00.000','BORR TRAN FROM LIASON BEC HE WANTED SOMEONE IN AMER',NULL,'992',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,8,'L78','2005-03-11 11:14:00.000','3RD PARTY CLD',NULL,'039',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,9,'L78','2005-03-11 11:14:00.000','EMP-APP:JOHN W/1ST AMERICAN CREDCO REQ ACCOUNT INFO.. ADV T',NULL,'899',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,10,'KTY','2005-04-13 07:52:00.000','MRS STEVENS CALLED WANTED INFO ADSVED NO AUTH GAVE',NULL,'033',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,11,'L78','2005-04-13 8:12:00.000','QC COMPLETE',NULL,'858',NULL,NULL,'N' UNION ALL
SELECT '07675688',1,12,'L78','2005-04-13 8:12:00.000','EMP-APP:TRUDY WITH AMBER FINANCIALS CALLED UP ADVD NO LOA',NULL,'877',NULL,NULL,'N'

1. I need to group these comments based on emp_proc_id and comment_date for a emp_number and bulk_download_id.I need to avoid the group that has comments starting with "EMP-APP:"
IF I use something like this :
SELECT emp_proc_id,emp_comment_date FROM tbl_emp_comments
WHERE emp_comment_line1 NOT LIKE 'EMP-APP:%'
GROUP BY emp_proc_id,emp_comment_date

- Here the problem is emp_occurence = 8 and 11 are coming in the select statement which should not come since emp_occurence = 9 and 12 has got emp_comment_line1 with "EMP-APP"

Ideal result should return the records with emp_occurence = 5,6,7 and 10

Step2 :
Now these records need to be inserted into tbl_emp_call in the following way :
emp_occurence 5 should be one single call,10 should be one single call and 6 with 7 should be one call
While inserting, the contact_code and reason_code should be joined with the tbl_contacts and tbl_reasons to get the contact_id and reason_id
Incase of emp_occurence 6 and 7 It should take the contact_id and reason_id of the 1st record

Step3: Now after inserting into table tbl_emp_call I will get the call_reference_id and with that I will insert the comments to the tbl_call_comments table.

Please let me know if this helps or I can give more explanation.Thanks for all the help








Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/22/2006 :  22:16:37  Show Profile  Reply with Quote
Step1 :

SELECT 	emp_number, emp_proc_id, emp_comment_date, emp_occurence, emp_comment_line1
FROM 	tbl_emp_comments c
WHERE 	not exists 
	(
		select	*
		from	tbl_emp_comments x
		where	x.emp_number	= c.emp_number
		and	x.emp_proc_id	= c.emp_proc_id
		and	x.emp_comment_line1 LIKE 'EMP-APP:%'
	)



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/22/2006 :  22:20:35  Show Profile  Reply with Quote
"emp_occurence 5 should be one single call,10 should be one single call and 6 with 7 should be one call"
Please explain more in detail on this. Why 6 & 7 are consider single call and why not 5 & 10 also ?


KH

Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/22/2006 :  22:32:05  Show Profile  Reply with Quote
Step1 is successful.Thanks a lot :

From this selection we have 5 records :
Now when i said emp_occurence 5 should be a single call and 10 another single call that means this 2 are idenitfied as 2 separate calls since they don't have any other comments at the same time with the same emp_proc_id
where as 6 and 7 the emp_comment_date and proc_id are the same so this should be a single call

Now when we insert into tbl_emp_call there will be only 3 inserts and with that 3 call_referene_id I will insert 4 comments into tbl_call_comments basically 6 and 7 will have the same call_reference_id






Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/22/2006 :  22:45:09  Show Profile  Reply with Quote
Step 2 :

insert into tbl_emp_call(emp_number, contact_id, reason_id)
SELECT 	c.emp_number, min(t.contact_id), min(reason_id)
FROM 	tbl_emp_comments c inner join tbl_contacts t
	on	c.contact_code	= t.contact_code
	left join tbl_reasons r
	on	c.reason_code	= r.reason_code
WHERE 	not exists 
	(
		select	*
		from	tbl_emp_comments x
		where	x.emp_number	= c.emp_number
		and	x.emp_proc_id	= c.emp_proc_id
		and	x.emp_comment_line1 LIKE 'EMP-APP:%'
	)
group by c.emp_number, c.emp_proc_id, c.emp_comment_date


How do you want to deal with reason_code of NULL in tbl_emp_comments ?


KH

Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/22/2006 :  23:05:52  Show Profile  Reply with Quote
I should use : c left join tbl_contacts t because again the contact codes and reason codes can be null.
If use Left join i get 3 records Now its step 3.Iam really happy..Thanks for the support.Great solutions Khatan :

Now we need to use the corressponding call_referene_id from tbl_emp_call and comments from tbl_emp_comments to insert into
tbl_call_comments table.

In the result table tbl_call_comments I will have 4 comments. 6 and 7 will have the same call_reference_id but 2 different comments
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/22/2006 :  23:36:25  Show Profile  Reply with Quote
To insert data into tbl_call_comments table, you will need the call_reference_id from tbl_emp_call. But you will have problem identify the correspondant comments from tbl_emp_call table as the contact_id & reason_id can be null in the tbl_emp_call table



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/22/2006 :  23:48:31  Show Profile  Reply with Quote
Or maybe you should have emp_proc_id, emp_comment_date column in the tbl_emp_call table ? As records in tbl_emp_call are identified by combinition of emp_number, emp_proc_id and emp_comment_date.

quote:

insert into tbl_emp_call(emp_number, contact_id, reason_id)
SELECT 	c.emp_number, min(t.contact_id), min(reason_id)
FROM 	tbl_emp_comments c inner join tbl_contacts t
	on	c.contact_code	= t.contact_code
	left join tbl_reasons r
	on	c.reason_code	= r.reason_code
WHERE 	not exists 
	(
		select	*
		from	tbl_emp_comments x
		where	x.emp_number	= c.emp_number
		and	x.emp_proc_id	= c.emp_proc_id
		and	x.emp_comment_line1 LIKE 'EMP-APP:%'
	)
group by c.emp_number, c.emp_proc_id, c.emp_comment_date




KH

Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/23/2006 :  00:08:34  Show Profile  Reply with Quote
Here When I use the join Iam not getting any of the contact_id and reason_id
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/23/2006 :  00:11:55  Show Profile  Reply with Quote
quote:
Originally posted by sqllearner

Here When I use the join Iam not getting any of the contact_id and reason_id


Sorry... what do you mean ?


KH

Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/23/2006 :  00:18:08  Show Profile  Reply with Quote
The select statement is not returning me any values :
If I use left join for tbl_contacts then I get 3 records but none of the records have contact_id and reason_id values all are coming as NULL values
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/23/2006 :  00:28:55  Show Profile  Reply with Quote
Does the contact_code and reason_code in tbl_emp_comments exists in the tbl_contacts and tbl_reasons ?


KH

Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 10/23/2006 :  00:39:17  Show Profile  Reply with Quote
Sorry the codes inside was 82 instead of 082 and Now it works..Thanks a lot for help.Iam going to code all these to a stored procedure level.

I will inform you all if I run into issue.

Thanks a lot for the help.This was a greate solution.I never thought I will be able to solve this.Thanks khtan.Sqlteam is a great website for sql solutions.

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 10/23/2006 :  00:46:58  Show Profile  Reply with Quote
Oh. Forgot to highlight this to you. You declare as char(3) but in the insert you specify as 082 and not '082'. SQL Server takes 082 as integer value of 82 so it is actually 82 get inserted into the table not '082'. You should specify as string

CREATE table tbl_contacts
(
contact_id INT NOT NULL,
contact_code char(3) not null,
)

INSERT INTO tbl_contacts
(contact_id,contact_code)
SELECT 1,'082' UNION 
SELECT 2,'002' UNION 
SELECT 3,'039' UNION 
SELECT 4,'033' UNION 
SELECT 5,'877'  



KH

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000