April 8, 2014 at 2:55 pm
Im trying to get 2 coulm with this syntax
I want that the t1.assyst_usr_sc will get evrthiny with the following names: 'NOCCHANGELOGGER', 'LOGGER', 'MAIL'
AND
I want that everthing what not in the following names coming get the name: something else
I have the following syntax:
Select DISTINCT t1.assyst_usr_n, inc_cat_sc, count(*) as total, t1.assyst_usr_sc as l, t2.assyst_usr_sc as k
from incident
join assyst_usr as t1
on incident.assyst_usr_id = t1.assyst_usr_id
join assyst_usr as t2
on incident.assyst_usr_id = t2.assyst_usr_id
join cat_proces_mngt
on incident.inc_cat_id = cat_proces_mngt.inc_cat_id
join serv_dept
on incident.serv_dept_id = serv_dept.serv_dept_id
where type_id in (1,2,3) and
t1.assyst_usr_sc IN
('NOCCHANGELOGGER', 'LOGGER', 'MAIL')
OR
t2.assyst_usr_sc NOT IN
('NOCCHANGELOGGER', 'LOGGER', 'MAIL')
group by t1.assyst_usr_n, inc_cat_sc, t1.assyst_usr_sc, t2.assyst_usr_sc
The resuls is not good:
INC_CAT_SC Total L K
ID-EXTREME2ACSCHULTACSCHULT
ID-JUNIPER1ACSCHULTACSCHULT
ID-WIRELESS15ACSCHULTACSCHULT
STCD-RFI1ACSCHULTACSCHULT
ID-ALGEMEEN8LOGGER LOGGER
IV-AVAYA CM185LOGGER LOGGER
I get the same resultat but i need t1 as all the items in the IN clause and the other is the rest of the times whats not in in the clause is..
How can i fix it
April 8, 2014 at 3:22 pm
Awfully sparse on details. Without some actual details it is pretty tough to guess.
I suspect the problem is because you are using an OR between your where predicates.
where type_id in (1,2,3)
and
(
t1.assyst_usr_sc IN ('NOCCHANGELOGGER', 'LOGGER', 'MAIL')
OR
t2.assyst_usr_sc NOT IN ('NOCCHANGELOGGER', 'LOGGER', 'MAIL')
)
You are missing the outer () which means your order of precedence is screwy. Formatting your code goes a long way to being able to see stuff like this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply