“dbo.get_supersetV1” is a stored procedure that can be used as a starter for creating export queries.
DECLARE @RC INT DECLARE @DATE VARCHAR(10) EXECUTE @RC = [dbo].[get_supersetV1] @DATE GO
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