Software 101 – Database Part 3

All queries for the job assessment can be found in the query template you have already uploaded.  However, if you would like a separate template for the Job Assessment, you can use the Cognitive JA template here.

The information you will need to locate information about a job will require you to first look up the CompanyID.

• select *
  from Company
  where [Name] = ‘Silly Productions Training Center, UOS’

Then, you will place that CompanyID and the exact job name into this query and run it:

• select PositionId, Name
  from Position
  where Companyid = 112281
  and Name = ‘Recruiter’

This query will produce a PositionID.  This ID when inserted into the following query will show all job assessments (both BA and CA) for this job.

• select *
  from ProSurveySession
  where PositionId = 5778613

**If nothing populates, that means the job did not send out a Job Assessment to set the Job Target.

If there are results, you will be able to see who the assessment was sent to, who completed it and who is also still pending.  There are three other columns you want to take notice of:

1. ScoringMethodId — If the results show a 1, that means the JA was emailed to that stakeholder.  If the results show a 2, that means this is the individual who sent the JAs and completed the JA after sending to other stakeholders.
2. SurveyStatusId — 1 = haven’t completed JA; 2 = error; 3 = Completed.
3. IncludesCognitiveJA — 1 = Cognitive was included on the JA; 0 = Cognitive was not included, EXCEPT if the JA is not completed.  If the JA is still pending then this column will show a 0.

Lastly, if you take the ProSurveySessionID (first column) from the previous query and insert it into this final query, you can review the cognitive answers that were submitted for the JA from that individual.

• select *
  from CognitiveJAResponses c
  inner join CognitiveJAResponses ja on c.CognitiveJAResponseId = ja.CognitiveJAResponseId
  where c.ProSurveySessionId = 1196494

The question response codes are as follows:

1 = Basic

2 = Intermediate

3 = Advanced

0 = None

Test your knowledge

When ready click the ‘Start Quiz’ button below to test your knowledge on the last few lessons. After completing the quiz click the ‘Mark Complete & Next Lesson’ button.