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