====== Superset ======
"dbo.get_supersetV1" is a stored procedure that can be used as a starter for creating export queries.
===== Execute =====
DECLARE @RC int
DECLARE @date varchar(10)
EXECUTE @RC = [dbo].[get_supersetV1]
@date
GO
===== Definition =====
DECLARE @date varchar(10)
IF @date IS NULL SET @date= CONVERT(CHAR(24),GETDATE(),101)
SELECT DISTINCT
@date AS mydate,
p.studentnumber AS StudentDistrictID,
i.ssn AS StudentSSN,
p.stateID AS StudentStateID,
CASE WHEN e.enddate < GETDATE() THEN 0 ELSE 1 END StudentEnrollmentStatus,
s.number AS StudentSiteCode,
d.number AS StudentDistNum,
COALESCE(rm.[name], 'NHR') AS StudentHomeroom,
i4.lastname AS StudentHomeRoomTeacherLastname,
i4.firstname AS StudentHomeRoomTeacherFirstname,
i.lastname AS StudentLastName,
i.firstname AS StudentFirstName,
i.middlename AS StudentMiddleName,
LTRIM(RTRIM(CONVERT(CHAR(24),i.birthdate,101))) AS StudentBirthdate,
i.gender AS StudentGender,
e.grade AS StudentGradeLevel,
i.raceEthnicity AS StudentRace,
c1.email As StudentEmail,
i2.lastname AS StudentPrimaryContactLastName,
i2.firstname AS StudentPrimaryContactFirstName,
r.[name] AS StudentPrimaryContactRelationship,
i2.ssn AS StudentPrimaryContactSSN,
c2.email As StudentPrimaryContactEmail,
c2.workphone As StudentPrimaryWorkPhone,
a.county AS StudentPrimaryContactCounty,
CASE WHEN a.postOfficeBox = 1 THEN 'P.O. Box '+a.number
ELSE a.number+' '+COALESCE(a.prefix+' ','')+COALESCE(a.street+' ','')+COALESCE(a.tag+' ','')+COALESCE(a.dir+' ','')+
COALESCE(CASE WHEN COALESCE(a.apt,'') <> '' AND LEFT(a.apt,1)<>'#' THEN '#'+a.apt END,'') END AS StudentPrimaryContactStreetAddress,
a.city AS StudentPrimaryContactCity,
a.state AS StudentPrimaryContactState,
a.zip AS StudentPrimaryContactZip,
COALESCE(h.phone,'') AS StudentPrimaryContactPhoneNumber
FROM person p
INNER JOIN [identity] i ON i.identityID = p.currentidentityID
LEFT OUTER JOIN contact c1 ON c1.personID = p.personID
INNER JOIN enrollment e ON e.personID = p.personID AND e.enrollmentID =
(SELECT TOP 1 x.enrollmentID FROM enrollment x
INNER JOIN calendar cx ON cx.calendarID = x.calendarID AND cx.summerschool = 0
INNER JOIN schoolyear syx ON syx.endyear = x.endyear AND syx.active = 1
WHERE x.personID = p.personID AND x.endyear = e.endyear and x.active = 1
ORDER BY CASE WHEN x.serviceType = 'P' THEN 1 ELSE 2 END, x.startDate DESC)
INNER JOIN calendar c ON c.calendarID = e.calendarID
INNER JOIN school s ON s.schoolID = c.schoolID
INNER JOIN district d ON d.districtID = s.districtID
INNER JOIN schoolyear sy ON sy.endyear = c.endyear AND sy.active = 1
INNER JOIN CampusAttribute ca on ca.object = 'Identity' AND ca.element = 'raceEthnicity'
LEFT OUTER JOIN CampusDictionary cd on cd.attributeID = ca.attributeID AND cd.code = i.raceEthnicity
LEFT OUTER JOIN household h ON h.householdID =
(SELECT TOP 1 m.householdID FROM householdMember m
LEFT OUTER JOIN householdMember g ON g.householdID = m.householdID
LEFT OUTER JOIN relatedPair r ON r.personID1 = m.personID AND r.personID2 = g.personID
AND r.guardian = 1
WHERE m.personID = p.personID
AND (m.startDate IS NULL OR m.startDate <= GETDATE())
AND (m.endDate IS NULL OR m.endDate >= GETDATE())
ORDER BY m.secondary, CASE WHEN r.personID2 IS NULL THEN 1 ELSE 0 END, m.startDate DESC)
LEFT OUTER JOIN relatedPair r ON r.personID1 = p.personID AND r.personID2 =
(SELECT TOP 1 x.personID2 FROM relatedPair x
LEFT OUTER JOIN householdMember m ON m.householdID = h.householdID
AND m.personID = x.personID2
AND (m.startDate IS NULL OR m.startDate <= GETDATE())
AND (m.endDate IS NULL OR m.endDate >= GETDATE())
WHERE x.personID1 = p.personID AND x.guardian = 1
ORDER BY CASE WHEN m.householdID IS NULL THEN 1 ELSE 0 END)
LEFT OUTER JOIN person p2 ON p2.personID = r.personID2
LEFT OUTER JOIN [identity] i2 ON i2.identityID = p2.currentIdentityID
LEFT OUTER JOIN contact c2 ON c2.personID = p2.personID
LEFT OUTER JOIN address a ON a.addressID =
(SELECT TOP 1 x.addressID FROM householdMember m
JOIN householdLocation l ON l.householdID = m.householdID
AND (l.startDate IS NULL OR l.startDate <= GETDATE())
AND (l.endDate IS NULL OR l.endDate >= GETDATE())
JOIN address x ON x.addressID = l.addressID
WHERE m.personID = p.personID
AND (m.startDate IS NULL OR m.startDate <= GETDATE())
AND (m.endDate IS NULL OR m.endDate >= GETDATE())
ORDER BY CASE WHEN m.householdID = h.householdID THEN 0 ELSE 1 END, CASE WHEN l.mailing = 1 THEN 0 ELSE 1 END,l.secondary)
--************** Address CASE statement for PO box. This is the library extract therefore the mailing address is preferred *******
LEFT OUTER JOIN [section] se WITH (NOLOCK) ON se.sectionID =
(SELECT TOP 1 x.sectionID FROM [section] x WITH (NOLOCK)
INNER JOIN roster r ON r.sectionID = x.sectionID AND r.trialID = x.trialID
AND ((r.startdate IS NULL OR r.startdate <= GETDATE()) AND (r.enddate IS NULL OR r.enddate >=GETDATE()))
INNER JOIN trial tl WITH (NOLOCK) ON tl.trialID = r.trialID AND tl.active = 1
INNER JOIN Course c WITH (NOLOCK) ON c.courseID = x.courseID AND c.calendarID = tl.calendarID
INNER JOIN SectionPlacement sp WITH (NOLOCK) ON sp.sectionID = x.sectionID AND tl.trialID = sp.trialID
INNER JOIN Period pd WITH (NOLOCK) ON pd.periodID = sp.periodID --AND (pd.noninstructional IS NULL OR pd.noninstructional = 0)
INNER JOIN PeriodSchedule ps ON ps.periodScheduleID = pd.periodScheduleID AND ps.structureID = tl.structureID
INNER JOIN Term tm WITH (NOLOCK) ON tm.termID = sp.termID
INNER JOIN TermSchedule ts WITH (NOLOCK) ON ts.termScheduleID = tm.termScheduleID AND ts.structureID = tl.structureID
INNER JOIN [day] d WITH (NOLOCK) ON d.calendarID = c.calendarID AND d.[date] >= tm.startdate AND d.[date] <=tm.enddate
AND d.periodscheduleID = ps.periodscheduleID
AND d.structureID = ps.structureID
AND CONVERT(CHAR(24),d.[date],101) = CONVERT(CHAR(24),@date,101)
WHERE r.personID = p.personID
AND c.calendarID = e.calendarID
ORDER BY CASE WHEN c.homeroom = 1 OR x.homeroomsection = 1 THEN 0 ELSE 1 END, pd.seq)
LEFT OUTER JOIN room rm WITH (NOLOCK) ON rm.roomID = se.roomID
LEFT OUTER JOIN person p4 WITH (NOLOCK) ON p4.personID = se.teacherpersonID
LEFT OUTER JOIN [identity] i4 WITH (NOLOCK) ON i4.identityID = p4.currentidentityID