Skip to main content

Example SQL Queries

This article is an aid to help you solve some example SQL queries for you. These queries pull data from your school's SIS into Clipboard.

This article provides some queries you can use as a basis for syncing into Clipboard from some SISes. The list is not exhaustive and is purely here as an aid. Clipboard doesn't condone simply copy-pasting any queries from below. We strongly recommend using these examples to write your own queries, which take into account your specific needs and requirements.

The queries don't return all the data outlined in the schema, so feel free to update and augment the queries as necessary.

Clipboard strongly recommends running these queries in your preferred database client, and checking the data returned is as expected, before using them in any integration.

Students

TASS

SELECT
  RTRIM(LTRIM(s.stud_code)) AS 'sis_id',
  RTRIM(LTRIM(s.preferred_name)) AS 'first_name',
  RTRIM(LTRIM(s.surname)) AS 'last_name',
  'Year ' + RTRIM(LTRIM(CONVERT(VARCHAR(10), s.year_grp))) AS 'year_group',
  RTRIM(LTRIM(CONVERT(VARCHAR(10), s.dob, 105))) AS 'birth_date',
  RTRIM(LTRIM(s.e_mail)) AS 'email',
  0 AS 'boarder',
  /* 1 if a boarder, else 0; NOTE: this query says all students are not boarders (0) */
FROM TASS.DBO.student AS s
WHERE s.cmpy_code = 01
  AND s.dol ISNULL
ORDER BY s.stud_code

TASS' database varies from school to school. Hence, this example may not work for your school.

Synergetic

select
  cast(co.id as varchar(20)) as sis_id,
  co.Preferred as first_name,
  co.Surname as last_name,
  yl.Description as year_group,
  FORMAT(BirthDate, 'dd/MM/yyyy') as birth_date,
  OccupEmail as email,
  case
    when Boarder = 'B' then 1
    else 0
  end as boarder
from Community co
join students st on co.ID = st.ID
join StudentYears sy on sy.ID = st.ID
join luYearLevel yl on yl.Code = sy.YearLevel
where sy.FileYear = YEAR(GETDATE())

UNION

-- Include future students
select
  cast(co.id as varchar(20)) as sis_id,
  co.Preferred as first_name,
  co.Surname as last_name,
  case
    when yl.Description = 'Kinder' then 'Pre-Kindy'
    when year(fs.EnrolDate) = YEAR(GETDATE()) then 'Year ' +      cast(fs.YearLevel as varchar(20))
    when fs.YearLevel -(YEAR(fs.EnrolDate) - YEAR(GETDATE())) = 0 then 'Kindergarten'
    when year(fs.EnrolDate) = YEAR(GETDATE()) + 1 then 'Year ' + cast(
 fs.Yearlevel -(YEAR(fs.EnrolDate) - YEAR(GETDATE())) as varchar(20)
 )
    else 'No Year Level'
  end as year_group,
  FORMAT(BirthDate, 'dd/MM/yyyy') as birth_date,
  OccupEmail as email,
  case
    when Boarder = 'B' then 1
    else 0
  end as boarder
from Community co
join FutureStudents fs on co.ID = fs.ID
join luYearLevel yl on yl.Code = fs.YearLevel
where fs.Status = 'C'
  and (
    Year(fs.EnrolDate) = YEAR(GETDATE())
    or year(fs.EnrolDate) = YEAR(GETDATE()) + 1
  )

Edumate

with first_last_form_run As (
  Select
     STUDENT_FORM_RUN.STUDENT_ID,
     FORM_RUN.FORM_RUN,
     STUDENT_FORM_RUN.START_DATE,
     STUDENT_FORM_RUN.END_DATE,
     STUDENT_FORM_RUN.DATE_ENTERED As DATE_ENTERED,
     school.school,
     ROW_NUMBER() Over (
       PartitionBy STUDENT_FORM_RUN.STUDENT_ID
       Order By STUDENT_FORM_RUN.END_DATE
     ) As "FIRST"
  From EDUMATE.STUDENT_FORM_RUN As STUDENT_FORM_RUN
  Inner Join EDUMATE.FORM_RUN As FORM_RUN On FORM_RUN.FORM_RUN_ID = STUDENT_FORM_RUN.FORM_RUN_ID
  INNER JOIN edumate.school as school ON school.school_id = form_run.school_id
)
select
  student.student_number as sis_id,
  surname as last_name,
  firstname as first_name,
  BIRTHDATE as birth_date,
  contact.email_address as email,
  form.form as year_group,
  0 as boarder -- NOTE: assumes no boarders
FROM Table(
 EDUMATE.GET_ENROLED_STUDENTS_FORM_RUN('2022-02-02')
) gesfr
inner Join EDUMATE.FORM_RUN As FORM_RUN On gesfr.FORM_RUN_ID = FORM_RUN.FORM_RUN_ID
Inner Join EDUMATE.FORM As FORM On FORM_RUN.FORM_ID = FORM.FORM_ID
Inner Join EDUMATE.STUDENT As STUDENT On gesfr.STUDENT_ID = STUDENT.STUDENT_ID
Inner Join EDUMATE.CONTACT As CONTACT On STUDENT.CONTACT_ID = CONTACT.CONTACT_ID
Inner Join first_last_form_run On STUDENT.STUDENT_ID = first_last_form_run.STUDENT_ID
Where first_last_form_run."FIRST" = 1
  and (
    form.form = 'Year 1'
    or form.form = 'Year 2'
    or form.form = 'Year 3'
    or form.form = 'Year 4'
    or form.form = 'Year 5'
    or form.form = 'Year 6'
    or FORM.form = 'Year 7'
    or form.form = 'Year 8'
    or form.form = 'Year 9'
    or form.form = 'Year 10'
    or form.form = 'Year 11'
    or form.form = 'Year 12'
    or (form.form = 'Kindergarten')
  )

Student Photos

Synergetic

SELECT
    sis_ID,
    client_image_url,
    image_base_64
FROM dbo.vPhotos;

OR

SELECT distinct vPhotos.ID AS 'sis_id',

CAST('' AS XML).value('xs:base64Binary(sql:column("PhotoMediumRes"))', 'VARCHAR(MAX)') AS image_base_64

FROM [dbo].[vPhotos]

JOIN vStudents on vStudents.StudentID = vPhotos.ID  where StudentActiveFlag = 1

Guardians

Synergetic

SELECT
  DISTINCT Cast(c.ID as varchar(20)) AS sis_id,
  Cast(s.ID as varchar(20)) AS student_sis_ID,
  c.Preferred AS first_name,
  c.Surname AS last_name,
  c.DefaultEmail AS email,
  Cast(c.ID as varchar(20)) AS secondary_sis_id,
  c.NetworkLogin AS external_username,
  c.DefaultMobilePhone AS phone_number
FROM
 {{database}}.dbo.vCommunityAddresses c,
 {{database}}.dbo.vStudentContactAddress s
WHERE
  c.ID = s.StudentContactID
  AND ((s.CurrentSemesterOnlyFlag = 1))

UNION ALL

SELECT
  DISTINCT Cast(s.StudentContactSpouseID as varchar(20)) AS sis_ID,
  Cast(s.ID as varchar(20)) AS student_sis_ID,
  c.Preferred AS first_name,
  c.Surname AS last_name,
  c.DefaultEmail AS email,
  Cast(c.ID as varchar(20)) AS secondary_sis_ID,
  c.NetworkLogin AS external_username,
  c.DefaultMobilePhone AS phone_number
FROM
 {{database}}.dbo.vCommunityAddresses c,
 {{database}}.dbo.vStudentContactAddress s
WHERE
 c.ID = s.StudentContactSpouseID
 AND ((s.CurrentSemesterOnlyFlag = 1))

Edumate

with first_last_form_run As (
  Select
    STUDENT_FORM_RUN.STUDENT_ID,
    FORM_RUN.FORM_RUN,
    STUDENT_FORM_RUN.START_DATE,
    STUDENT_FORM_RUN.END_DATE,
    STUDENT_FORM_RUN.DATE_ENTERED As DATE_ENTERED,
    ROW_NUMBER() Over (
      Partition By STUDENT_FORM_RUN.STUDENT_ID
      Order By STUDENT_FORM_RUN.END_DATE
    ) As "FIRST"
  From
    EDUMATE.STUDENT_FORM_RUN As STUDENT_FORM_RUN
  Inner Join EDUMATE.FORM_RUN As FORM_RUN On FORM_RUN.FORM_RUN_ID = STUDENT_FORM_RUN.FORM_RUN_ID
),
carers as (
  SELECT
    c.carer_number,
    ct.surname,
    ct.firstname,
    ct.EMAIL_ADDRESS,
    sy.username,
    s.student_number,
    ct.mobile_phone
  FROM
    EDUMATE.carer as c
  INNER JOIN EDUMATE.sys_user as sy ON sy.contact_id = c.contact_id
  inner join EDUMATE.contact as ct on ct.contact_id = c.contact_id
  INNER JOIN EDUMATE.relationship as r ON (
    r.contact_id1 = c.contact_id
    OR r.contact_id2 = c.contact_id
  )
  AND r.report_flag = 1
  INNER JOIN EDUMATE.student as s ON (
    s.contact_id = r.contact_id1
    OR s.contact_id = r.contact_id2
  )
  AND s.contact_id != c.contact_id
)
select
  carers.carer_number as sis_id,
  carers.firstname as first_name,
  carers.surname as last_name,
  carers.email_address as email,
  trim(carers.mobile_phone) as phone_number,
  carers.username as external_username,
  student.student_number as student_sis_id
Table(
  EDUMATE.GET_ENROLED_STUDENTS_FORM_RUN('2022-02-02')
) gesfr
inner Join EDUMATE.FORM_RUN As FORM_RUN On gesfr.FORM_RUN_ID = FORM_RUN.FORM_RUN_ID
Inner Join EDUMATE.FORM As FORM On FORM_RUN.FORM_ID = FORM.FORM_ID
Inner Join EDUMATE.STUDENT As STUDENT On gesfr.STUDENT_ID = STUDENT.STUDENT_ID
Inner Join EDUMATE.CONTACT As CONTACT On STUDENT.CONTACT_ID = CONTACT.CONTACT_ID
inner join carers on carers.student_number = student.student_number
Inner Join first_last_form_run On STUDENT.STUDENT_ID = first_last_form_run.STUDENT_ID
Where
  first_last_form_run."FIRST" = 1
  and carers.email_address is not null

TASS

SELECT
    DISTINCT CASE
        WHEN (sf.sfa_num) IS NULL THEN 'P' + RTRIM(p.par_code)
        ELSE 'P' + RTRIM(p.par_code) + RTRIM(sf.sfa_num)
    END AS sis_id,
    s.stud_code AS student_sis_id,
    RTRIM(pd.preferred_name) AS first_name,
    RTRIM(pd.surname) AS last_name,
    CASE
        WHEN pa.e_mail2 IS NOT NULL THEN RTRIM(pa.e_mail2)
        ELSE RTRIM(pa.e_mail)
    END AS email,
    CASE
        WHEN pa.mobile2 IS NOT NULL THEN rtrim(pa.mobile2)
        ELSE rtrim(pa.mobile1)
    END AS phone_number
FROM
    parent AS p
    INNER JOIN paraddress AS pa ON p.par_code = pa.par_code
    AND p.cmpy_code = pa.cmpy_code
    AND pa.add_num IN ('1', '5')
    LEFT JOIN pia_sfaccess AS sf ON p.par_code = sf.par_code
    AND p.cmpy_code = sf.cmpy_code
    LEFT JOIN studcommrules AS scr ON p.par_code = scr.par_code
    AND scr.add_num IN ('1', '4', '5')
    AND scr.commtype_code IN ('TKCO')
    LEFT JOIN student AS s ON (
        scr.stud_code = s.stud_code
        AND scr.cmpy_code = s.cmpy_code
    )
    INNER JOIN family f ON f.entity_code = p.par_code
    AND (
        f.entity_type = 'P'
        OR f.entity_type = 'E'
    )
    INNER JOIN persondefs pd ON pd.person_num = f.person_num
    AND pd.cmpy_code = p.cmpy_code
    AND pd.gender = 'M'
    AND (
        pd.deceased_flg = 'N'
        OR pd.deceased_flg IS NULL
    )
WHERE
    p.cmpy_code = '01'
    AND s.given_name IS NOT NULL
    AND (
        s.dol IS NULL
        OR CAST(s.dol AS DATE) >= CAST(GETDATE() AS DATE)
    )
    AND (
        (
            CHARINDEX(
                ',' + RTRIM(pa.add_num) + ',',
                ',' + RTRIM(sf.add_nums) + ','
            ) IS NULL
        )
        OR (
            CHARINDEX(
                ',' + RTRIM(pa.add_num) + ',',
                ',' + RTRIM(sf.add_nums) + ','
            ) > 0
        )
    )
UNION
ALL
SELECT
    DISTINCT CASE
        WHEN (sf.sfa_num) IS NULL THEN 'S' + RTRIM(p.par_code)
        ELSE 'S' + RTRIM(p.par_code) + RTRIM(sf.sfa_num)
    END AS sis_id,
    s.stud_code AS student_sis_id,
    RTRIM(pd.preferred_name) AS first_name,
    RTRIM(pd.surname) AS last_name,
    RTRIM(pa.e_mail) AS email,
    pa.mobile1 as phone_number
FROM
    parent AS p
    INNER JOIN paraddress AS pa ON p.par_code = pa.par_code
    AND p.cmpy_code = pa.cmpy_code
    AND pa.add_num IN ('1', '4')
    LEFT JOIN pia_sfaccess AS sf ON p.par_code = sf.par_code
    AND p.cmpy_code = sf.cmpy_code
    LEFT JOIN studcommrules AS scr ON p.par_code = scr.par_code
    AND scr.add_num IN ('1', '4', '5')
    AND scr.commtype_code IN ('TKCO')
    LEFT JOIN student AS s ON (
        scr.stud_code = s.stud_code
        AND scr.cmpy_code = s.cmpy_code
    )
    INNER JOIN family f ON f.entity_code = p.par_code
    AND (
        f.entity_type = 'P'
        OR f.entity_type = 'E'
    )
    INNER JOIN persondefs pd ON pd.person_num = f.person_num
    AND pd.cmpy_code = p.cmpy_code
    AND pd.gender = 'F'
    AND (
        pd.deceased_flg = 'N'
        OR pd.deceased_flg IS NULL
    )
WHERE
    p.cmpy_code = '01'
    AND s.given_name IS NOT NULL
    AND (
        s.dol IS NULL
        OR CAST(s.dol AS DATE) >= CAST(GETDATE() AS DATE)
    )
    AND (
        (
            CHARINDEX(
                ',' + RTRIM(pa.add_num) + ',',
                ',' + RTRIM(sf.add_nums) + ','
            ) IS NULL
        )
        OR (
            CHARINDEX(
                ',' + RTRIM(pa.add_num) + ',',
                ',' + RTRIM(sf.add_nums) + ','
            ) > 0
        )
    )

TASS' database varies from school to school. Hence, this example may not work for your school.

SEQTA (Postgres)

Select
--sc.student,
--sc.contact ,
con.code AS sis_id,
stu.code AS student_sis_id,
con.firstname AS first_name,
con.surname AS last_name,
con.email AS email,
REGEXP_REPLACE(trim(con.phone_mobile), '^(0)', '+61') AS phone_number
FROM "studentContact" AS sc
INNER JOIN "contact" con ON con.id = sc.contact
INNER JOIN "student" stu ON stu.id = sc.student
WHERE coneqt_access = TRUE AND stu."status" = 'FULL'

Medical Conditions

Synergetic

select
  cast(m.StudentID as varchar(10)) sis_id,
  m.StudentMedicalAlert description
from
  vStudentsAll m
inner join FileSemesters f on m.FileYear = f.FileYear
  and m.FileSemester = f.FileSemester
where
  f.SystemCurrentFlag = 1
  and m.StudentMedicalAlertFlag = 1

Edumate

select
  student.student_number as sis_id,
  listagg(condition.condition, ', ') as description
from
  EDUMATE.STUDENT_MED_COND as STUDENT_MED_COND
inner join EDUMATE.CONDITION as condition on condition.CONDITION_ID = student_med_cond.CONDITION_ID
inner join edumate.student as student on student.student_id = STUDENT_MED_COND.student_id
inner join Table(
 EDUMATE.GET_ENROLED_STUDENTS_FORM_RUN('2022-02-02')
) as st on st.student_id = student.student_id
group by
 student.student_number

TASS

SELECT
  DISTINCT LTRIM(RTRIM(stud_code)) AS sis_id,
  medcond.mcond_desc AS description
FROM
  studmedcond
INNER JOIN medcond ON medcond.mcond_code = studmedcond.mcond_code
WHERE
  studmedcond.cmpy_code = '01'
  AND studmedcond.mcond_code = 'AST'

TASS' database varies from school to school. Hence, this example may not work for your school.

Absences

Synergetic

SELECT
  cast(a.ID as varchar(10)) as sis_id,
  a.ReasonCode as reason,
  a.NoteReceivedFlag as explained,
  convert(varchar, AbsenceDate, 103) as absent_date
FROM
  [Synergetic_AUNSW_CCGS_PRD].[dbo].[Absences] a
WHERE
  AbsencePeriod = 'Day'
  AND AbsenceDate BETWEEN CAST(GETDATE() AS Date)
  AND DATEADD(week, 6, CAST(GETDATE() AS Date))

Edumate

select
  student.student_number as sis_id,
  absence_reason.ABSENCE_REASON || ' (' ||   daily_attendance_status.DAILY_ATTENDANCE_STATUS || ')' as reason
from
 EDUMATE.daily_attendance as daily_attendance
inner join EDUMATE.daily_attendance_status AS daily_attendance_status   on daily_attendance_status.daily_attendance_status_id = daily_attendance.daily_attendance_status_id
INNER JOIN EDUMATE.ABSENTEE_REASON AS ABSENTEE_REASON ON ABSENTEE_REASON.STUDENT_ID = daily_attendance.STUDENT_ID
inner join edumate.absence_reason as absence_reason on absence_reason.ABSENCE_REASON_ID = ABSENTEE_REASON.ABSENCE_REASON_ID
inner join edumate.student as student on student.student_id = daily_attendance.student_id
where
  date_on = current_date
  and daily_attendance_status.DAILY_ATTENDANCE_STATUS <> 'Present'
  and CAST(ABSENTEE_REASON.EFFECTIVE_START ASdate) = current_date

TASS

-- This query requires filling out absent type and reason codes as these differ per TASS implementation
SELECT DISTINCT
	RTRIM(LTRIM(studabsent.stud_code)) AS sis_id,
	CASE
		WHEN studabsent.abs_from_time IS NOT NULL THEN CONVERT(VARCHAR, studabsent.absent_date + studabsent.abs_from_time, 126)
		ELSE CONVERT(VARCHAR, studabsent.absent_date, 111)
	END AS absent_date,
	CASE
	-- Some TASS instances will mark a student leaving early as leaving at the end of the school day
	-- if this happens for a specific absent_type fill it in here to mark them as absent till midnight
	-- e.g. ('EDP')
		WHEN studabsent.absent_type IN ('') THEN CONVERT(VARCHAR, CAST(studabsent.absent_date AS DATETIME) + CAST('23:59:59' AS DATETIME), 126)
		WHEN studabsent.abs_to_time IS NOT NULL THEN CONVERT(VARCHAR, CAST(CAST(studabsent.absent_date AS DATETIME) + CAST(studabsent.abs_to_time AS DATETIME) AS DATETIME), 126)
		ELSE NULL
	END AS end_date,
	RTRIM(LTRIM(absreason.areas_desc)) AS reason,
	CASE
	-- TODO: Fill in reason codes to be marked as unexplained e.g. ('B', 'H')
		WHEN studabsent.reas_code IN ('') THEN 0
		ELSE 1
	END AS explained
FROM
	TASS.DBO.studabsent
	JOIN TASS.DBO.absreason ON (studabsent.reas_code = absreason.areas_code)
	JOIN TASS.DBO.abstype ON (studabsent.absent_type = abstype.atype_code)
	JOIN TASS.DBO.student ON (student.stud_code = studabsent.stud_code)
WHERE
	studabsent.absent_date >= CONVERT(DATE, DATEADD (ww, DATEDIFF (ww, 0, GETDATE ()), -1))
	AND abstype.cmpy_code = '01'
	-- TODO: Fill in absent types to include e.g. ('AFS', 'APD')
	AND studabsent.absent_type IN ('')
	-- TODO: Fill in reason codes to entirely exclude even if their absent type is included e.g. ('EXC', 'ASC')
	AND studabsent.reas_code NOT IN ('')

SEQTA (Postgres)

Select
  Distinct cast(s.code as varchar(20)) as sis_id,
  Case
    When as1.reason is null Then 'No Reason Recorded'
    Else as1.reason
  End Reason,
  cast(1 as int) as explained,
  to_char(al.date, 'yyyy-MM-dd') as absent_date
from
  attendancelayer al
inner join attendancesolution as1 on al.attendancesolution = as1.id
inner join attendancetype at1 on at1.id = as1.attendancetype
inner join public."attendancesolutionStudent" ass on ass.attendancesolution = al.attendancesolution
inner join student s on s.id = ass.student
where
  al.date between CAST(NOW() AS Date)
  AND CAST(NOW() + INTERVAL '7 days' AS Date)
  and at1.id in (11, 12, 15, 17, 18, 25, 26, 27, 28, 31, 4, 132, 35, 36, 34)
  and s.status = 'FULL'

Here are some suggestions on how to get Attendance codes from SEQTA to Clipboard.

The SEQTA codes may be sourced from the HTML source code, see below:

Select

Distinct cast(s.code as varchar(20)) as sis_id,

Case

When as1.reason is null Then 'No Reason Recorded'

Else as1.reason

End Reason,

cast(1 as int) as explained,

to_char(al.date, 'yyyy-MM-dd') as absent_date

from

attendancelayer al

inner join attendancesolution as1 on al.attendancesolution = as1.id

inner join attendancetype at1 on at1.id = as1.attendancetype

inner join public."attendancesolutionStudent" ass on ass.attendancesolution = al.attendancesolution

inner join student s on s.id = ass.student

where

al.date BETWEEN DATE_TRUNC('day', NOW() AT TIME ZONE 'UTC' AT TIME ZONE 'Australia/Adelaide')

AND DATE_TRUNC('day', (NOW() AT TIME ZONE 'UTC' AT TIME ZONE 'Australia/Adelaide') + INTERVAL '7 days')

and at1.id in (10, 11, 12, 13, 14, 15, 17, 18, 19, 20, 21, 22, 23, 25, 26, 27, 28, 31, 34, 2, 4, 6, 132, 35, 36, 38, 34)

and s.status = 'FULL'

Please note that the query above includes HTML code which may need to be altered to reflect your schools SEQTA instance.

Emergency Contacts

TASS

The following query merges the guardian contacts (parents) in with the emergency contacts, so Users will have access to both the parent details and the emergency contact details on the roll.

-- Parent 1 Contacts
SELECT
    CAST(p.person_num AS VARCHAR) AS sis_id,
    TRIM(s.stud_code) AS student_sis_id,
    TRIM(p.preferred_name) AS first_name,
    TRIM(p.surname) AS last_name,
    TRIM(ISNULL(pa.e_mail2, pa.e_mail)) AS email,
    TRIM(ISNULL(pa.mobile2, pa.home_phone)) AS phone_number,
    ISNULL(sco.call_order, 1) AS priority_of_contact,
    'Parent 1' AS relationship_of_contact
FROM dbo.student s
INNER JOIN dbo.studcommrules scr
    ON s.cmpy_code = scr.cmpy_code
    AND s.stud_code = scr.stud_code
    AND scr.commtype_code = 'TKCO'
INNER JOIN dbo.parent par
    ON par.par_code = scr.par_code
INNER JOIN dbo.paraddress pa
    ON pa.par_code = par.par_code
    AND pa.add_num = scr.add_num
    AND scr.add_num = 5
LEFT OUTER JOIN dbo.studcallorder sco
    ON scr.par_code = sco.par_code
    AND scr.stud_code = sco.stud_code
    AND pa.add_num = sco.add_num
INNER JOIN dbo.family f
    ON f.cmpy_code = par.cmpy_code
    AND f.entity_code = par.par_code
    AND f.person_posn = 1
    AND f.entity_type = 'P'
INNER JOIN dbo.persondefs p
    ON f.cmpy_code = p.cmpy_code
    AND f.person_num = p.person_num
WHERE s.cmpy_code = '01'
    AND s.year_grp > 0
    AND (s.dol IS NULL OR CAST(s.dol AS DATE) >= GETDATE())

UNION

-- Parent 2 Contacts
SELECT
    CAST(p.person_num AS VARCHAR) AS sis_id,
    TRIM(s.stud_code) AS student_sis_id,
    TRIM(p.preferred_name) AS first_name,
    TRIM(p.surname) AS last_name,
    TRIM(ISNULL(pa.e_mail, pa.e_mail2)) AS email,
    TRIM(ISNULL(pa.mobile1, pa.home_phone)) AS phone_number,
    ISNULL(sco.call_order, 2) AS priority_of_contact,
    'Parent 2' AS relationship_of_contact
FROM dbo.student s
INNER JOIN dbo.studcommrules scr
    ON s.cmpy_code = scr.cmpy_code
    AND s.stud_code = scr.stud_code
    AND scr.commtype_code = 'TKCO'
INNER JOIN dbo.parent par
    ON par.par_code = scr.par_code
INNER JOIN dbo.paraddress pa
    ON pa.par_code = par.par_code
    AND pa.add_num = scr.add_num
    AND scr.add_num = 4
LEFT OUTER JOIN dbo.studcallorder sco
    ON scr.par_code = sco.par_code
    AND scr.stud_code = sco.stud_code
    AND pa.add_num = sco.add_num
INNER JOIN dbo.family f
    ON f.cmpy_code = par.cmpy_code
    AND f.entity_code = par.par_code
    AND f.person_posn = 2
    AND f.entity_type = 'P'
INNER JOIN dbo.persondefs p
    ON f.cmpy_code = p.cmpy_code
    AND f.person_num = p.person_num
WHERE s.cmpy_code = '01'
    AND s.year_grp > 0
    AND (s.dol IS NULL OR CAST(s.dol AS DATE) >= GETDATE())

UNION

-- Alternative Emergency Contacts
SELECT
    'A' + TRIM(par.par_code) AS sis_id,
    TRIM(s.stud_code) AS student_sis_id,
    pa.par_name AS first_name,
    '' AS last_name,
    TRIM(ISNULL(pa.e_mail, pa.e_mail2)) AS email,
    TRIM(ISNULL(pa.mobile1, pa.home_phone)) AS phone_number,
    ISNULL(sco.call_order, 3) AS priority_of_contact,
    'Alt Emergency Contact' AS relationship_of_contact
FROM dbo.student s
INNER JOIN dbo.studcommrules scr
    ON s.cmpy_code = scr.cmpy_code
    AND s.stud_code = scr.stud_code
    AND scr.commtype_code = 'EC'
INNER JOIN dbo.parent par
    ON par.par_code = scr.par_code
INNER JOIN dbo.paraddress pa
    ON pa.par_code = par.par_code
    AND pa.add_num = scr.add_num
    AND scr.add_num = 8
LEFT OUTER JOIN dbo.studcallorder sco
    ON scr.par_code = sco.par_code
    AND scr.stud_code = sco.stud_code
    AND pa.add_num = sco.add_num
WHERE s.cmpy_code = '01'
    AND s.year_grp > 0
    AND (s.dol IS NULL OR CAST(s.dol AS DATE) >= GETDATE());
Did this answer your question?