Useful SQL statments

Delete any empty calendars

UPDATE CALENDARS SET CHANGETYPE=2 WHERE IDENTIFIER IN (SELECT CALENDARS.IDENTIFIER FROM CALENDARS
LEFT OUTER JOIN EVENTS ON EVENTS.CALENDAR_IDENTIFIER = CALENDARS.IDENTIFIER
LEFT OUTER JOIN TASK ON TASK.CALENDAR_IDENTIFIER = CALENDARS.IDENTIFIER
GROUP BY CALENDARS.IDENTIFIER
HAVING COUNT(EVENTS.IDENTIFIER) = 0 AND COUNT (TASK.IDENTIFIER) = 0)

Show Groups / Subgroups relationships

SELECT PARENTTRAIL.CLIENTIDENTIFIER,PARENTS.UPDATEDON,PARENTS.IDENTIFIER,PARENTS.NAME,
CHILD.NAME,CHILD.IDENTIFIER,CHILD.UPDATEDON,CHTRAIL.CLIENTIDENTIFIER  
FROM GROUPS_GROUPS 
JOIN GROUPS AS CHILD ON CHILD.IDENTIFIER  = GROUPS_GROUPS.SUBGROUPS_IDENTIFIER 
JOIN GROUPS AS PARENTS ON PARENTS.IDENTIFIER = GROUPS_GROUPS.PARENTGROUPS_IDENTIFIER
LEFT OUTER JOIN RECORDADDITION AS CHTRAIL ON CHTRAIL.UPDATEDON=CHILD.UPDATEDON 
AND CHTRAIL.RECORDIDENTIFIER=CHILD.IDENTIFIER
LEFT OUTER JOIN RECORDADDITION AS PARENTTRAIL ON PARENTTRAIL.UPDATEDON=CHILD.UPDATEDON 
AND PARENTTRAIL.RECORDIDENTIFIER=PARENTS.IDENTIFIER

Show contacts not associated with any groups (also available under Reports Tab)

SELECT Contacts.identifier, Contacts.firstName, Contacts.lastName, Contacts.companyName, Contacts.changeType, 
COUNT(Groups_Contacts.groups_identifier) as memberships, Clientidentifier.clientidentifier,Contacts.updatedOn
FROM Contacts
LEFT OUTER JOIN Groups_Contacts ON Groups_Contacts.members_identifier = Contacts.identifier
LEFT OUTER JOIN ClientIdentifier ON ClientIdentifier.lastSuccessfullSync=Contacts.updatedOn
GROUP BY Contacts.identifier, Contacts.firstName, Contacts.lastName, Contacts.companyName, Contacts.changeType, 
Clientidentifier.clientidentifier,Contacts.updatedOn
HAVING COUNT(Groups_Contacts.groups_identifier) = 0
ORDER BY Clientidentifier.clientidentifier, Contacts.companyName, Contacts.firstName, Contacts.lastName

Assign ungrouped contacts to existing group

The group is specified by it's group identifer (380A5F2-1A22-4F46-A1DD-C9B99FEFEF0B)

INSERT INTO GROUPS_CONTACTS 
SELECT '9380A5F2-1A22-4F46-A1DD-C9B99FEFEF0B' AS GROUP_IDENTIFIER,identifier AS MEMBERS_IDENTIFIER FROM 
(SELECT Contacts.identifier, Contacts.firstName, Contacts.lastName, Contacts.companyName, Contacts.changeType, 
COUNT(Groups_Contacts.groups_identifier) as memberships, Clientidentifier.clientidentifier,Contacts.updatedOn
FROM Contacts
LEFT OUTER JOIN Groups_Contacts ON Groups_Contacts.members_identifier = Contacts.identifier
LEFT OUTER JOIN ClientIdentifier ON ClientIdentifier.lastSuccessfullSync=Contacts.updatedOn
GROUP BY Contacts.identifier, Contacts.firstName, Contacts.lastName, Contacts.companyName, Contacts.changeType, 
Clientidentifier.clientidentifier,Contacts.updatedOn
HAVING COUNT(Groups_Contacts.groups_identifier) = 0
ORDER BY Clientidentifier.clientidentifier, Contacts.companyName, Contacts.firstName, Contacts.lastName)

This statement should be followed by

UPDATE GROUPS SET UPDATEDON = CURRENT_TIMESTAMP WHERE IDENTIFIER ='9380A5F2-1A22-4F46-A1DD-C9B99FEFEF0B';

to ensure the clients fetch these changes during their next sync session.