Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 10:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:30 PM
Points: 120, Visits: 346
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

StudentID	StudentKey	SchoolID	SchoolKey	EntryDate	EntryCode	WithDrawalDate	WithDrawalCode
0082600 740682 103 6540 20130201 A 20991231 NULL


but desired out put is

StudentID	StudentKey	SchoolID	SchoolKey	EntryDate	EntryCode	WithDrawalDate	WithDrawalCode
0082600 740681 101 6540 20120830 A 20120128 NULL
0082600 740682 103 6540 20130201 A 20991231 NULL


Please help me to do this
Post #1441355
Posted Thursday, April 11, 2013 10:55 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:23 AM
Points: 156, Visits: 599
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.



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1441380
Posted Thursday, April 11, 2013 11:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441381
Posted Thursday, April 11, 2013 11:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:30 PM
Points: 120, Visits: 346
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
Post #1441384
Posted Thursday, April 11, 2013 11:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441387
Posted Thursday, April 11, 2013 5:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 364, Visits: 385
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?

select	e.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

Post #1441528
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse