Smtxwiki: 1 revision imported

2020-05-25T20:44:28Z

1 revision imported

← Older revision	Revision as of 22:44, 25 May 2020
(No difference)

Smtxwiki at 14:03, 6 February 2018

2018-02-06T14:03:51Z

New page

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
<nowiki>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)</nowiki>

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

Generic Queries - Revision history

Smtxwiki: 1 revision imported

Smtxwiki at 14:03, 6 February 2018