Return only certain results - 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

General SQL Server Forums

New to SQL Server Programming

Return only certain results

Author

Topic

almaler
Starting Member

4 Posts

Posted - 2015-03-13 : 10:34:57

I am trying to get this query to the point where it will return only results that meet specific conditions and I'm needing some assistance. Here is the current query I have:


use mdb
SELECT call_req.ref_num, prob_ctg.sym, act_type.sym, act_log.description
	
  FROM call_req
    INNER JOIN prob_ctg
     ON call_req.category = prob_ctg.persid
	 INNER join act_log
	 ON call_req.persid = act_log.call_req_id
	 INNER JOIN act_type
	 ON act_log.type = act_type.code

where prob_ctg.sym like 'rd1.%' and prob_ctg.auto_assign=1 and call_req.active_flag=0 ORDER BY call_req.ref_num, act_log.time_stamp DESC

This produces results as follows:

I3835493FY15 RD1.Desktop Transfer Transfer Asignee from " to Jordana, Jose"
I3835493FY15 RD1.Desktop Close Status changed from 'Resolved' to 'Closed'
I3835493FY15 RD1.Desktop Close Status changed from 'Open' to 'Resolved'
I3835493FY15 RD1.Desktop Event Occurred AHD05447 No eligible Request Locations
I3835493FY15 RD1.Desktop Initial Create new request/incident/problem
I3835494FY15 RD1.Desktop Transfer Transfer Asignee from " to Smith, Abbott" Transfer Group from 'RD1 IT Service Center' to 'NSD Region 1'
I3835494FY15 RD1.Desktop Close Status changed from 'Resolved' to 'Closed'
I3835494FY15 RD1.Desktop Close Status changed from 'Open' to 'Resolved'
I3835494FY15 RD1.Desktop Event Occurred AHD05447 No eligible Request Locations
I3835494FY15 RD1.Desktop Initial Create new request/incident/problem

This query gives me the total number of records that meet my overall criteria, but what I need to have is a count of the total distinct number of call_req.ref_num and then I need a count of the number of records where act_type.code = TR and the text of act_log.description contains text including "Transfer Group From" and then I need a count of the records where act_type.code = EVT and the text of act_log.description contains text including "AHD054".

Any and all assistance is greatly appreciated! Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 10:48:00

Try this: note that I can't run it since I don't have your table definitions or data.


with cte as
(
	SELECT call_req.ref_num, prob_ctg.sym, act_type.sym, act_log.description
	
	  FROM call_req
		INNER JOIN prob_ctg
		 ON call_req.category = prob_ctg.persid
		 INNER join act_log
		 ON call_req.persid = act_log.call_req_id
		 INNER JOIN act_type
		 ON act_log.type = act_type.code

	where prob_ctg.sym like 'rd1.%' and prob_ctg.auto_assign=1 and call_req.active_flag=0 and (act_type.code = 'TR' or act_type.code = 'EVT')
)

select (select count(ref_num) from cte) as count_ref_num, 
       (select count(*) from cte where code = 'TR' and [description] like '%Transfer Group From%') as count_code_tr,
       (select count(*) from cte where code = 'EVT' and [description] like '%AHD054%') as count_code_evt

almaler
Starting Member

4 Posts

Posted - 2015-03-13 : 11:34:38

Thanks for the suggestion! I did try that and received the following message:

Msg 8156, Level 16, State 1, Line 1
The column 'sym' was specified multiple times for 'cte'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 11:45:26

Yeah, you need to resolve that. Give an alias for the second occurrence of the sym column (e.g. act_type.sym as at_sym)

almaler
Starting Member

4 Posts

Posted - 2015-03-13 : 12:05:45

Did that..Message now is Msg 207, Level 16, State 1, Line 17
Invalid column name 'code'.

I tried putting in the actual name of act_type.code, but then got:

Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "act_type.code" could not be bound.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 12:07:23

The cte doesn't return the code column. You need to add it there.

almaler
Starting Member

4 Posts

Posted - 2015-03-13 : 12:51:34

Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources