~[wc:commonscripts]
"Student Id","Last Name","First Name","Type","Address","City","State","Zip","Home Phone","Work Phone","Cell Phone","Email","LivesWith","IsCustodial"
~[TLIST_SQL;
WITH addr AS (
SELECT paa.personid,
pa.street,
pa.city,
cs.code AS state,
pa.postalcode,
ROW_NUMBER() OVER (
PARTITION BY paa.personid
ORDER BY paa.addresspriorityorder
) AS row_id
FROM personaddressassoc paa
JOIN personaddress pa ON pa.personaddressid = paa.personaddressid
LEFT JOIN codeset cs ON cs.codesetid = pa.statescodesetid
), phone AS (
SELECT ppna.personid,
cs.code as phonetype,
pn.phonenumber,
ROW_NUMBER() OVER (
PARTITION BY ppna.personid, cs.code
ORDER BY ppna.phonenumberpriorityorder
) AS row_id
FROM personphonenumberassoc ppna
JOIN phonenumber pn ON pn.phonenumberid = ppna.phonenumberid
LEFT JOIN codeset cs ON cs.codesetid = ppna.phonetypecodesetid
), email AS (
SELECT peaa.personid,
ea.emailaddress,
ROW_NUMBER() OVER (
PARTITION BY peaa.personid
ORDER BY peaa.emailaddresspriorityorder
) AS row_id
FROM personemailaddressassoc peaa
JOIN emailaddress ea ON ea.emailaddressid = peaa.emailaddressid
)
SELECT REPLACE(s.student_number, '"', '""'),
REPLACE(p.lastname, '"', '""'),
REPLACE(p.firstname, '"', '""'),
ROW_NUMBER() OVER (
PARTITION BY sca.studentdcid
ORDER BY sca.studentdcid, sca.contactpriorityorder
) AS priority,
REPLACE(a.street, '"', '""'),
REPLACE(a.city, '"', '""'),
REPLACE(a.state, '"', '""'),
REPLACE(a.postalcode, '"', '""'),
REPLACE(h.phonenumber, '"', '""'),
REPLACE(w.phonenumber, '"', '""'),
REPLACE(m.phonenumber, '"', '""'),
REPLACE(e.emailaddress, '"', '""'),
REPLACE(scd.liveswithflg, '"', '""'),
REPLACE(scd.iscustodial, '"', '""')
FROM students s
JOIN studentcontactassoc sca
ON sca.studentdcid = s.dcid
JOIN studentcontactdetail scd
ON scd.studentcontactassocid = sca.studentcontactassocid AND
scd.isactive = 1
JOIN person p
ON p.id = sca.personid AND
p.isactive = 1
LEFT JOIN codeset cs
ON cs.codesetid = sca.currreltypecodesetid
LEFT JOIN phone h
ON h.personid = p.id AND
h.phonetype = 'Home' AND
h.row_id = 1
LEFT JOIN phone w
ON w.personid = p.id AND
w.phonetype = 'Work' AND
w.row_id = 1
LEFT JOIN phone m
ON m.personid = p.id AND
m.phonetype = 'Mobile' AND
m.row_id = 1
LEFT JOIN addr a
ON a.personid = p.id AND
a.row_id = 1
LEFT JOIN email e
ON e.personid = p.id AND
e.row_id = 1
WHERE s.student_number IS NOT NULL
ORDER BY s.student_number,
sca.contactpriorityorder
~[if.~(gpv.offset)=]
OFFSET 0 ROWS FETCH NEXT 50000 ROWS ONLY
[else]
OFFSET ~(gpv.offset) ROWS FETCH NEXT 50000 ROWS ONLY
[/if]
;]"~(1;T)","~(2;T)","~(3;T)","~(4;T)","~(5;T)","~(6;T)","~(7;T)","~(8;T)","~(9;T)","~(10;T)","~(11;T)","~(12;T)","~(13;T)","~(14;T)"
[/TLIST_SQL]