DATABASE VIEWS* for beginners.

[ktillman]

DATABASE VIEWS* for beginners.
Hello all,
Does anyone have any database view best practices? I understand them yet it is hit or miss when I join tables for reporting purposes.(I'm throwing darts in the dark)
****
My questions are:
1)What should I attempt to join between two tables to get a db view of that join? (ie a field in common between the two tables?SYSID to SYSID?)
2)Table order. Does it matter?
3)Where clause: What is allowed? What isn't allowed? Appears any syntax is allowed regarding logic (ie || && = ==)
4)Are there any detailed examples on the web I can use? Google: "Servicenow Database view" even google can't find much but a single you tube video and a knowledge article from servicenow.com Other than those there is nothing explaining this feature.

thanks!

Kimani

[lynmonticone]

Hi Kimani....   What you are describing is exactly what I am trying to accomplish.   I have successfully created pivot tables of the questions - scores - and the analyst who created the ticket... but I can't figure out how to run against the Assignment Group or Assigned to.   Would you share which tables you joined and the where clauses... I have been beating my head against the wall on this.   I greatly appreciate any help you may provide.   I believe the tables I need are: (I did not have to do a database view to grab the data from the first 3 tables - but I can't connect Incident...   or   Requested Items)

Metric Result [asmt_metric_result]

                                      Instance                                 AINST #

                                      Metric                                         Survey Question

                                      Assigned to                         Customer who took survey

                                      Actual value                       Survey Answer Numeric Value   (-1) if comment or (1) yes/ (0) no

                                      String value                         Survey Answer Numeric Value if string value comment or yes/no

                                      Created                                   Date Survey was taken          

                                      Source                                       Assessment Metric Type (Survey)

Assessment Instance [asmt_assessment_instance]

                                      Instance.Number                             AINST #

                                      Instance.Assessment group     ASG #

                                      Instance.Trigger ID                       Incident #

                                      Instance.Trigger table               Incident/Problem/Requested Item

                                      Instance.State                                     Complete or Ready to Take

                                      Instance.Assigned to                 Customer who took survey

                                      Instance.Metric type                   Survey

Assessment group [asmt_assessment]

            Assessment Group.Number           ASG #

            Assessment group.Created             Date of Incident

                                      Assessment group.Created by   Analyst who took the call

                                      Assessment group.Metric type   Survey

NEEDED:

                  Incident

                                      Incident.Number         INC#

                                      Incident.Assignment group

                                      Incident.Assigned to

                  Requested Item

                                      Requested Item.Assignment group

                                      Requested Item.Assigned to

[ktillman]

I know I'm like a year late BUT lol

---

[ktillman]

asmt_metric_type='4392d53da1b342002a37c06383b4d1ae' (data was cut off from image above).

[mdsh]

Hi ,

We are trying to go one step further. We have multiple Tasks related to single ritm. We want managers to be able to see survey responses for Assignment Groups(in which they are the managers). And we have Assignment Group and Assigned To defined for Tasks (sc_task). So we need to glide from ritm to tasks (where sc_task_request_item=ritm_sys_id).

For some reason, it is throwing error: syntax violation....

Could you help out?

[ktillman]

Older Survey Eureka Join: (later versions changed the tables the surveys are coming from but the concept is still the same)

asmt_metric_type='c2ee5cb54fdc0e40cf3eacb14210c7c3' (data cut of from screenshot below)