Really Complex Complex Query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Really Complex Complex Query

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-21 : 17:55:31

[code]

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 :

[/code]

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-22 : 02:25:41

Iam really lost to get a query out of this.Please help

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-22 : 17:28:11

The 2 parameters emp_number and bulk_download_id are the only input parameters.How can I utilize the cursor with this query

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-22 : 20:25:30

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

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-22 : 21:52:45

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 22:16:37

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:%'
	)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 22:20:35

"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 ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-22 : 22:32:05

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 22:45:09

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 ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-22 : 23:05:52

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 23:36:25

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 23:48:31

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

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-23 : 00:08:34

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-23 : 00:11:55

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 ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-23 : 00:18:08

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-23 : 00:28:55

Does the contact_code and reason_code in tbl_emp_comments exists in the tbl_contacts and tbl_reasons ?

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-10-23 : 00:39:17

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-23 : 00:46:58

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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources