records in a 'snapshot' range

[Daron]

We have student enrollment data in a table with a (simplified) format
of:
ID
Date
Enroll_Reason

Enroll_reason can be Start or Stop.
A student may have multiple sets (Start, Stop, Start), but will always
have at least one Start.
Example:

0001, 09-01-2007, Start
0002, 09-01-2007, Start
0002, 10-31-2007, Stop
0002, 01-01-2008, Start
0003, 11-01-2008, Start

We need to be able to pull all students with enrollments during a
specified time period using two parameters , and I am at a loss as to
how to structure the SQL. I know this will have to do a self-join, but
not to sure how to even begin to do the filter to not overlap into a
possible next start.

I hope this makes sense! Any help is greatly appreciated!

[Salad]

Re: records in a 'snapshot' range

Daron wrote:

We have student enrollment data in a table with a (simplified) format
of:
ID
Date
Enroll_Reason
>
Enroll_reason can be Start or Stop.
A student may have multiple sets (Start, Stop, Start), but will always
have at least one Start.
Example:
>
0001, 09-01-2007, Start
0002, 09-01-2007, Start
0002, 10-31-2007, Stop
0002, 01-01-2008, Start
0003, 11-01-2008, Start
>
We need to be able to pull all students with enrollments during a
specified time period using two parameters , and I am at a loss as to
how to structure the SQL. I know this will have to do a self-join, but
not to sure how to even begin to do the filter to not overlap into a
possible next start.
>
I hope this makes sense! Any help is greatly appreciated!

Perhaps you can add some more information. What are the two parameters;
FromDate and ToDate?

If so, what data do you want to see? Let's say the from/to dates are
9/1/2007 to 11/1/2007. ID 0002 would have 2 starts. Is that what you
want to see? Or do you want to see the first start date or do you want
to see the last start date?

What would you like to see as you output display?

Are you using a form to pass as the parameters for the query? Are you
having difficuly with the paramters or with the SQL?

[Daron]

Re: records in a 'snapshot' range

On Nov 4, 11:17 am, Salad <o...@vinegar.c omwrote:

Daron wrote:

We have student enrollment data in a table with a (simplified) format
of:
   ID
   Date
   Enroll_Reason

>

Enroll_reason can be Start or Stop.
A student may have multiple sets (Start, Stop, Start), but will always
have at least one Start.
Example:

>

0001, 09-01-2007, Start
0002, 09-01-2007, Start
0002, 10-31-2007, Stop
0002, 01-01-2008, Start
0003, 11-01-2008, Start

>

We need to be able to pull all students with enrollments during a
specified time period [one parameter] /correction/ , and I am at a lossas to
how to structure the SQL. I know this will have to do a self-join, but
not to sure how to even begin to do the filter to not overlap into a
possible next start.

>

I hope this makes sense! Any help is greatly appreciated!

>
Perhaps you can add some more information.  What are the two parameters;
FromDate and ToDate?
>
If so, what data do you want to see?  Let's say the from/to dates are
9/1/2007 to 11/1/2007.  ID 0002 would have 2 starts.  Is that what you
want to see?  Or do you want to see the first start date or do you want
to see the last start date?
>
What would you like to see as you output display?
>
Are you using a form to pass as the parameters for the query?  Are you
having difficulty with the parameters or with the SQL?

I need to see what students have valid Start enrollment record on, or
before, a specific date. We would only be providing one date (sorry
about the miss-information previously stated). If the Stop date is
after the specified date, then the Start record is valid. if the Stop
Date is before the specified date, then the student is not enrolled,
therefore not valid.

Part of the problem is how to 'flatten' the enrollment records into
valid Start/Stop pairs, especially when there pair is really only a
Start date with no Stop date.

Using the above data set. If we provide a date of 10-01-2007, then
student 0001 and 0002 would be reported with the start dates of
09-01-2007. 0003 would not show, and 0002's Start of 01-01-2008 would
also not show as this would be after the provided date.

I'm having problems with how to write the SQL. I am fairly good with
writing SQL, but this one is baffling me. I would use a form to
provide the source for the parameter, which I've done in the past.

Thanks Salad. I know from past posts that you are one that can at
least point me in the right direction.