Multiple Date Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Date Records

Author

Topic

VJG
Starting Member

17 Posts

Posted - 2012-08-13 : 13:06:50

I need help: I work at a school district and need help creating a report.

I have a table named entry_with which contains contains student withdraw information along with entry information.

the columns are: StudentID (SID), EntryDate (ED), EntryCode (EC), Building (sch), WithdrawalDate (WD), WithdrawalCode (WC)

The issue I'm having is that a student may multiple entry dates and
withdrawal dates (if he/she transferred from school to school)

I"m need a historical report of just the last school they withdrew from and the newest school they enrolled to.

right now my data looks Something like this:

withdraw records
001 (SID) 142 (SCH) 2/1/2012 (WD) ID (WC)
001 (SID) 143 (SCH) 6/1/2012 (WD) YR (WC)
enroll record
001 (SID) 143 (SCh) 2/1/2012(ED) 4 (EC)

basically this student had an in-district transfer from School code 142 to school code 143 on 2/1/2012 then at year end they were withdrawn from school code 143 for year-end purposes.

I'm try to get the withdraw latest withdraw and entry dates.

what is the best way to accomplish this?

Thanks in advance for your help!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 13:30:00

see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VJG
Starting Member

17 Posts

Posted - 2012-08-13 : 14:53:34

thanks for the reply..

however, my data is stored in the same table

quote:
Originally posted by visakh16

see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 14:57:30

quote:
Originally posted by VJG

thanks for the reply..

however, my data is stored in the same table

quote:
Originally posted by visakh16

see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thats fine. you can follow the same logic and use same table twice relating them on required conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 15:09:37

also is there a a field which indicates whether its an entry or withdrawal record? or is it depending on the date fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VJG
Starting Member

17 Posts

Posted - 2012-08-14 : 10:37:53

sorry for not getting back with you yesterday, but i got called away to a meeting and didn't get chance to get back on the project.

i think i might of misguided you yesterday.

all records are entry records, however when a student withdraws a date is populated. If the student is still enrolled the WithdrawalDate and WithdrawalCode is null.

i've taken a screen print, maybe this explains it a little better..

http://i83.photobucket.com/albums/j284/javigarza/sample.png

anyway, like i said yesterday, some students have more than one entry record (with withdrawal dates) because the moved to different school within the district.

Thanks in advance for your help. Much appreciated..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:48:33

[code]
SELECT SID,
ED,
EC,
sch,
WD,
WC
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SID,CASE WHEN WC IS NULL THEN 2 ELSE 1 END ORDER BY CASE WHEN WC IS NULL THEN ED ELSE DATEDIFF(dd,WD,'99991231') END) AS Seq,*
FROM table
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VJG
Starting Member

17 Posts

Posted - 2012-08-14 : 11:41:42

you my friend are a genius! thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 12:48:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources