CA Service Management

Hello,

Does anyone know the where clause to show all contacts that aren't a member of a group?

For example this works to show any inactive contacts that are still a member of a group:

group_list.id is not null AND delete_flag=1 AND type!=2308

But my logic of changing this to the following where clause didn't work.

group_list.id is null AND delete_flag=0 AND type!=2308

I've also tried creating a CABI report with a merged dimension on these objects without success, because the option to show null records doesn't seem to work.

Contact\Contact Extended\Contact - Group Member\Member uuid

Contact\Contact Detail\Uuid

Thanks

Stuart

Hi Stuart.

I am not aware of that this is posssible with an sdm  majic query.

The problem is that you have to check for existance of records in the grpmem table, and this is just not supported in SDM.

The grpmem table handles the information which contact is a member in which group.

In SQL it could look like:

select * from ca_contact
   where contact_type != 2308 and
   not exists (
      select grpmem.id from grpmem
      where member=ca_contact.contact_uuid
   );

Regards

..........Michael

Hi Stuart,

I concur with Michael_Mueller. This is something that I doubt can be done with stored queries. Best would be to do this with a SQL query or a WEBI report based on a similar query the one Michael alluded to.

===

Kind Regards,

Brian

Building on Michael's query, this shows all active analyst contact types that aren't a member of a group:

USE mdb

GO

SELECT

[first_name]

,[last_name]

,[userid]

FROM [mdb].[dbo].[ca_contact]

WHERE [contact_type] = 2307 AND [inactive]=0 AND NOT ([first_name] IS NULL OR [last_name] IS NULL OR [userid] IS NULL)

AND NOT EXISTS (

SELECT [id] FROM [mdb].[dbo].[grpmem]

WHERE [mdb].[dbo].[grpmem].[member]=[mdb].[dbo].[ca_contact].[contact_uuid]

)

ORDER BY [mdb].[dbo].[ca_contact].[first_name] ASC;