Finding the status on a given date

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)

Finding the status on a given date

Author

Topic

azshep
Starting Member

1 Post

Posted - 2008-02-28 : 18:05:05

Hello,

I have a table that holds enrollment and withdrawal codes (separate columns) for a student along with an effective date. When a student is enrolled, an entry is created with an effective date and an entry code. That student is 'enrolled' from that point forward until a similar withdrawal entry is created. What I am trying to do is this: Given a date, pull a list of students that were enrolled on that date.

The table structure is like this:

CREATE TABLE #ENROLLMENT (
[STUDENTID] INT,
[EFFECTIVEDATE] SMALLDATETIME,
[ENTERCODE] CHAR(3),
[LEAVECODE] CHAR(3))

Sample data:

INSERT INTO #ENROLLMENT (STUDENTID,EFFECTIVEDATE,ENTERCODE,LEAVECODE) 
SELECT 1234,'8/1/2007','E1',NULL UNION ALL
SELECT 1234,'9/15/2007',NULL,'W2' UNION ALL
SELECT 1234,'10/20/2007','R3',NULL UNION ALL
SELECT 6789,'8/1/2007','E1',NULL UNION ALL
SELECT 6789,'2/20/2008',NULL,'W3' UNION ALL
SELECT 6789,'2/26/2008','R3',NULL UNION ALL
SELECT 6789,'2/28/2008',NULL,'W4'

I would like to be able to query STUDENTID that are enrolled on 2/27/2008. Sample data should return both students. The actual codes don't mean much for this query...any code in 'ENTERCODE' is an entry and any code in 'LEAVECODE' is a withdrawal.

Thank you for any help that might point me in the right dirrection.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 18:30:08

[code]DECLARE @Enrollment TABLE (StudentID INT, EffectiveDate SMALLDATETIME, EnterCode VARCHAR(3), LeaveCode VARCHAR(3))

SET DATEFORMAT MDY

INSERT @Enrollment
SELECT 1234, '8/1/2007', 'E1', NULL UNION ALL
SELECT 1234, '9/15/2007', NULL, 'W2' UNION ALL
SELECT 1234, '10/20/2007', 'R3', NULL UNION ALL
SELECT 6789, '8/1/2007', 'E1', NULL UNION ALL
SELECT 6789, '2/20/2008', NULL, 'W3' UNION ALL
SELECT 6789, '2/26/2008', 'R3', NULL UNION ALL
SELECT 6789, '2/28/2008', NULL, 'W4'

DECLARE @SearchDate SMALLDATETIME

SET @SearchDate = '2/27/2008'

SELECT r.StudentID,*
FROM @Enrollment AS r
INNER JOIN (
SELECT StudentID,
MAX(EffectiveDate) AS maxDate
FROM @Enrollment
WHERE EffectiveDate <= @SearchDate
AND EnterCode IS NOT NULL
GROUP BY StudentID
) AS q ON q.StudentID = r.StudentID
LEFT JOIN (
SELECT StudentID,
MIN(EffectiveDate) AS minDate
FROM @Enrollment
WHERE EffectiveDate >= @SearchDate
AND LeaveCode IS NOT NULL
GROUP BY StudentID
) AS p ON p.StudentID = r.StudentID
WHERE r.EffectiveDate = q.maxDate
AND (r.LeaveCode IS NULL OR p.minDate IS NOT NULL)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources