April 11, 2013 at 10:05 am
Hello
I need one help to develop logic
please help me to this
create table #Enrollement
(
StudentID Varchar(10),
SchoolID Int,
EntryDate int,
EntryCode Char(3),
WithdrawalDate int,
WithdrawalCode char(3),
SchoolYear int
)
insert into #Enrollement values ('0082600',101,20120830,'A',20120128,NULL,2012)
insert into #Enrollement values ('0082600',103,20130201,'A',20991231,NULL,2012)
create table #Student
(
StudentKey int,
StudentID Varchar(10),
CurrentIEPStatus int,
FRLunch int,
EnrolledSchoolID int,
EnrolledSchoolKey int,
StartDate int,
EndDate int,
CurrentFlag int
)
insert into #Student values (740681,'0082600',0,0,101,6540,20120830,20120930,0)
insert into #Student values (740682,'0082600',0,1,101,6540,20120930,20991231,1)
in requirement, based on EndDate in #Student, I need to display StudentKey and EnrolledSchoolKey.
I try this
select Distinct
E.StudentID,
MAX(S.StudentKey) AS StudentKey,
--S.EnrolledSchoolID,
E.SchoolID,
MAX(s.EnrolledSchoolKey) AS SchoolKey,
E.EntryDate,
E.EntryCode,
E.WithDrawalDate,
E.WithDrawalCode from #Student s
join
#Enrollement E
on E.StudentID = S.StudentID
where S.EndDate <=e.withdrawalDate
and s.StudentID = '0082600'
group by
E.StudentID,
E.SchoolID,
E.EntryDate,
E.EntryCode,
E.WithDrawalDate,
E.WithDrawalCode,
E.SchoolYear
order by StudentKey
and i got output as below
StudentIDStudentKeySchoolIDSchoolKeyEntryDateEntryCodeWithDrawalDateWithDrawalCode
0082600740682103654020130201A 20991231NULL
but desired out put is
StudentIDStudentKeySchoolIDSchoolKeyEntryDateEntryCodeWithDrawalDateWithDrawalCode
0082600740681101654020120830 A 20120128NULL
0082600740682103654020130201A 20991231NULL
Please help me to do this
April 11, 2013 at 10:55 am
Sorry, I would like to help, but there are just too many things about all of this that don't make sense to me.
For example, why are you (seemingly) storing students more than once in the STUDENT table?
And shouldn't there be another table for SCHOOLS, with a single primary key (ID) that you would reference from other tables such as STUDENT, rather than having "schoolid" and "schoolkey" both stored in STUDENT?
If you alter your design so it is properly normalized, you will eliminate redundant and incoherent data, and it will vastly simplify your queries.
April 11, 2013 at 11:00 am
autoexcrement (4/11/2013)
Sorry, I would like to help, but there are just too many things about all of this that don't make sense to me.For example, why are you (seemingly) storing students more than once in the STUDENT table?
If you alter your design so it is properly normalized, you will eliminate redundant and incoherent data, and it will vastly simplify your queries.
Happens to be a normal occurance in a SIS apparently. It was when I worked at a school district. The student table had one (or more) records for each student enrolled for that school year.
My question is what is the value of S.EndDate when this query was run.
April 11, 2013 at 11:06 am
Lynn Pettis (4/11/2013)
autoexcrement (4/11/2013)
Sorry, I would like to help, but there are just too many things about all of this that don't make sense to me.For example, why are you (seemingly) storing students more than once in the STUDENT table?
If you alter your design so it is properly normalized, you will eliminate redundant and incoherent data, and it will vastly simplify your queries.
Happens to be a normal occurance in a SIS apparently. It was when I worked at a school district. The student table had one (or more) records for each student enrolled for that school year.
My question is what is the value of S.EndDate when this query was run.
basically we capture changes during a year so Student table has multiple records.
Start and EndDate is for that purpose of track those changes
April 11, 2013 at 11:10 am
yogi123 (4/11/2013)
Lynn Pettis (4/11/2013)
autoexcrement (4/11/2013)
Sorry, I would like to help, but there are just too many things about all of this that don't make sense to me.For example, why are you (seemingly) storing students more than once in the STUDENT table?
If you alter your design so it is properly normalized, you will eliminate redundant and incoherent data, and it will vastly simplify your queries.
Happens to be a normal occurance in a SIS apparently. It was when I worked at a school district. The student table had one (or more) records for each student enrolled for that school year.
My question is what is the value of S.EndDate when this query was run.
basically we capture changes during a year so Student table has multiple records.
Start and EndDate is for that purpose of track those changes
Quite familiar with this having worked at a k-12 public school district for 5 years. Unfortunately it is difficult to tell you why you got the results you did instead of the expected results since we can't see the data used.
You need to provide us with a reasonable facsimile of your problem (tables, sample data in a readily consummable format -- i.e. cut/paste/run in SSMS) for us to really help.
April 11, 2013 at 5:37 pm
Lynn Pettis (4/11/2013)
My question is what is the value of S.EndDate when this query was run.
I'm taking it to mean that we need to "anchor" the #student table to return only a single version of each StudentID. With that in mind, will something like this work?
selecte.StudentID,
s.StudentKey,
e.SchoolID,
s.EnrolledSchoolKey,
e.EntryDate,
e.EntryCode,
e.WithDrawalDate,
e.WithDrawalCode
from #student s
join #enrollement e on s.enddate >= e.WithdrawalDate and s.StudentID = e.StudentID
where s.StudentID = '0082600'
and s.CurrentFlag = 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply