====== Superset ======
"dbo.get_supersetV1" is a stored procedure that can be used as a starter for creating export queries.

===== Execute =====
<code sql>
DECLARE @RC int
DECLARE @date varchar(10)
EXECUTE @RC = [dbo].[get_supersetV1] 
   @date
GO
</code>

===== Definition =====
<code sql>
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
</code>