retrieving latest records in a date range problem

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

SQL Server 2000 Forums

Transact-SQL (2000)

retrieving latest records in a date range problem

Author

Topic

LLatinsky
Starting Member

38 Posts

Posted - 2007-04-23 : 15:44:47

--Hello, I am having a problem retrieving data for my report. Below /*is a simplified version of what I am dealing with
table student keeps current information about a student and his status*/
create table student (studentid int not null identity(1,1), lastname varchar(32) not null, currentstatus int not null-- ... etc, status is coming from predefined table of values, etc
constraint pk_student primary key (studentid))
--each time there is an admission a record gets written into the
--admission table (including the current one)
create table admissions (recid int not null identity(1,1),
studentid int not null
constraint fk_admissions_student foreign key(studentid) references student (studentid), startdate datetime not null, enddate datetime not null
constraint df_adm default '9/9/9999')--- etc...

--each time there is a student status change a record gets written
--to studentstatushistory (including current)
create table studentstatushistory(recid int not null identity (1,1), studentID int not null
constraint fk_studentstatushistory_student foreign key(studentid) references student (studentid),
status int not null, changedate datetime not null, enddate datetime not null
constraint df_statuschange default '9/9/9999')--- etc...
----sample data
insert into student select 'jones', 123
insert into admissions select 1,'3/5/2007','9/9/9999'
union all select 1,'1/1/2007','3/5/2007'
union all select 1, '1/9/2005','1/1/2007'

insert into studentstatushistory
select 1, 123,'3/5/2007' ,'9/9/9999'
union all select
1, 122, '2/1/2007','3/5/2007'
union all select
1, 124, '1/1/2007','2/1/2007'
union all select
1, 122, '1/9/2005','1/1/2007'

--I am having a problem extracting the latest status for each
--admission period
select*from admissions a join studentstatushistory s on
a.studentid=s.studentid and s.changedate >= a. startdate and s.changedate<a.enddate
-- will produce more results that are in admissions table
-- join (select max(s.changedate) as changedate , studentid from studentstatushistory group by ......having trouble)m
-- on s.studentid=m.studentid and m.changedate=s.changedate

-- what is the best way to deal with this kind of date ranges?
-- thank you very much for your help

LLatinsky
Starting Member

38 Posts

Posted - 2007-04-23 : 16:16:46

hello again
I actually cme up with a statement but I don't think whether this is the best solution

select*from admissions a join studentstatushistory s on
a.studentid=s.studentid and s.changedate >= a. startdate and s.changedate<a.enddate
where s.changedate in(select max(changedate) from studentstatushistory
where s.studentid=studentid and changedate >= a.startdate and changedate<a.enddate
group by studentid, a.recid)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-23 : 16:17:11

you are very close, you just have things a little out of order. Also, replace * with actual columns that you need:


select 
  a.*, s.*
from 
  admissions a 
inner join  
  (
    select studentID, max(s.changeDate) as MaxDate 
    from studentstatusHistory 
    group by studentID
  ) x
  on x.studentID = a.studentID
inner join
  studentstatushistory s on s.studentid=x.studentid and s.changeDate = x.maxDate

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-23 : 16:19:22

on a side note -- please try to use good indenting and formatting of your code, it is really hard to read and interpret as presented, not only for us to help you but probably for you to read and work with as well.

- Jeff
http://weblogs.sqlteam.com/JeffS

LLatinsky
Starting Member

38 Posts

Posted - 2007-04-23 : 16:32:35

Sorry for the weird formatting, I was writing the whole message in the message window and then commented out everything that was not sql.
Unfortunately what your query does is selects the max stutus change date for the student, not for the student during a given admission period. My second reply produces the result I need, it just looks really clumsy. I was just trying to figure out how to relate the admissions and the status history table in the group by - the surrogate key helped. I just hated to do a group by on the student and a concatenated string of converted admission dates

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-23 : 16:45:45

I'm sorry, I keep staring at your original post trying to make heads or tails out of it and I jsut get a headache. I'm sure others are having a similar reaction. If you can present your schema clearly (with some sample data as well) we may be able to provide a good solution, but for now I just don't have time to take all of your code and manually format it myself for you.

- Jeff
http://weblogs.sqlteam.com/JeffS

LLatinsky
Starting Member

38 Posts

Posted - 2007-04-23 : 17:00:35

I apologize if my post caused headaches. Here's a formatted version of what I needed:
retrieve the latest student status in a given admissions period

USE northwind
CREATE TABLE student(
studentid int NOT NULL IDENTITY (1, 1),
lastname varchar(32) NOT NULL,
currentstatus int NOT NULL
CONSTRAINT pk_student PRIMARY KEY (studentid))

CREATE TABLE admissions(
recid int NOT NULL IDENTITY (1, 1),
studentid int NOT NULL
CONSTRAINT fk_admissions_student FOREIGN KEY (studentid) REFERENCES student(studentid), startdate datetime NOT NULL,
enddate datetime NOT NULL
CONSTRAINT df_adm DEFAULT '9/9/9999')

CREATE TABLE studentstatushistory(
recid int NOT NULL IDENTITY (1, 1),
studentID int NOT NULL
CONSTRAINT fk_studentstatushistory_student FOREIGN KEY (studentid) REFERENCES student(studentid),
status int NOT NULL,
changedate datetime NOT NULL,
enddate datetime NOT NULL CONSTRAINT df_statuschange DEFAULT '9/9/9999')

INSERT INTO student
SELECT 'jones', 123

INSERT INTO admissions
SELECT 1, '3/5/2007', '9/9/9999'
UNION ALL
SELECT 1, '1/1/2007', '3/5/2007'
UNION ALL
SELECT 1, '1/9/2005', '1/1/2007'

INSERT INTO studentstatushistory
SELECT 1, 123, '3/5/2007', '9/9/9999'
UNION ALL
SELECT 1, 122, '2/1/2007', '3/5/2007'
UNION ALL
SELECT 1, 124, '1/1/2007', '2/1/2007'
UNION ALL
SELECT 1, 122, '1/9/2005', '1/1/2007'

SELECT *
FROM admissions a INNER JOIN
studentstatushistory s ON a.studentid = s.studentID AND s.changedate >= a.startdate AND s.changedate < a.enddate
WHERE (s.changedate IN
(SELECT MAX(changedate)
FROM studentstatushistory
WHERE s.studentid = studentid AND changedate >= a.startdate AND changedate < a.enddate
GROUP BY studentid, a.recid))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources