SQL query assistance to only retrieve projects meeting specific condition

[vtleogal]

Hi, I 've tried as many different SQL commands as I can and I cannot get the query to show me what I want.

 

14.2 SQL 2012 (on premise )

 

We have a custom attribute that holds a numeric number from a custom view. This number can be associated to 1 or many Clarity project ids.

I only want the query to retrieve the custom attribute number if ALL associated Clarity projects are inactive.

 

Example:

 

Custom attribute (xproject)  = 12345

This value of 12345 is associated to the following Clarity projects:

project A - active

Project B- inactive

Project C - inactive

 

 

Custom attribute (xproject) = 55555

This one is associated to the following Clarity projects:

Project  D = inactive

Project E = inactive

Project F = inactive

 

In my query, I would only want the results to show xproject 5555 (and nice if it would show the associated Clarity project id), since ALL of its associated clarity projects are inactive.

 

I have tried Where exists, the all command, group by w/ having and I cannot get it to ONLY show me the above. It will show me ALL projects or only those that are inactive.

This is the very basic query that I have been trying to manipulate with various commands.  This will be part of a much larger query, but the larger part is all working, I just need to see if this requirement can be met.

 

select o.xproject, p.unique_name from odf_ca_project o

inner join srm_projects p on o.id= p.id

where p.IS_ACTIVE ='0'

 

this above would only show the inactive clarity projects, but it would return xproject 12345 AND 55555, where I just want it to show 55555. How do I manipulate the where clause or let me know if I need to include more information?

 

thanks!

[Eugene Greiff]

select o.xproject, p.unique_name from odf_ca_project o

inner join srm_projects p on o.id= p.id

where p.IS_ACTIVE ='0'

and o.xproject = '55555'

 

V/r,

Gene

[vtleogal]

Thanks but the above is only an example to illustrate what I was trying to achieve. . I am doing this across the entire database, so I want it to pull ALL x projects meeting the constraint.

[Eugene Greiff]

OK it early here, I didn't read the requirement close enough!

 

To add to Nick's example:

 

select o.*, p.* from odf_ca_project o
inner join srm_projects p on o.id= p.id
left outer join (
    select o.xproject
    from odf_ca_project o
    inner join srm_projects p on o.id= p.id
    where p.IS_ACTIVE ='1'
) a on a.xproject = o.xproject
where a.xproject is null

 

V/r,

Gene

[Nick Darlington]

select p.*, o.*

from srm_projects p

join odf_ca_project o on o.id = p.id

join (

select so.xproject

from odf_ca_project so join srm_projects sp on so.id = sp.id

group by so.xproject

having sum(sp.is_active) = 0

) n on n.xproject = o.xproject

 

The trick is in using sum() over is_active and checking for a result of 0, since if any investments were active for a particular xproject then the sum would be 1 or higher, then joining the results of this back to the non-aggregated odf_ca_project to identify the individual records.

 

Here, using a non-PPM schema so I can just create some temporary tables and data, I replicated your example data above for testing this out (don't run the following on a PPM schema as the objects will already be in use, and the inserts will probably complain/fail):

 

create table srm_projects (id numeric, inv_code nvarchar(32), is_active numeric)

go

 

create table odf_ca_project (id numeric, xproject nvarchar(32))

go

 

insert into srm_projects
select 1, 'project a', 1
union all select 2, 'project b', 0
union all select 3, 'project c', 0
union all select 4, 'project d', 0
union all select 5, 'project e', 0
union all select 6, 'project f', 0

go

 

insert into odf_ca_project
select 1, '12345'
union all select 2, '12345'
union all select 3, '12345'
union all select 4, '55555'
union all select 5, '55555'
union all select 6, '55555'

go

 

-- challenge: return xproject and associated project codes only where all
--            projects are inactive

select p.*, o.*
from srm_projects p
join odf_ca_project o on o.id = p.id
join (
select so.xproject
from odf_ca_project so join srm_projects sp on so.id = sp.id
group by so.xproject
having sum(sp.is_active) = 0
) n on n.xproject = o.xproject

go

 

And the results:

 

id     inv_code     is_active     id     xproject   
-----  -----------  ------------  -----  -----------
4      project d    0             4      55555      
5      project e    0             5      55555      
6      project f    0             6      55555      

 

3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 1/ms]

 

[Executed: 29/12/15 10:53:33 CST ] [Execution: 82/ms]

[vtleogal]

thank you both!!

 

Nick, I had tried the Having command, but I obviously could not figure out the SUM  part and I had just done that extra join, but again, I was off..

so that you both so very much!!

 

I ran it and tested it against several results and they indeed do appear to accurate

I will constrain it further to not show any with a null xproject value, etc.

 

greatly appreciate it!