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)
 Complex grouping Query

Author  Topic 

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 00:01:53
[code]

I have actually raised similar type of issue before but now the requirement have changed and its complex.

CREATE table tbl_emp_comments
(
emp_comment_id [bigint] IDENTITY (1, 1) NOT NULL ,
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,'001','052',NULL,'N' UNION ALL
SELECT '07675688',1,7,'DR8','2005-03-11 10:43:00.000','LOS-APP: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,'043',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,'020',NULL,NULL,'N'


CREATE table type
(
type_id INT NOT NULL,
type_description
)

INSERT INTO type
SELECT 43,'Emp Comment Source' UNION
SELECT 44, 'Emp Contacted Codes'

CREATE table type_code
(
type_code_id INT NOT NULL,
type_id INT NOT NULL,
type_code char(10) not null,
type_description VARCHAR(100) NOT NULL
)

INSERT INTO type_code
SELECT 1523,43,'EM-APP','Emp Application' UNION
SELECT 1524,43,'LOS-APP','LOS Application' UNION
SELECT 1525,43,'FA','FA Application' UNION

SELECT 1652,44,'001','Contacted Home' UNION
SELECT 1653,44,'020','Contacted Office' UNION
SELECT 1654,44,'030','Contacted Wife at Home' UNION
SELECT 1655,44,'043','Contacted Wife at Office' UNION
SELECT 1656,44,'055','Contacted Third Party'

CREATE table tbl_comment_history
(
emp_comment_id [bigint] IDENTITY (1, 1) NOT NULL ,
emp_number varchar(15) 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,
comment_source_type_code_id INT not null,
contacted_flag CHAR(1) NOT NULL
)


1. I need to group these comments based on emp_proc_id and emp_comment_date for a emp_number and bulk_download_id from tbl_emp_comments.
2. Now when I group the comments starting with EMP-APP: should have the type_code_id =1523 for the comment_source_type_code_id
similarly the group with LOS-APP: should have type_code_id = 1524 for the comment_source_type_code_id all the others other than
these shuld be 1525 which is FA

3. Now if any of the group has contact_code in the type_code where type_id=44 then the contacted_flag should be 'Y'
else it should be 'N'

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

[/code]

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 00:15:46
The insert should be to tbl_comment_history table
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-20 : 00:22:59
You'll need to post the data again because you say the insert should be into the history table, but the insert you gave isn't for the history table.

Also, when you say 1. I need to group these comments based on emp_proc_id and emp_comment_date for a emp_number and bulk_download_id from tbl_emp_comments. Do you mean you want to group by the emp_proc_id and emp_comment_date and filter by bulk_download_id from tbl_emp_comments? And if the insert should be into the history table, then what should the query be on, in this point you mention columns from the comments table?
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 00:38:01
The insert which I mentioned is from tbl_emp_comments to tbl_comment_history based on the above mentioned conditions.The select query based on the group should be from tbl_emp_comments and should be inserted into tbl_comment_history table based on the logic I have mentioned.
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 00:49:41
Aged Yak Warrior hope this explains.Please let me know if you need more details
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 01:05:50
All the comments in tbl_emp_comments table should be available in tbl_comment_history with the contacted_flag and source_type_code_id information.
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 03:53:25
The grouping will be like this :

SELECT c.emp_number,min(t.type_code_id),c.emp_proc_id, c.emp_comment_date
FROM tbl_emp_comments
c left join type_code t
on c.contact_code = t.type_code

WHERE
bulk_download_id = 1
group by c.emp_number, c.emp_proc_id, c.emp_comment_date

But to get the contacted_flag and source_type_code_id to the specific group based on the logic is finding to be tough
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-20 : 11:11:00
Try this

SELECT emp_number, RawData.type_code_id, emp_proc_id,  emp_comment_date,
	CASE type_id WHEN 44 THEN 'Y' ELSE 'N' END as Contacted
FROM 
(SELECT c.emp_number,min(t.type_code_id) as type_code_id,c.emp_proc_id, c.emp_comment_date
FROM tbl_emp_comments
c left join type_code t
on c.contact_code = t.type_code

WHERE 
bulk_download_id = 1
group by c.emp_number, c.emp_proc_id, c.emp_comment_date) as RawData
LEFT OUTER JOIN type_code ON RawData.type_code_id = type_code.type_code_id
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 14:57:41
Here tbl_emp_comments has got 8 records and my insert to tbl_comment_history should have 8 records as well.In the above query the source_type_code_id is missing where emp_comment_line1 is having starting with "EMP-APP:" then the source is 1523 and if it is "LOS-APP:" then 1524 and all other group is 1525.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2006-11-20 : 15:09:17
Do us a favor though...and it's a very good post, but what does the expected results look like

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 16:26:17
[code]
This is the result I want out of the select query :
emp_number emp_proc_id emp_comment_date emp_comment_line1 emp_comment_line2 contact_code reason_code response_code contacted_flag comment_source_type_code_id
--------------- ----------- ------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- ------------- ------------------ ----------------------------
07675688 KTY 2005-03-11 09:52:00.000 ORIGINAL OWNER CONDITION ON 091206 S83R NULL 082 NULL NULL N 1525
07675688 DR8 2005-03-11 10:43:00.000 MR. CALLED TRAN TO OTHER NULL 001 052 NULL Y 1524
07675688 DR8 2005-03-11 10:43:00.000 LOS-APP:BORR TRAN FROM LIASON BEC HE WANTED SOMEONE IN AMER NULL 992 NULL NULL Y 1524
07675688 L78 2005-03-11 11:14:00.000 3RD PARTY CLD NULL 039 NULL NULL N 1523
07675688 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 1523
07675688 KTY 2005-04-13 07:52:00.000 MRS STEVENS CALLED WANTED INFO ADSVED NO AUTH GAVE NULL 033 NULL NULL N 1525
07675688 L78 2005-04-13 08:12:00.000 QC COMPLETE NULL 043 NULL NULL Y 1523
07675688 L78 2005-04-13 08:12:00.000 EMP-APP:TRUDY WITH AMBER FINANCIALS CALLED UP ADVD NO LOA NULL 020 NULL NULL Y 1523







[/code]
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-20 : 16:56:21
I'm sorry, but now I'm even more confused about what you want.

You said you need to group the comments, but the example you gave of the output you want is not grouped.
You still haven't given any rows for the history table, but your example shows a column taken from the history table.
Your example shows the contacted_flag as Y for a row that has contact_code 992, but you said it must only be Y for type_id = 44 and there is no type_code for 992, so how is type = 44.

I suggest you post the data again including the history data and the query that you have so far. Also when you post the data, try to make the columns narrower, it's very hard to read your sample ouput because the columns are so wide.
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 17:29:03
Its little confusing :

I want to insert the same number of records in the source to the destination but inbetween
we will have to group it identify the source_code_type_id and contacted_flag since
If we have emp_number with same emp_proc_id and emp_comment_date that means that it is a single
call information the user has made.Now for eg:

emp_proc_id 	 emp_comment_date	  emp_comment_line_1  							contact_code
L78 		2005-03-11 11:14:00.000	3RD PARTY CLD								039	
L78 		2005-03-11 11:14:00.000	EMP-APP:JOHN W/1ST AMERICAN CREDCO REQ ACCOUNT INFO.. ADV T		899

Here it happend at the same time.Since the one of the comment start with
EMP-APP: we know that the source for these 2 comments is EMP-APP (which is 1523 source_code_type_id)
Now to identify whether its contacted_flag we should take the contact_code of the
first emp_comment_line_1 and check whether if its available in the tbl_type_code where type_id=44
and if its available then both these will be marked as contacted since its one single call.In the above case
its not so it will be 'N'


Similar is the case with LOSS-APP: also and

if nothing is there then it should be FA but the contact_code logic applies the same.

Please let me know if you need more explanation
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 17:56:42
This is one of the query I could group and build for the source "LOS-APP" but Iam not able make it for all the cases.Now should i insert this into a temp table (since we have only one record after grouping) and join back to get all the records based on the emp_proc_id and emp_comment_date

SELECT
ddni.emp_number,
ddni.emp_proc_id,
ddni.emp_comment_date,
MIN(1524) AS comment_source_type_code_id,
CASE
WHEN MIN (tc.type_code) IS NULL THEN 'N'
ELSE
'Y'
END AS contacted_flag
FROM tbl_emp_comments ddni
LEFT JOIN type_code tc ON
ddni.contact_code=tc.type_code AND
tc.type_id=44
WHERE
EXISTS
(
SELECT * FROM tbl_emp_comments ddnix
WHERE ddnix.emp_number = ddni.emp_number AND
ddnix.emp_proc_id = ddni.emp_proc_id AND
ddnix.emp_comment_line1 LIKE 'LOS-APP:%'
) AND
ddni.emp_number='07675688' AND
ddni.bulk_download_id = 1

GROUP BY ddni.emp_number, ddni.emp_proc_id,ddni.emp_comment_date
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 18:52:48
snSQL can you please help me with this
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-20 : 19:12:59
I use this code and got the results but is there any way i can optimize this to peform better with the same results :


DECLARE @coll_table TABLE 
(
emp_number VARCHAR(13),emp_proc_id CHAR(3),emp_comment_date datetime,
comment_source_type_code_id INT,contacted_flag CHAR(1)
)
INSERT INTO @coll_table 
(
	emp_number,
	emp_proc_id,
	emp_comment_date,
	comment_source_type_code_id,
	contacted_flag
)
SELECT 
	ddni.emp_number,
	ddni.emp_proc_id,
	ddni.emp_comment_date,
	MIN(1523) AS comment_source_type_code_id,
	CASE
		WHEN MIN (tc.type_code) IS NULL THEN 'N'
		ELSE
			'Y'
	END AS contacted_flag
FROM 	tbl_emp_comments ddni
			LEFT JOIN type_code tc ON
						ddni.contact_code=tc.type_code AND
						tc.type_id=44
WHERE
	EXISTS
		(
		SELECT * FROM tbl_emp_comments ddnix
		WHERE	ddnix.emp_number	= ddni.emp_number AND
			ddnix.emp_proc_id	= ddni.emp_proc_id AND
			ddnix.emp_comment_line1  LIKE 'EMP-APP:%'
	) AND
	ddni.emp_number='07675688' AND
	ddni.bulk_download_id = 1  

GROUP BY ddni.emp_number, ddni.emp_proc_id,ddni.emp_comment_date

UNION ALL

SELECT 
	ddni.emp_number,
	ddni.emp_proc_id,
	ddni.emp_comment_date,
	MIN(1524) AS comment_source_type_code_id,
	CASE
		WHEN MIN (tc.type_code) IS NULL THEN 'N'
		ELSE
			'Y'
	END AS contacted_flag
FROM 	tbl_emp_comments ddni
			LEFT JOIN type_code tc ON
						ddni.contact_code=tc.type_code AND
						tc.type_id=44
WHERE
	EXISTS
		(
		SELECT * FROM tbl_emp_comments ddnix
		WHERE	ddnix.emp_number	= ddni.emp_number AND
			ddnix.emp_proc_id	= ddni.emp_proc_id AND
			ddnix.emp_comment_line1  LIKE 'LOS-APP:%' 
	) AND
	ddni.emp_number='07675688' AND
	ddni.bulk_download_id = 1  

GROUP BY ddni.emp_number, ddni.emp_proc_id,ddni.emp_comment_date

UNION 

SELECT 
	ddni.emp_number,
	ddni.emp_proc_id,
	ddni.emp_comment_date,
	MIN(1525) AS comment_source_type_code_id,
	CASE
		WHEN MIN (tc.type_code) IS NULL THEN 'N'
		ELSE
			'Y'
	END AS contacted_flag
FROM 	tbl_emp_comments ddni
			LEFT JOIN type_code tc ON
						ddni.contact_code=tc.type_code AND
						tc.type_id=44
WHERE
	NOT EXISTS
		(
		SELECT * FROM tbl_emp_comments ddnix
		WHERE	ddnix.emp_number	= ddni.emp_number AND
			ddnix.emp_proc_id	= ddni.emp_proc_id AND
			(ddnix.emp_comment_line1  LIKE 'LOS-APP:%' OR ddnix.emp_comment_line1  LIKE 'EMP-APP:%')
	) AND
	ddni.emp_number='07675688' AND
	ddni.bulk_download_id = 1  

GROUP BY ddni.emp_number, ddni.emp_proc_id,ddni.emp_comment_date

INSERT INTO tbl_comment_history
(
emp_number,
emp_proc_id,
emp_comment_date,
emp_comment_line1,
emp_comment_line2,
contact_code,
reason_code,
response_code,
comment_source_type_code_id,
contacted_flag
)
SELECT 
emc.emp_number,
emc.emp_proc_id,
emc.emp_comment_date,
emc.emp_comment_line1,
emc.emp_comment_line2,
emc.contact_code,
emc.reason_code,
emc.response_code,
tmp.comment_source_type_code_id,
tmp.contacted_flag
FROM 
	@coll_table tmp
		INNER JOIN tbl_emp_comments emc ON
						tmp.emp_number =emc.emp_number AND
						tmp.emp_proc_id = emc.emp_proc_id AND
						tmp.emp_comment_date = emc.emp_comment_date


select emp_proc_id,emp_comment_date,emp_comment_line1,contact_code,comment_source_type_code_id,contacted_flag from tbl_comment_history
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-21 : 01:48:47
Please help me to optimize or an alternate query for the above logic
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts
Posted - 2006-11-21 : 04:35:07
Any idea
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-11-21 : 04:50:16
Are emp_number and emp_proc_id together an unique key for the records in tbl_emp_comments table?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-11-21 : 05:07:44
This might be worth a try
DECLARE @coll_table TABLE 
	(
		emp_number VARCHAR(13),
		emp_proc_id CHAR(3),
		emp_comment_date datetime,
		comment_source_type_code_id INT,
		contacted_flag CHAR(1)
	)

INSERT INTO	@coll_table 
		(
			emp_number,
			emp_proc_id,
			emp_comment_date,
			comment_source_type_code_id,
			contacted_flag
		)
SELECT		q.Emp_Number,
		q.Proc_ID,
		q.Emp_Comment_Date,
		q.Comment_Source_Type_Code_ID,
		MIN(q.Contacted_Flag)
FROM		(
			SELECT		ddni.emp_number,
					ddni.emp_proc_id,
					ddni.emp_comment_date,
					CASE
						WHEN ddni.emp_comment_line1 LIKE 'EMP-APP:%' THEN 1523
						WHEN ddni.emp_comment_line1 LIKE 'LOS-APP:%' THEN 1524
						ELSE 1525
					END AS comment_source_type_code_id,
					CASE
						WHEN tc.type_code IS NULL THEN 'N'
						ELSE 'Y'
					END AS contacted_flag
			FROM		tbl_emp_comments ddni
			LEFT JOIN	type_code tc ON ddni.contact_code = tc.type_code AND tc.type_id = 44
			WHERE		ddni.emp_number = '07675688'
					AND ddni.bulk_download_id = 1
		) q
GROUP BY	q.Emp_Number,
		q.Proc_ID,
		q.Emp_Comment_Date,
		q.Comment_Source_Type_Code_ID

INSERT INTO	tbl_comment_history
		(
			emp_number,
			emp_proc_id,
			emp_comment_date,
			emp_comment_line1,
			emp_comment_line2,
			contact_code,
			reason_code,
			response_code,
			comment_source_type_code_id,
			contacted_flag
		)
SELECT		emc.emp_number,
		emc.emp_proc_id,
		emc.emp_comment_date,
		emc.emp_comment_line1,
		emc.emp_comment_line2,
		emc.contact_code,
		emc.reason_code,
		emc.response_code,
		tmp.comment_source_type_code_id,
		tmp.contacted_flag
FROM		@coll_table tmp
INNER JOIN	tbl_emp_comments emc ON tmp.emp_number = emc.emp_number
			AND tmp.emp_proc_id = emc.emp_proc_id
			AND tmp.emp_comment_date = emc.emp_comment_date

SELECT		emp_proc_id,
		emp_comment_date,
		emp_comment_line1,
		contact_code,
		comment_source_type_code_id,
		contacted_flag
FROM		tbl_comment_history


Peter Larsson
Helsingborg, Sweden
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