I hope this make sense...
When students leave school (dropout, medical reasons, etc), there is a date field that admission use to record this event. For program evaluation, I want to know what program (Electronics, Nursing, Drafting, etc) they were in or assigned at the time when they went on leave. My problem is that some times students switch programs several times and I only want the closest assignment program to the Leave date range.
So for instance:
I have the fields Leave_Begin_date and Assignment_begin_date
I enter a date range between 2/5/07 and 2/15/07 for the leave_begin_date, the query should return 17 cases; however because some students switch departments I get 42 cases with some “duplicates and triplicates. Because I don’t care any assignments greater than the Leave_begin_date I use Assignment_begin_date<= to the leave_begin_date so this gets rid of anything higher than the leave date range 32 cases now. So here is where I need assistance
For instance:
John Doe, Assigned to Electronics 1/1/07 (don’t need it)
John Doe, Assigned to Drafting 1/15/07 (don’t need it)
John Doe, Assigned to Nursing 2/1/07 In this case I only want this
John Doe, Assigned to Manufacturing 2/20/07 (this will be eliminated with <= Leave date)
Please let me know if additional information is needed it such as sql or meta data.
Jose
|