How do I select a field from the second row of the top two rows of data selected from a table.
(SELECT TOP(2) scp.DisplayName
FROM dbo.StudentContact sc
INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID
WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1
Order By scp.LastName,
scp.FirstName
I want to select the top two rows and select the name of the person in the second row to appear in my result.
Try:
select top 1 DisplayName
from
(
SELECT TOP(2) scp.DisplayName, scp.LastName, scp.FirstName
FROM dbo.StudentContact sc
INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID
WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1
Order By scp.LastName,
scp.FirstName
) as t
order by LastName DESC, FirstName DESC
go
If you are using SQL Server 2005, then use:
;with cte
as
(
SELECT scp.DisplayName, row_number() over(order by scp.LastName, scp.FirstName) as rn
FROM dbo.StudentContact sc
INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID
WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1
)
select DisplayName
from cte
where rn = 2;
go
AMB
|||
Thanks for your reply, I am using SQL Server 2005 so I opted for the second option. I have incorporated it into my query but I am getting a null value for the second guardian. I am including the entire query this time, sorry I didn't do this the first time. If you can give any help it would be greatly appreciated.
IF OBJECT_ID('dbo.p_Export') IS NOT NULL
DROP PROCEDURE [dbo].[p_Export]
GO
/*
<Purpose>This query produces a list student information with their lunch codes for import into Comalex Cafe.</Purpose>
Coded against version history file 7/16/07 12:25pm
EXEC p_Export @.XmlAcadSessionList = '<root><item ID="18" /><item ID="282" /><item ID="9" /><item ID="10" /><item ID="11" /><item ID="12" /></root>'
*/
CREATE PROCEDURE p_Export
(
@.XmlAcadSessionList XML,
@.StudentFilterID INT = NULL
)
AS
;with Guardian
as
(
SELECT sc.StudentID,scp.DisplayName, row_number() over(order by sc.StudentID,scp.LastName, scp.FirstName) as rn
FROM dbo.StudentContact sc
INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID
WHERE sc.IsCustodian = 1 AND sc.ReceivesMailings = 1
)
SELECT
Snum = Substring(stu.StudentNumber,2,14),
Lname = stu.LastName,
Fname = stu.FirstName,
DOB = REPLICATE('0', 2 - LEN(CONVERT(VARCHAR(2),DATEPART(mm,stu.DateOfBirth)))) + CONVERT(VARCHAR(2),DATEPART(mm,stu.DateOfBirth))
+ REPLICATE('0', 2 - LEN(CONVERT(VARCHAR(2),DATEPART(dd,stu.DateOfBirth)))) + CONVERT(VARCHAR(2),DATEPART(dd,stu.DateOfBirth))
+ (CONVERT(VARCHAR(4),DATEPART(yyyy,stu.DateOfBirth))),
[Status] = CASE WHEN
lp.StateCode = '1'
THEN 'D'
WHEN lp.StateCode = '2'
THEN 'R'
WHEN lp.StateCode = '3'
THEN 'F'
WHEN lp.StateCode NOT IN ('2', '3')
THEN 'D'
END,
Fill3 = space(20),
GradeLevel = sas.GradeLevelName,
Home = (SELECT stf.DisplayName FROM dbo.Staff stf
INNER JOIN dbo.Homeroom hm ON hm.HomeroomID = sas.HomeroomID
WHERE stf.StaffID = hm.TeacherID),
Race = (SELECT TOP(1) (CASE WHEN e.StateCode = 'H' THEN 'O' WHEN e.StateCode NOT IN ('W', 'B', 'A', 'I') THEN 'O' Else e.StateCode END)--need to add code to set code to O for H
FROM dbo.Person p
LEFT JOIN dbo.PersonEthnicity pe on pe.PersonID = p.PersonID
INNER JOIN dbo.Ethnicity e on e.EthnicityID = pe.EthnicityID
WHERE p.StudentNumber = stu.StudentNumber
ORDER BY pe.IsPrimary DESC
),
Asnum = stu.AltStudentNumber,
[Language] = CASE WHEN lan.[Name] Like 'Spanish'
THEN 'S'
ELSE 'E'
END,
Addr1 = RTRIM(adr.StreetNumber) + ' ' +RTRIM(adr.AddressLine1),
City = adr.City,
[State] = adr.State,
Zip = adr.PostalCode,
Phone = pt.AreaCode+pt.LocalNumber,
Fill4 = space(20),
[Gname1]= (SELECT TOP(1) scp.DisplayName
FROM dbo.StudentContact sc
INNER JOIN dbo.Person scp on scp.PersonID = sc.ContactID
WHERE sc.StudentID = stu.StudentID AND sc.IsCustodian = 1 AND sc.ReceivesMailings = 1
Order By scp.LastName,
scp.FirstName
),
Gname2= (Select g.DisplayName from dbo.studentacadsession sas1 INNER JOIN Guardian g ON g.StudentID = sas1.StudentID where g.StudentID = sas.StudentID AND g.rn = 2),
Fill5 = space(20),
Fill6 = space(20),
Fill7 = space(20),
Fill8 = space(20),
Fill9 = space(20),
Fill10 = space(20),
Fill11 = space(20),
Fill12 = space(20),
Fill13 = space(20),
Fill14 = space(20),
Fill15 = space(20),
Fill16 = space(20),
Fill17 = space(20),
Fill18 = space(20),
Fill19 = space(20),
Fill20 = space(20),
Fill21 = space(20),
Fill22 = space(20),
Fill23 = space(20),
Fill24 = space(20),
Fill25 = space(20),
Fill26 = space(20),
Fill27 = space(20),
Fill28 = space(20),
Fill29 = space(20),
Active = CASE When sas.CurrentEnrollmentStatus = 'C'
THEN 'T'
ELSE 'F'
END
FROM
dbo.StudentAcadSession sas
INNER JOIN dbo.AcadSession asn ON asn.AcadSessionID = sas.AcadSessionID
INNER JOIN dbo.Student stu ON stu.StudentID = sas.StudentID
LEFT JOIN dbo.LunchProgram lp ON lp.LunchProgramID = sas.LunchProgramID
LEFT JOIN dbo.PersonLanguage pl ON pl.PersonID = sas.StudentID
LEFT JOIN dbo.Language lan ON lan.LanguageID = pl.LanguageID
LEFT JOIN dbo.Address adr on adr.AddressID = stu.MailingAddressID
LEFT JOIN dbo.PersonTelephone pt ON pt.PersonID = sas.StudentID
AND pt.IsPrimary = 1
WHERE
(EXISTS
(
SELECT
*
FROM
@.XmlAcadSessionList.nodes('/root/item') X(a)
WHERE
X.a.value('@.ID','INT') = asn.AcadSessionID
)
AND
asn.IsClosed = 0)
AND
( ( @.StudentFilterID IS NULL ) OR ( EXISTS( SELECT * FROM dbo.[FilterKeySet] fks WHERE fks.[FilterID] = @.StudentFilterID AND fks.[ItemID] = sas.[StudentID] ) ) )
ORDER BY stu.StudentNumber
GO
|||
I would like to help, but how can I reproduce your problem?
Can you post the estructure of the tables involved and sample data?
AMB
|||
tbl 1
IF OBJECT_ID('dbo.Person') IS NOT NULL
DROP TABLE [dbo].[Person]
GO
CREATE TABLE [dbo].[Person] (
[PersonID] INT IDENTITY (3,1) NOT NULL
CONSTRAINT [CPK_Person] PRIMARY KEY CLUSTERED ( [PersonID] ),
[PersonGUID] UNIQUEIDENTIFIER NOT NULL
CONSTRAINT [UNCC_Person_PersonGUID] UNIQUE NONCLUSTERED ( [PersonGUID] ),
[PersonNumber] VARCHAR(15) NOT NULL
CONSTRAINT [UNCC_Person_PersonNumber] UNIQUE NONCLUSTERED ( [PersonNumber] ),
[StudentNumber] VARCHAR(15) NOT NULL
CONSTRAINT [DF_Person_StudentNumber] DEFAULT(''),
[StaffNumber] VARCHAR(15) NOT NULL
CONSTRAINT [DF_Person_StaffNumber] DEFAULT(''),
[AltPersonNumber] VARCHAR(15) NULL,
[DisplayName] VARCHAR(255) NOT NULL,
[FirstName] VARCHAR(50) NOT NULL
CONSTRAINT [DF_Person_FirstName] DEFAULT(''),
[MiddleName] VARCHAR(50) NOT NULL
CONSTRAINT [DF_Person_MiddleName] DEFAULT(''),
[LastName] VARCHAR(75) NOT NULL,
[PreferredName] VARCHAR(75) NOT NULL
CONSTRAINT [DF_Person_PreferredName] DEFAULT(''),
[DateOfBirth] DATETIME NULL,
[SocialSecurityNumber] CHAR(9) NOT NULL
CONSTRAINT [DF_Person_SocialSecurityNumber] DEFAULT(''),
[ProperTitleID] SMALLINT NULL,
[GenerationID] SMALLINT NULL,
[GenderID] SMALLINT NULL,
[GenderDescriptor] VARCHAR(50) NULL,
[EducationLevelID] SMALLINT NULL,
[MaritalStatusID] SMALLINT NULL,
[ReligionID] SMALLINT NULL,
[ResidencyStatusID] SMALLINT NULL,
[PhotographID] INT NULL,
[MailingAddressID] INT NULL,
[PhysicalAddressID] INT NULL,
[UserID] INT NULL,
[CountryOfResidence] CHAR(3) NOT NULL
CONSTRAINT [DF_Person_CountryOfResidence] DEFAULT(''),
[EmployerName] VARCHAR(150) NOT NULL
CONSTRAINT [DF_Person_EmployerName] DEFAULT(''),
[IsHomeless] BIT NOT NULL
CONSTRAINT [DF_Person_IsHomeless] DEFAULT (0),
[RowVersion] UNIQUEIDENTIFIER NOT NULL
CONSTRAINT [DF_Person_RowVersion] DEFAULT (NEWID())
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'The person table contains common information about students, teachers and other staff members, parents, emergency contacts, and other persons in the system.',
N'user', N'dbo', N'table', N'Person'
GO
exec sp_addextendedproperty N'MS_Description', N'The primary key of the table.',
N'user', N'dbo', N'table', N'Person', N'column', N'PersonID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier that may be used to identify a unique person across multiple databases or systems. Unlike the PersonID, the PersonGUID should be kept with the original record if the data is transferred to another database.',
N'user', N'dbo', N'table', N'Person', N'column', N'PersonGUID'
GO
exec sp_addextendedproperty N'MS_Description', N'An easy to read identifier of the person.',
N'user', N'dbo', N'table', N'Person', N'column', N'PersonNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'The full name of the person as it should be displayed by the system.',
N'user', N'dbo', N'table', N'Person', N'column', N'DisplayName'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s first name. Empty quotes if unknown.',
N'user', N'dbo', N'table', N'Person', N'column', N'FirstName'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s middle name. Empty quotes if unknown.',
N'user', N'dbo', N'table', N'Person', N'column', N'MiddleName'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s last name. Last name is required for all persons.',
N'user', N'dbo', N'table', N'Person', N'column', N'LastName'
GO
exec sp_addextendedproperty N'MS_Description', N'The name that the person prefers to be called. The person''s full name should not be used. Empty quotes indicates that the person has no preferred name; the first name is assumed.',
N'user', N'dbo', N'table', N'Person', N'column', N'PreferredName'
GO
exec sp_addextendedproperty N'MS_Description', N'The date on which the person was born, not including time. Null indicates an unknown date of birth.',
N'user', N'dbo', N'table', N'Person', N'column', N'DateOfBirth'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s unformatted social security number. Null represents not known or not applicable.',
N'user', N'dbo', N'table', N'Person', N'column', N'SocialSecurityNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s title. Examples: {Mr, Ms, Dr}. Null represents not known or not applicable.',
N'user', N'dbo', N'table', N'Person', N'column', N'ProperTitleID'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s generational suffix. Examples: {Jr, Sr, III}. Null represents not known or not applicable.',
N'user', N'dbo', N'table', N'Person', N'column', N'GenerationID'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s gender. Examples: {male, female}. Null represents not known.',
N'user', N'dbo', N'table', N'Person', N'column', N'GenderID'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s one character gender descriptor according to the GenderID; Cached for quick access. F indicates Female, M indicates Male, Null represents not known.',
N'user', N'dbo', N'table', N'Person', N'column', N'GenderDescriptor'
GO
exec sp_addextendedproperty N'MS_Description', N'The highest level of education completed by the person. Null represents not known.',
N'user', N'dbo', N'table', N'Person', N'column', N'EducationLevelID'
GO
exec sp_addextendedproperty N'MS_Description', N'The current marital status of the person. Null represents not known.',
N'user', N'dbo', N'table', N'Person', N'column', N'MaritalStatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s self-identified religion. Null represents not known.',
N'user', N'dbo', N'table', N'Person', N'column', N'ReligionID'
GO
exec sp_addextendedproperty N'MS_Description', N'The residency status of the person from the perspective of the country of the owner of the data.',
N'user', N'dbo', N'table', N'Person', N'column', N'ResidencyStatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'An image of the person.',
N'user', N'dbo', N'table', N'Person', N'column', N'PhotographID'
GO
exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s mailing address.',
N'user', N'dbo', N'table', N'Person', N'column', N'MailingAddressID'
GO
exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s physical address.',
N'user', N'dbo', N'table', N'Person', N'column', N'PhysicalAddressID'
GO
exec sp_addextendedproperty N'MS_Description', N'The name of the country of the person''s primary residence.',
N'user', N'dbo', N'table', N'Person', N'column', N'CountryOfResidence'
GO
exec sp_addextendedproperty N'MS_Description', N'The name of the person''s employer. Null if the person''s employee is not known or not applicable.',
N'user', N'dbo', N'table', N'Person', N'column', N'EmployerName'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the person is currently homeless. 1 = homeless, 0 = not homeless.',
N'user', N'dbo', N'table', N'Person', N'column', N'IsHomeless'
GO
exec sp_addextendedproperty N'MS_Description', N'A uniqueidentifier used to manage optimistic concurrency.',
N'user', N'dbo', N'table', N'Person', N'column', N'RowVersion'
GO
tbl 1 sample data
PersonID PersonGUID PersonNumber StudentNumber StaffNumber AltPersonNumber DisplayName FirstName MiddleName LastName PreferredName DateOfBirth SocialSecurityNumber ProperTitleID GenerationID GenderID GenderDescriptor EducationLevelID MaritalStatusID ReligionID ResidencyStatusID PhotographID MailingAddressID PhysicalAddressID UserID CountryOfResidence EmployerName IsHomeless RowVersion
-- -- -- -- -- - -- -- - - -- - -- -- -
3 6A6A8238-D35D-483D-A2BB-1223E3C2830F P1001 S1001 NULL Allenton, Alice Alice Allenton Alice 1990-10-10 00:00:00.000 367296330 NULL NULL 2 F NULL NULL NULL NULL NULL 1 2 NULL 0 1D551B0E-F4C1-483C-B974-AFE7B2C8C2D5
4 A614FB9F-B4A6-487F-9A95-D7822CF90921 P1002 S1002 NULL Daniels, Darrell Darrell Daniels Darrell 1992-02-14 00:00:00.000 434257090 NULL NULL 1 M NULL NULL NULL NULL NULL 3 3 NULL 0 83B4EFAD-4E3E-4AA2-A77F-7E4FEE7433BD
5 FE955667-D634-47EC-B0C4-434CEE3C4B7B P1003 S1003 NULL Aventon, Arlene Arlene Aventon Arlene 1990-11-24 00:00:00.000 881807495 NULL NULL 2 F NULL NULL NULL NULL NULL 4 5 NULL 0 F34C80CD-1AE3-46FE-9CE4-F00142685E05
6 23D316DD-3100-4A56-9F36-9D8B34D21121 P1004 S1004 NULL Brown, Betty Betty Brown Betty 1990-10-10 00:00:00.000 460805371 NULL NULL 2 F NULL NULL NULL NULL NULL 6 7 NULL 0 5344672C-F50F-4574-8DD7-3A61B5A2F7B2
7 DF49BECF-76AA-40E4-AF30-210A9C03BFAC P1005 S1005 NULL Edwards, Ethan Ethan Edwards Ethan 1992-02-14 00:00:00.000 504931759 NULL NULL 1 M NULL NULL NULL NULL NULL 8 8 NULL 0 BACC7CE4-6C1C-4FC4-9591-CC66AA89EF3A
8 6F7B664C-AC44-4379-BE23-694B8A86A07E P1006 S1006 NULL Buck, Barbara Barbara Buck Barbara 1990-11-24 00:00:00.000 515164939 NULL NULL 2 F NULL NULL NULL NULL NULL 9 10 NULL 0 94951FD2-831A-44E5-8494-ACB0D6368EC3
9 577E4F3C-F674-47C0-9A21-1860952F0102 P1007 ST1001 NULL AStaff, Alpha Alpha AStaff Alpha NULL 111998888 NULL NULL 1 M NULL NULL NULL NULL NULL NULL NULL NULL 0 4402F5FC-23D8-4FDC-A32B-C694287E4171
Tbl 2
IF OBJECT_ID('dbo.StudentContact') IS NOT NULL
DROP TABLE [dbo].[StudentContact]
GO
CREATE TABLE [dbo].[StudentContact]
(
[StudentID] INT NOT NULL,
[ContactID] INT NOT NULL,
[RelationshipID] SMALLINT NOT NULL,
[Description] VARCHAR(255) NOT NULL
CONSTRAINT [DF_StudentContact_Description] DEFAULT (''),
[ReceivesMailings] BIT NOT NULL
CONSTRAINT [DF_StudentContact_ReceivesMailings] DEFAULT (0),
[CanPickUp] BIT NOT NULL
CONSTRAINT [DF_StudentContact_CanPickUp] DEFAULT (0),
[IsRelated] BIT NOT NULL
CONSTRAINT [DF_StudentContact_IsRelated] DEFAULT(0),
[IsCustodian] BIT NOT NULL
CONSTRAINT [DF_StudentContact_IsCustodian] DEFAULT(0),
[IsEmergencyContact] BIT NOT NULL
CONSTRAINT [DF_StudentContact_IsEmergencyContact] DEFAULT(0),
CONSTRAINT [CPK_StudentContact] PRIMARY KEY CLUSTERED ( [StudentID], [ContactID] )
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'The StudentContact table contains student-person relationships in the system.',
N'user', N'dbo', N'table', N'StudentContact'
GO
exec sp_addextendedproperty N'MS_Description', N'The student for whom the person is a contact.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'StudentID'
GO
exec sp_addextendedproperty N'MS_Description', N'The person who is the contact.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'ContactID'
GO
exec sp_addextendedproperty N'MS_Description', N'Defines the relationship between the student and the contact.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'RelationshipID'
GO
exec sp_addextendedproperty N'MS_Description', N'Free text description of how the contact relates to the student.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'Description'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates the contact receives mail-outs from the school for this student.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'ReceivesMailings'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates the contact can pick up this student from school.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'CanPickUp'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates the contact is a blood relation to the student.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsRelated'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates the contact is the legal custodian of the student.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsCustodian'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates the contact should be notified in an emergency.',
N'user', N'dbo', N'table', N'StudentContact', N'column', N'IsEmergencyContact'
GO
Tbl 2 Sample Data
StudentID ContactID RelationshipID Description ReceivesMailings CanPickUp IsRelated IsCustodian IsEmergencyContact
-- -- -- - --
55 5 3 0 0 0 1 0
69 91 1 1 1 1 1
82 4 1 1 1 1 1 1
8842 0 0 0 1 0
91 3 1 1 1 1 1 1
tbl 3
IF OBJECT_ID('dbo.Student') IS NOT NULL
DROP TABLE [dbo].[Student]
GO
CREATE TABLE [dbo].[Student] (
[StudentID] INT IDENTITY (3,1) NOT NULL
CONSTRAINT [CPK_Student] PRIMARY KEY CLUSTERED ( [StudentID] ),
[StudentGUID] UNIQUEIDENTIFIER NOT NULL
CONSTRAINT [UNCC_Student_StudentGUID] UNIQUE NONCLUSTERED ( [StudentGUID] ),
[StudentNumber] VARCHAR(15) NOT NULL
CONSTRAINT [UNCC_Student_StudentNumber] UNIQUE NONCLUSTERED ( [StudentNumber] ),
[DisplayName] VARCHAR(255) NOT NULL,
[FirstName] VARCHAR(50) NOT NULL
CONSTRAINT [DF_Student_FirstName] DEFAULT(''),
[MiddleName] VARCHAR(50) NOT NULL
CONSTRAINT [DF_Student_MiddleName] DEFAULT(''),
[LastName] VARCHAR(75) NOT NULL,
[PreferredName] VARCHAR(75) NOT NULL
CONSTRAINT [DF_Student_PreferredName] DEFAULT(''),
[DateOfBirth] DATETIME NULL,
[SocialSecurityNumber] CHAR(9) NOT NULL
CONSTRAINT [DF_Student_SocialSecurityNumber] DEFAULT(''),
[NextYearSchoolID] INT NULL,
[ProperTitleID] SMALLINT NULL,
[GenerationID] SMALLINT NULL,
[GenderID] SMALLINT NULL,
[GenderDescriptor] CHAR(1) NULL,
[MaritalStatusID] SMALLINT NULL,
[ReligionID] SMALLINT NULL,
[ResidencyStatusID] SMALLINT NULL,
[PhotographID] INT NULL,
[MailingAddressID] INT NULL,
[MailingAddressMultiLine] VARCHAR(230) NULL,
[MailingAddressSingleLine] VARCHAR(225) NULL,
[MailingAddressIsListed] BIT NULL,
[PhysicalAddressID] INT NULL,
[ResidesOutOfDistrict] BIT NOT NULL
CONSTRAINT [DF_Student_ResidesOutOfDistrict] DEFAULT( 0 ),
[LimitedEnglishID] SMALLINT NULL,
[MigrantFamilyID] SMALLINT NULL,
[BirthCertNumber] VARCHAR(30) NOT NULL
CONSTRAINT [DF_Student_BirthCertNumber] DEFAULT(''),
[BirthCertVerifyNumber] VARCHAR(30) NOT NULL
CONSTRAINT [DF_Student_BirthCertVerifyNumber] DEFAULT(''),
[Section504Qualification] CHAR(3) NOT NULL
CONSTRAINT [DF_Student_Section504Qualification] DEFAULT ('NA'),
[AltStudentNumber] VARCHAR(15) NOT NULL
CONSTRAINT [DF_Student_AltStudentNumber] DEFAULT(''),
[StateIDNumber] VARCHAR(30) NOT NULL
CONSTRAINT [DF_Student_StateIDNumber] DEFAULT(''),
[CountryOfResidence] CHAR(3) NOT NULL
CONSTRAINT [DF_Student_CountyOfResidence] DEFAULT(''),
[EmployerName] VARCHAR(150) NOT NULL
CONSTRAINT [DF_Student_EmployerName] DEFAULT(''),
[IsHomeless] BIT NOT NULL
CONSTRAINT [DF_Student_IsHomeless] DEFAULT (0),
[IsImmigrant] BIT NOT NULL
CONSTRAINT [DF_Student_IsImmigrant] DEFAULT (0),
[IsForeignExchange] BIT NOT NULL
CONSTRAINT [DF_Student_IsForeignExchange] DEFAULT (0),
[IsAllowedInetAccess] BIT NOT NULL
CONSTRAINT [DF_Student_IsAllowedInetAccess] DEFAULT (0),
[HasMedicalAlert] BIT NOT NULL
CONSTRAINT [DF_Student_HasMedicalAlert] DEFAULT (0),
[IsGraduate] BIT NOT NULL
CONSTRAINT [DF_Student_IsGraduate] DEFAULT (0),
[IsHomeSchooled] BIT NOT NULL
CONSTRAINT [DF_Student_IsHomeSchooled] DEFAULT (0),
[IsTuition] BIT NOT NULL
CONSTRAINT [DF_Student_IsTuition] DEFAULT (0),
[TelephonyPIN] VARCHAR(8) NOT NULL,
[InternetPassword] VARCHAR(20) NOT NULL,
--REVIEW IIB: Schema change document says this should be renamed to FormattedTelephoneNumber.
[TelephoneNumber] VARCHAR(21) NULL,
[IsListed] BIT NULL,
[EmailAddress] VARCHAR(128) NULL,
[OriginalEnrollmentDate] DATETIME NULL,
[ExpectedGraduationDate] DATETIME NULL,
[CachedSchoolList] TEXT NULL,
[SpecialInstructions] VARCHAR(4096) NOT NULL
CONSTRAINT [DF_Student_SpecialInstructions] DEFAULT(''),
[SpEdStatusID] SMALLINT NULL,
[PrimaryExcepID] SMALLINT NULL,
[SecondaryExcepID] SMALLINT NULL,
[LeastRestEnvID] SMALLINT NULL,
[CaseManagerID] INT NULL,
[EligibilityDate] DATETIME NULL,
[ReevaluationDate] DATETIME NULL,
[IEPBeginDate] DATETIME NULL,
[IEPEndDate] DATETIME NULL,
[DateEnrolledInLEA] DATETIME NULL,
[ReportingSchoolID] INT NULL,
[IntegratedWithSETS] BIT NOT NULL
CONSTRAINT [DF_Student_IntegratedWithSETS] DEFAULT(0),
[UserID] INT NOT NULL,
[RowVersion] UNIQUEIDENTIFIER NOT NULL
CONSTRAINT [DF_Student_RowVersion] DEFAULT (NEWID())
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'The Student table contains district-wide information about students in the system.',
N'user', N'dbo', N'table', N'Student'
GO
exec sp_addextendedproperty N'MS_Description', N'The primary key of the table.',
N'user', N'dbo', N'table', N'Student', N'column', N'StudentID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier that may be used to identify a unique student across multiple databases or systems. Unlike the StudentID, the StudentGUID should be kept with the original record if the data is transferred to another database.',
N'user', N'dbo', N'table', N'Student', N'column', N'StudentGUID'
GO
exec sp_addextendedproperty N'MS_Description', N'An easy to read identifier of the student.',
N'user', N'dbo', N'table', N'Student', N'column', N'StudentNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'The full name of the student as it should be displayed by the system.',
N'user', N'dbo', N'table', N'Student', N'column', N'DisplayName'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s first name. Empty quotes if unknown.',
N'user', N'dbo', N'table', N'Student', N'column', N'FirstName'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s middle name. Empty quotes if unknown.',
N'user', N'dbo', N'table', N'Student', N'column', N'MiddleName'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s last name. Last name is required for all students.',
N'user', N'dbo', N'table', N'Student', N'column', N'LastName'
GO
exec sp_addextendedproperty N'MS_Description', N'The name that the student prefers to be called. The student''s full name should not be used. Empty quotes indicates that the Student has no preferred name; the first name is assumed.',
N'user', N'dbo', N'table', N'Student', N'column', N'PreferredName'
GO
exec sp_addextendedproperty N'MS_Description', N'The date on which the student was born, not including time. Null indicates an unknown date of birth.',
N'user', N'dbo', N'table', N'Student', N'column', N'DateOfBirth'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s unformatted social security number. Null represents not known.',
N'user', N'dbo', N'table', N'Student', N'column', N'SocialSecurityNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'ID (FK) of the school this student will attend next year.',
N'user', N'dbo', N'table', N'Student', N'column', N'NextYearSchoolID'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s title. Examples: {Mr, Ms, Dr}. Null represents not known or not applicable.',
N'user', N'dbo', N'table', N'Student', N'column', N'ProperTitleID'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s generational suffix. Examples: {Jr, Sr, III}. Null represents not known or not applicable.',
N'user', N'dbo', N'table', N'Student', N'column', N'GenerationID'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s gender. Examples: {male, female}. Null represents not known.',
N'user', N'dbo', N'table', N'Student', N'column', N'GenderID'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s one character gender descriptor according to the GenderID; Cached for quick access. F indicates Female, M indicates Male, Null represents not known.',
N'user', N'dbo', N'table', N'Student', N'column', N'GenderDescriptor'
GO
exec sp_addextendedproperty N'MS_Description', N'The current marital status of the student. Null represents not known.',
N'user', N'dbo', N'table', N'Student', N'column', N'MaritalStatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'The student''s self-identified religion. Null represents not known.',
N'user', N'dbo', N'table', N'Student', N'column', N'ReligionID'
GO
exec sp_addextendedproperty N'MS_Description', N'The residency status of the student from the perspective of the country of the owner of the data.',
N'user', N'dbo', N'table', N'Student', N'column', N'ResidencyStatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'An image of the student.',
N'user', N'dbo', N'table', N'Student', N'column', N'PhotographID'
GO
exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s mailing address.',
N'user', N'dbo', N'table', N'Student', N'column', N'MailingAddressID'
GO
exec sp_addextendedproperty N'MS_Description', N'The ID of the person''s physical address.',
N'user', N'dbo', N'table', N'Student', N'column', N'PhysicalAddressID'
GO
exec sp_addextendedproperty N'MS_Description', N'The level of written and verbal proficiency that the student possesses in the English language. Null represents that the student''s primary language, or mother-tongue, is English. Any other value represents a limited English proficiency.',
N'user', N'dbo', N'table', N'Student', N'column', N'LimitedEnglishID'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether a student belongs to a migrant family. Null represents that the student''s family is not migrant. Any other value represents the cateogory of migrant family to which the student belongs.',
N'user', N'dbo', N'table', N'Student', N'column', N'MigrantFamilyID'
GO
exec sp_addextendedproperty N'MS_Description', N'The birth certificate number from the student''s birth certificate.',
N'user', N'dbo', N'table', N'Student', N'column', N'BirthCertNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'A reference to the method used to verify the birth certificate. The data usually represents the name or identifier of the person who performed a visual verification of the physical document.',
N'user', N'dbo', N'table', N'Student', N'column', N'BirthCertVerifyNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether a student has a metnal or physical disability that qualifies under Section 504 of the Rehabilitation Act of 1973. (Empty Quotes) Indicates no qualified disability. (504) Indicates a qualified disability. (IEP) Indicates a qualified disability for a student with an IEP.',
N'user', N'dbo', N'table', N'Student', N'column', N'Section504Qualification'
GO
exec sp_addextendedproperty N'MS_Description', N'Used to identify a student to another agency or organization.',
N'user', N'dbo', N'table', N'Student', N'column', N'AltStudentNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'The number assigned by the State Dept of Education.',
N'user', N'dbo', N'table', N'Student', N'column', N'StateIDNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'The abbreviation of the country of the student''s primary residence.',
N'user', N'dbo', N'table', N'Student', N'column', N'CountryOfResidence'
GO
exec sp_addextendedproperty N'MS_Description', N'The name of the student''s employer. Null if the student''s employer is not known or not applicable.',
N'user', N'dbo', N'table', N'Student', N'column', N'EmployerName'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is currently homeless. (1) homeless. (0) not homeless.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsHomeless'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is an immigrant. (1) immigrant. (0) not an immigrant.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsImmigrant'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is a foreign exchange student. (1) foreign exchange student. (0) not a foreign exchange student.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsForeignExchange'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is allowed internet access at the school. (1) The student is allowed internet access. (0) The student is not allowed internet access.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsAllowedInetAccess'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is a graduate. (1) The student is a graduate. (0) The student is not a graduate.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsGraduate'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the student is home schooled. (1) The student is home schooled. (0) The student is not home schooled.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsHomeSchooled'
GO
exec sp_addextendedproperty N'MS_Description', N'Indicates whether the students parents pay tution. (1) The students parents pay tuition. (0) The students parents do not pay tuition.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsTuition'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s primary telephone number. Cached for quick access. Additional telephone numbers are stored in the PersonTelephone table.',
N'user', N'dbo', N'table', N'Student', N'column', N'TelephoneNumber'
GO
exec sp_addextendedproperty N'MS_Description', N'Boolean indicating if the telephone number is listed on external lists.',
N'user', N'dbo', N'table', N'Student', N'column', N'IsListed'
GO
exec sp_addextendedproperty N'MS_Description', N'The person''s primary email address. Cached for quick access. Additional email addresses are stored in the PersonEmail table.',
N'user', N'dbo', N'table', N'Student', N'column', N'EmailAddress'
GO
exec sp_addextendedproperty N'MS_Description', N'The date the student was originally enrolled in the district. NULL indicates that the student has never been enrolled in the district. This value is maintained by the system.',
N'user', N'dbo', N'table', N'Student', N'column', N'OriginalEnrollmentDate'
GO
exec sp_addextendedproperty N'MS_Description', N'Date that the student is expected to graduate. NULL indicates that this information is unknown.',
N'user', N'dbo', N'table', N'Student', N'column', N'ExpectedGraduationDate'
GO
exec sp_addextendedproperty N'MS_Description', N'An XML formatted cached list of the schools in which the student has been enrolled.',
N'user', N'dbo', N'table', N'Student', N'column', N'CachedSchoolList'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Indicator used to determine if a student is receiving Special Education Services.',
N'user', N'dbo', N'table', N'Student', N'column', N'SpEdStatusID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'The primary reason why the student is reciving Special Education Services.',
N'user', N'dbo', N'table', N'Student', N'column', N'PrimaryExcepID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'The secondary reason why the student is reciving Special Education Services.',
N'user', N'dbo', N'table', N'Student', N'column', N'SecondaryExcepID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Represents the student least restrictive environment for Special Education Services.',
N'user', N'dbo', N'table', N'Student', N'column', N'LeastRestEnvID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Reference to a staff member that is in chanrge of the students Special Education Services.',
N'user', N'dbo', N'table', N'Student', N'column', N'CaseManagerID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date in which a student has become eligible for an Exceptionality.',
N'user', N'dbo', N'table', N'Student', N'column', N'EligibilityDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date in which to reevaluate a student to see if they still need Special Education Services for there exceptionality.',
N'user', N'dbo', N'table', N'Student', N'column', N'ReevaluationDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date that represents when a student has begon an Individulized Education Program.',
N'user', N'dbo', N'table', N'Student', N'column', N'IEPBeginDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date that represents when a student has ended an Individulized Education Program.',
N'user', N'dbo', N'table', N'Student', N'column', N'IEPEndDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date in which a student first started receiving Special Education Services.',
N'user', N'dbo', N'table', N'Student', N'column', N'DateEnrolledInLEA'
GO
EXEC sp_addextendedproperty N'MS_Description', N'A refrence to the school that a student is considered to report from.',
N'user', N'dbo', N'table', N'Student', N'column', N'ReportingSchoolID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Indicator to determine if a student has been Integrated with SETSWeb.',
N'user', N'dbo', N'table', N'Student', N'column', N'IntegratedWithSETS'
GO
exec sp_addextendedproperty N'MS_Description', N'The user ID of the student, for use with the Home Module.',
N'user', N'dbo', N'table', N'Student', N'column', N'UserID'
GO
exec sp_addextendedproperty N'MS_Description', N'A uniqueidentifier used to manage optimistic concurrency.',
N'user', N'dbo', N'table', N'Student', N'column', N'RowVersion'
GO
Tbl 3 Sample Data
StudentID StudentGUID StudentNumber DisplayName FirstName MiddleName LastName PreferredName DateOfBirth SocialSecurityNumber NextYearSchoolID ProperTitleID GenerationID GenderID GenderDescriptor MaritalStatusID ReligionID ResidencyStatusID PhotographID MailingAddressID MailingAddressMultiLine MailingAddressSingleLine MailingAddressIsListed PhysicalAddressID ResidesOutOfDistrict LimitedEnglishID MigrantFamilyID BirthCertNumber BirthCertVerifyNumber Section504Qualification AltStudentNumber StateIDNumber CountryOfResidence EmployerName IsHomeless IsImmigrant IsForeignExchange IsAllowedInetAccess HasMedicalAlert IsGraduate IsHomeSchooled IsTuition TelephonyPIN InternetPassword TelephoneNumber IsListed EmailAddress OriginalEnrollmentDate ExpectedGraduationDate CachedSchoolList SpecialInstructions SpEdStatusID PrimaryExcepID SecondaryExcepID LeastRestEnvID CaseManagerID EligibilityDate ReevaluationDate IEPBeginDate IEPEndDate DateEnrolledInLEA ReportingSchoolID IntegratedWithSETS UserID RowVersion
-- -- -- -- -- - - -- - - -- - -- - -- -- - -- - - -- -- - - -- -- -- -- -- -- - - -- - -- - -- -- -- -- -- -- --
3 6A6A8238-D35D-483D-A2BB-1223E3C2830F S1001 Allenton, Alice Alice Allenton Alice 1990-10-10 00:00:00.000 367296330 NULL NULL NULL 2 F NULL NULL NULL NULL 1 PO Box 59
148 Starling Road
Vienna, MO 65582 PO Box 59148 Starling Road Vienna, MO 65582 0 2 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-1234 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 3 1D551B0E-F4C1-483C-B974-AFE7B2C8C2D5
4 A614FB9F-B4A6-487F-9A95-D7822CF90921 S1002 Daniels, Darrell Darrell Daniels Darrell 1992-02-14 00:00:00.000 434257090 NULL NULL NULL 1 M NULL NULL NULL NULL 3 81 Darby Hollow Street
Apt 29
Richland, MO 65556 81 Darby Hollow StreetApt 29 Richland, MO 65556 0 3 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4321 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 4 83B4EFAD-4E3E-4AA2-A77F-7E4FEE7433BD
5 FE955667-D634-47EC-B0C4-434CEE3C4B7B S1003 Aventon, Arlene Arlene Aventon Arlene 1990-11-24 00:00:00.000 881807495 NULL NULL NULL 2 F NULL NULL NULL NULL 4 PO Box 300
815 West Eubanks
Vienna, MO 65582 PO Box 300815 West Eubanks Vienna, MO 65582 0 5 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4333 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 5 F34C80CD-1AE3-46FE-9CE4-F00142685E05
6 23D316DD-3100-4A56-9F36-9D8B34D21121 S1004 Brown, Betty Betty Brown Betty 1990-10-10 00:00:00.000 460805371 NULL NULL NULL 2 F NULL NULL NULL NULL 6 PO Box 806
187 West Franklin
Vienna, MO 65582 PO Box 806187 West Franklin Vienna, MO 65582 0 7 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-1234 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 6 5344672C-F50F-4574-8DD7-3A61B5A2F7B2
7 DF49BECF-76AA-40E4-AF30-210A9C03BFAC S1005 Edwards, Ethan Ethan Edwards Ethan 1992-02-14 00:00:00.000 504931759 NULL NULL NULL 1 M NULL NULL NULL NULL 8 3331 East Broadway
Apt 93
Stoutland, MO 65560 3331 East BroadwayApt 93 Stoutland, MO 65560 0 8 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4321 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 7 BACC7CE4-6C1C-4FC4-9591-CC66AA89EF3A
8 6F7B664C-AC44-4379-BE23-694B8A86A07E S1006 Buck, Barbara Barbara Buck Barbara 1990-11-24 00:00:00.000 515164939 NULL NULL NULL 2 F NULL NULL NULL NULL 9 PO Box 9196
3331 Hwy JJ
Stoutland, MO 65560 PO Box 91963331 Hwy JJ Stoutland, MO 65560 0 10 0 NULL NULL NA 0 0 0 0 0 0 0 0 (573) 690-4333 1 NULL 2006-08-10 00:00:00.000 NULL <cache><c asID="6" asName="0607 Regular" schID="6" schName="Academic Session View Year Calendar" schNumber="3000" status="C" /></cache> NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 8 94951FD2-831A-44E5-8494-ACB0D6368EC3
|||
We appreciate your effort in posting the code and sample data.
However, in order to help us help you, we really need the sample data in a form that does not require that we spend much time creating and populating the tables.
Please view this link which should help you understand how to prepare the data so we can easily use it.
(Keep in mind, the less time we have to spend in 'setting up' the problem, the more likely you will get folks here to volunteer their time to help you..)
|||
Thanks for the link, this makes it much easier for me also......
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(5,'45847C59-0EEC-4F0A-B3A7-74B8B8F877BE','S1001','Burr, Aaron','Aaron','','Burr','','Feb 6 1989 12:00:00:000AM','902061756',5,NULL,NULL,2,'M',NULL,NULL,NULL,4,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,1,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','Not allowed to associate with Alexander Hamilton.',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,3,'2D1228D2-650D-4831-8269-7D5B42F66995')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(6,'A9B6D8C2-8D95-42E2-92E6-02F496A764BB','S1002','Franklin, Benjamin','Benjamin','','Franklin','Ben','Jan 17 1966 12:00:00:000AM','901171706',5,NULL,NULL,2,'M',NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','The Philadelphia Inquirer',0,0,0,0,0,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,4,'5B4F017C-1A9C-4ABF-9F93-C57AD53DED5F')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(7,'50DD3F56-2882-4223-B993-53FB765E1B2F','S1004','Gerry, Elbridge','Elbridge','','Gerry','','Jul 17 1989 12:00:00:000AM','907171744',5,NULL,NULL,2,'M',NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,0,0,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,6,'C87C1F1B-B27F-444F-B7B3-0E565652A0BA')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(8,'7C02B0FC-D46E-43BF-A19A-6BD30301BC37','S1003','Hamilton, Alexander','Alexander','','Hamilton','','Jan 11 1990 12:00:00:000AM','901111755',5,NULL,NULL,2,'M',NULL,NULL,NULL,7,1,'287 Convent Ave New York, NY 10031','287 Convent Ave New York, NY 10031',0,1,0,NULL,NULL,'','','NA','','','','US Treasury Dept',0,0,0,0,0,0,0,0,'','','(212) 283-5154 x34555',0,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,5,'29707034-4806-4F6E-A00C-B3E174FCE297')
INSERT INTO [student] ([StudentID],[StudentGUID],[StudentNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[NextYearSchoolID],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[MailingAddressMultiLine],[MailingAddressSingleLine],[MailingAddressIsListed],[PhysicalAddressID],[ResidesOutOfDistrict],[LimitedEnglishID],[MigrantFamilyID],[BirthCertNumber],[BirthCertVerifyNumber],[Section504Qualification],[AltStudentNumber],[StateIDNumber],[CountryOfResidence],[EmployerName],[IsHomeless],[IsImmigrant],[IsForeignExchange],[IsAllowedInetAccess],[HasMedicalAlert],[IsGraduate],[IsHomeSchooled],[IsTuition],[TelephonyPIN],[InternetPassword],[TelephoneNumber],[IsListed],[EmailAddress],[OriginalEnrollmentDate],[ExpectedGraduationDate],[CachedSchoolList],[SpecialInstructions],[SpEdStatusID],[PrimaryExcepID],[SecondaryExcepID],[LeastRestEnvID],[CaseManagerID],[EligibilityDate],[ReevaluationDate],[IEPBeginDate],[IEPEndDate],[DateEnrolledInLEA],[ReportingSchoolID],[IntegratedWithSETS],[UserID],[RowVersion])VALUES(12,'6194B294-F02E-41CB-8494-57B1DD290E45','S1005','Washington, Martha','Martha','','Washington','','May 15 1991 12:00:00:000AM','902104557',NULL,NULL,NULL,1,'F',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,'','','NA','','','','',0,0,0,0,0,1,0,0,'','',NULL,NULL,NULL,NULL,NULL,'<cache><c asID="18" asName="0506 Regular" schID="4" schName="Greene High School" schNumber="001" status="C" /></cache>','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,7,'48601CC4-5162-42DE-8B79-45CB9FCCDFDA')
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(5,8,69,'Dueling Partner',0,0,0,0,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(5,10,69,'Guardian',0,0,0,1,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(7,6,69,'',0,0,0,0,0)
INSERT INTO [studentcontact] ([StudentID],[ContactID],[RelationshipID],[Description],[ReceivesMailings],[CanPickUp],[IsRelated],[IsCustodian],[IsEmergencyContact])VALUES(7,10,69,'',0,0,0,0,0)
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(3,'E742AD65-ADA7-4D50-87B4-18BEDF2946C0','P1001','','ST1002',NULL,'Adams, John','John','','Adams','','Oct 30 1989 12:00:00:000AM','911311735',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,'','',0,'71F9A6CC-9002-4E0B-8E0B-C6B46EE3D6E6')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(4,'5A3AE32D-7511-4272-BDF8-AFE9F6C4A0AA','P1002','','ST1005',NULL,'Adams, John Quincy','John','Quincy','Adams','','Jul 11 1989 12:00:00:000AM','907111767',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,3,NULL,NULL,NULL,'','',0,'0BB294F5-0843-40F3-9BB7-8429C4E04AED')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(5,'45847C59-0EEC-4F0A-B3A7-74B8B8F877BE','P1003','S1001','',NULL,'Burr, Aaron','Aaron','','Burr','','Feb 6 1989 12:00:00:000AM','902061756',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,4,NULL,NULL,NULL,'','',0,'2D1228D2-650D-4831-8269-7D5B42F66995')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(6,'A9B6D8C2-8D95-42E2-92E6-02F496A764BB','P1004','S1002','',NULL,'Franklin, Benjamin','Benjamin','','Franklin','Ben','Jan 17 1966 12:00:00:000AM','901171706',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,5,NULL,NULL,NULL,'','The Philadelphia Inquirer',0,'5B4F017C-1A9C-4ABF-9F93-C57AD53DED5F')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(7,'50DD3F56-2882-4223-B993-53FB765E1B2F','P1005','S1004','',NULL,'Gerry, Elbridge','Elbridge','','Gerry','','Jul 17 1989 12:00:00:000AM','907171744',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,'','',0,'C87C1F1B-B27F-444F-B7B3-0E565652A0BA')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(8,'7C02B0FC-D46E-43BF-A19A-6BD30301BC37','P1006','S1003','',NULL,'Hamilton, Alexander','Alexander','','Hamilton','','Jan 11 1990 12:00:00:000AM','901111755',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,7,1,1,NULL,'','US Treasury Dept',0,'29707034-4806-4F6E-A00C-B3E174FCE297')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(9,'AF52AF69-8645-416F-AD46-5288F3977202','P1007','','ST1003',NULL,'Jefferson, Thomas','Thomas','','Jefferson','','Apr 13 1990 12:00:00:000AM','104131743',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,8,2,3,NULL,'','',0,'AAAC30E6-551A-4349-B3B0-B16EFABCAAB7')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(10,'397F411E-F501-4454-894C-EA751F9DE7E4','P1008','','ST1004',NULL,'Madison, James Jr','James','','Madison','','Mar 16 1990 12:00:00:000AM','903161751',NULL,1,2,'M',NULL,NULL,NULL,NULL,9,4,NULL,8,'','',0,'42DFC849-1989-41EB-8AE2-E2A108F6053F')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(11,'3B8D0472-B748-4F4F-BA67-F0E9A023BC28','P1009','','ST1001',NULL,'Washington, George','George','','Washington','','Feb 22 1990 12:00:00:000AM','902221732',NULL,NULL,2,'M',NULL,NULL,NULL,NULL,10,NULL,NULL,NULL,'','',0,'E63B1391-E7EC-4921-BABB-8825C82A7A70')
INSERT INTO [person] ([PersonID],[PersonGUID],[PersonNumber],[StudentNumber],[StaffNumber],[AltPersonNumber],[DisplayName],[FirstName],[MiddleName],[LastName],[PreferredName],[DateOfBirth],[SocialSecurityNumber],[ProperTitleID],[GenerationID],[GenderID],[GenderDescriptor],[EducationLevelID],[MaritalStatusID],[ReligionID],[ResidencyStatusID],[PhotographID],[MailingAddressID],[PhysicalAddressID],[UserID],[CountryOfResidence],[EmployerName],[IsHomeless],[RowVersion])VALUES(12,'6194B294-F02E-41CB-8494-57B1DD290E45','P1010','S1005','',NULL,'Washington, Martha','Martha','','Washington','','May 15 1991 12:00:00:000AM','902104557',NULL,NULL,1,'F',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','',0,'48601CC4-5162-42DE-8B79-45CB9FCCDFDA')
|||Actually, you should use the TOP query since that will give better performance than the ROW_NUMBER one. And you don't seem to be filtering on the row number value in your query also. So it should produce more rows than the TOP 2.
