Query help........Times out - 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

SQL Server Development (2000)

Query help........Times out

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-04-13 : 08:45:51

HEllo all,
I'm trying to run the query below. I'm really just trying to get the second COUNT to be returned in the same result set. The query runs fine until I add the sencond COUNT..after the second COUNT is added, it runs forever (10 minutes) befroe I have to stop it. I have duplicate tables in the query (named differently). I've highlighted them in green and purple. Any help would be greatly appreciated.

SELECT table_part_num.x_company,
COUNT(CASE WHEN (table_x_part_move_audit.x_from_site_id = 'Diagnostics') THEN table_x_part_move_audit.x_from_site_id ELSE ''END)AS [Processed Out Of Diag],
COUNT(CASE WHEN ( table_x_part_move_audit_1.x_To_site_id = 'Diagnostics') THEN table_x_part_move_audit_1.x_To_site_id ELSE ''END)AS [Received To Diag]
FROM table_x_diagnosis INNER JOIN
table_site_part (NOLOCK)ON table_x_diagnosis.x_diagnosis2site_part = table_site_part.objid INNER JOIN
table_mod_level (NOLOCK)ON table_site_part.site_part2part_info = table_mod_level.objid INNER JOIN
table_part_num (NOLOCK)ON table_mod_level.part_info2part_num = table_part_num.objid INNER JOIN
table_site (NOLOCK)ON table_site_part.site_part2site = table_site.objid INNER JOIN
table_x_part_move_audit (NOLOCK)ON table_site.objid = table_x_part_move_audit.x_from_site_objid INNER JOIN
table_site table_site_1 (NOLOCK)ON table_site_part.site_part2site = table_site_1.objid INNER JOIN
table_x_part_move_audit table_x_part_move_audit_1 (NOLOCK)ON table_site_1.objid = table_x_part_move_audit_1.x_to_site_objid
GROUP BY table_part_num.x_company
ORDER BY table_part_num.x_company

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:00:25

COUNT of a populated record and COUNT of an empty string equals to the same number.

SELECT		table_part_num.x_company,
		SUM(CASE WHEN table_x_part_move_audit.x_from_site_id = 'Diagnostics' THEN 1 ELSE 0 END) AS [Processed Out Of Diag],
		SUM(CASE WHEN table_x_part_move_audit_1.x_To_site_id = 'Diagnostics' THEN 1 ELSE 0 END) AS [Received To Diag]
FROM		table_x_diagnosis
INNER JOIN	table_site_part (NOLOCK) ON table_x_diagnosis.x_diagnosis2site_part = table_site_part.objid
INNER JOIN	table_mod_level (NOLOCK) ON table_site_part.site_part2part_info = table_mod_level.objid
INNER JOIN	table_part_num (NOLOCK) ON table_mod_level.part_info2part_num = table_part_num.objid
INNER JOIN	table_site (NOLOCK) ON table_site_part.site_part2site = table_site.objid
INNER JOIN	table_x_part_move_audit (NOLOCK) ON table_site.objid = table_x_part_move_audit.x_from_site_objid
INNER JOIN	table_site table_site_1 (NOLOCK) ON table_site_part.site_part2site = table_site_1.objid
INNER JOIN	table_x_part_move_audit table_x_part_move_audit_1 (NOLOCK) ON table_site_1.objid = table_x_part_move_audit_1.x_to_site_objid
GROUP BY	table_part_num.x_company
ORDER BY	table_part_num.x_company

Peter Larsson
Helsingborg, Sweden

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-04-17 : 15:52:08

Thank you very much!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources