Generic Queries

From SMT-X
Jump to: navigation, search

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