Generic Queries

Getting list of forms with its category, yearmonth of creation and some ticketing fields

select PI.id, PI.datestarted, PI.dateended, cast (year(datestarted)as varchar)+ '/' + right('0' + cast(month(datestarted) as varchar),2) YearMonth, FL.Title Formname, CL.Title as Category, TL.Title as Topic, PI.TicketStatus, PI.TicketCategory, PI.TicketPriority, '1' as Counter from ProcessInstance PI inner join FormLanguage FL on PI.FormId = FL.Form_Id and fl.Language_Id=2 inner join Form F on f.Id=fl.Form_Id inner join Topic T on t.Id=f.Topic_Id inner join TopicLanguage TL on tl.Topic_Id=t.Id and TL.Language_Id=2 inner join Category c on c.Id=t.Category_Id inner join CategoryLanguage CL on cl.Category_Id=c.Id and cl.Language_Id=2 where PI.DateEnded is null

Get a list of all users and their roles, 1 entry per role

select email, uniqueid, (SELECT   ', ' + role.name AS 'data()' FROM role,personrole, person p2 WHERE (p1.id = p2.id) and  (p2.id = personrole.person_id)  and (role.id = personrole.role_id) FOR xml path() ) from person p1

Get a list of all users and their roles, 1 entry holds all roles

select email, uniqueid, role.name from person, role, personrole where person.id = personrole.person_id and role.id = personrole.role_id

Update Email templates in Process

update ProcessEmailTemplatelanguage set body = replace(replace(Body, '<p>',''),'</p>','<br /><br />') where ProcessEmailTemplateId in (select PETL.ProcessEmailTemplateId from ProcessEmailTemplatelanguage PETL inner join ProcessEmailTemplate PET on PETL.ProcessEmailTemplateId=PET.Id where PET.processid=260)

All Active groups and their members by name

  SELECT Name as GroupName, (SELECT Stuff(
  (SELECT N', ' + FirstName + ' ' + LastName
  FROM PersonGroupPerson, Person
  WHERE Person.Id = PersonGroupPerson.PersonId AND PersonGroupId = PG.Id 
  FOR XML PATH(),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N)) AS Members
  FROM [dbo].[PersonGroup] PG WHERE [Active] = 1

If some groups have a lot of members, it is better to store that in fields 25 and 26, as they allow for more data to be in

  SELECT  as Empty1,  as Empty2,  as Empty3,  as Empty4,  as Empty5,  as Empty6,  as Empty7,  as Empty8,  as Empty9,  as Empty10,  as Empty11,  as Empty12,  as Empty13,  as Empty14,  as Empty15,  as Empty16,  as Empty17,  as Empty18,  as Empty19,  as Empty20,  as Empty21,  as Empty22,  as Empty23,  as Empty24, Name as GroupName, (SELECT Stuff(
 (SELECT N', ' + FirstName + ' ' + LastName
 FROM PersonGroupPerson, Person
 WHERE Person.Id = PersonGroupPerson.PersonId AND PersonGroupId = PG.Id 
 FOR XML PATH(),TYPE)
 .value('text()[1]','nvarchar(max)'),1,2,N)) AS Members
 FROM [dbo].[PersonGroup] PG WHERE [Active] = 1