sql turn dates of adding and removing into date ranges

Question

I am using a timetabling application called CELCAT and trying to pull out some data about when students should have been marked for reporting... This seems to be extremely difficult because of the way the adding and removing of students on registers is structured see below:

studentid  eventid     fromdatetime                 addition  removal
25149     25145    2009-09-12 10:30:00.000     Y        NULL
25149     25145    2009-09-12 10:30:00.000     NULL     Y
25149     25145    2009-09-12 10:30:00.000     Y         NULL
25150     23013    2009-09-08 09:00:00.000     Y         NULL
25150     23554    2009-09-07 09:00:00.000     Y         NULL
25150     25145    2009-09-12 10:30:00.000     Y             NULL
25150     25145    2009-07-27 00:00:00.000     NULL         Y
25150     25145    2009-09-12 10:30:00.000     Y         NULL
25150     25145    2009-09-12 10:30:00.000     NULL         Y
25150     25145    2009-09-12 10:30:00.000     Y         NULL
25150     25148    2009-09-12 15:00:00.000     Y         NULL
25151     25145    2009-09-12 10:30:00.000     Y         NULL
25151     25145    2009-10-10 00:00:00.000     NULL     Y
25152     25145    2009-09-19 10:30:00.000     Y           NULL
25152     25145    2009-07-27 00:00:00.000     NULL     Y

So an addition of a student means they should be marked from that date onwards in the register (registers are weekly reccurring events with their own week profile, I can handle that side of it though). A removal would mean the student doesn't need to be marked past this date, however a student could potentially be added, removed and then re-added in a later week.

What I think would get me in the right direction would be to get a table of structure

studentid    eventid    fromdate                               todate
25149        25145     2009-09-12 10:30:00.000       2009-09-28 10:30:00.000
25149        25145     2009-10-13 10:30:00.000       2009-10-24 10:30:00.000

Any ideas how to do this? Or a better suggestion? I imagine it will involve some use of cursors unless someone has an awesome solution. The tables are designed by CELCAT and cannot be modified.

Oh yeah it's sql server 2005.

EDIT by KM, here is some code to test solutions with:

DECLARE @YourTable table (studentid int
                         ,eventid int
                         ,fromdatetime datetime
                         ,addition char(1) 
                         ,removal char(1)
                         )

SET NOCOUNT ON
INSERT INTO @YourTable VALUES (25149,25145,'2009-09-12 10:30:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25149,25145,'2009-09-12 10:30:00.000', NULL,'Y')
INSERT INTO @YourTable VALUES (25149,25145,'2009-09-12 10:30:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25150,23013,'2009-09-08 09:00:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25150,23554,'2009-09-07 09:00:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25150,25145,'2009-07-27 00:00:00.000', NULL,'Y')
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000', NULL,'Y')
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25150,25148,'2009-09-12 15:00:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25151,25145,'2009-09-12 10:30:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25151,25145,'2009-10-10 00:00:00.000', NULL,'Y')
INSERT INTO @YourTable VALUES (25152,25145,'2009-09-19 10:30:00.000','Y'  ,NULL)
INSERT INTO @YourTable VALUES (25152,25145,'2009-07-27 00:00:00.000', NULL,'Y')
SET NOCOUNT OFF

Answer 1

One simple way would be to create a scalar function which for a studentid and an eventid returned a bit indicating whether that student is in or out:

create function Event_IsStudentIn(@eventID int, @studentID int) returns bit as begin

declare @in bit

set @in = 0

select @in = case when addition = 'Y' then 1 else 0 end from [table] where eventid = @eventID and studentid = @studentID order by fromdatetime desc

return @in

end

However in the future you may want to restructure that data.

Answer 2

I'm not sure of the query you are trying to get and how date ranges will help.

However, you could keep the current table, it is like a log. Just add a summary table that keeps the current state. Insert the old table and UPDATE the new one. you can query the new one to tell if IN/OUT and use the old one for more detailed analysis.

Answer 3

I'm not quite sure what you need to be able to do with the data, but here's how to get the data into the format you wanted.

SELECT studentid, eventid, fromdatetime as fromdate, 
    (SELECT TOP 1 fromdatetime FROM table 
    WHERE studentid = t1.studentid 
     AND eventid = t1.eventid 
     AND removal = 'Y' AND fromdatetime > t1.fromdatetime
    ) AS todate
FROM table t1
WHERE addition = 'Y'

This query will produce the data in the format you asked for:

studentid    eventid   fromdate                      todate
25149        25145     2009-09-12 10:30:00.000       2009-09-28 10:30:00.000
25149        25145     2009-10-13 10:30:00.000       2009-10-24 10:30:00.000

This query will probably be inefficient with lots of data, so I'm really hoping your table has an identity row that's indexed. If it does, replace the AND fromdatetime > t1.fromdatetime with AND id > t1.id. This should make the subquery more efficient.

Answer 4

I think there's something wrong with the sample data First 3 records

25149     25145    2009-09-12 10:30:00.000      Y        NULL
25149     25145    2009-09-12 10:30:00.000      NULL     Y
25149     25145    2009-09-12 10:30:00.000      Y         NULL

Basically they say, the student was added/removed and added again at the same time. However, remember when selecting the order is not guaranteed. How does one know the student wasn't removed first and then added twice? The question asks for the row

25149        25145     2009-10-13 10:30:00.000       2009-10-24 10:30:00.000

in the output, but the '2009-10-13' value is nowhere in the data.

Then these three:

25150     25145    2009-09-12 10:30:00.000        Y             NULL
25150     25145    2009-07-27 00:00:00.000      NULL         Y
25150     25145    2009-09-12 10:30:00.000      Y         NULL

Exactly the hypothetical scenario I described above - judging by the date, the student was removed first and then added twice!