Date Query

[jonest454]

I have a working query that I am wanting to change. The query as is:
select studentid, count(absences) AS DAYS
FROM ((SELECT ATTENDANCE.STUDENTID, Count(*)as absences
FROM PSNAVIGATOR.ATT_CODE_CODE_ENTITY ATT_CODE_CODE_ENTITY, PSNAVIGATOR.ATTENDANCE ATTENDANCE, PS.ATTENDANCE_CODE ATTENDANCE_CODE, PS.CODE_ENTITY CODE_ENTITY, PS.STUDENTS STUDENTS
WHERE ATTENDANCE.ATTENDANCE_CODEID = ATTENDANCE_CODE.ID AND ATTENDANCE.STUDENTID = STUDENTS.ID AND ATT_CODE_CODE_ENTITY.ATTENDANCE_CODEID = ATTENDANCE_CODE.ID AND ATT_CODE_CODE_ENTITY.CODE_ENTITYID = CODE_ENTITY.ID AND ((ATTENDANCE_CODE.PRESENCE_STATUS_CD='Absent') AND (ATTENDANCE.SCHOOLID=32))
GROUP BY ATTENDANCE.STUDENTID, ATTENDANCE.ATT_DATE,students.FTEID, STUDENTS.LASTFIRST, STUDENTS.GRADE_LEVEL, ATTENDANCE.SCHOOLID, ATTENDANCE.ATTENDANCE_CODEID, ATTENDANCE.TOTAL_MINUTES, ATTENDANCE_CODE.ATT_CODE, ATTENDANCE_CODE.PRESENCE_STATUS_CD, CODE_ENTITY.CE_CODE
HAVING (((count (*)>3) AND (ps.students.FTEID='30')) or ((count (*)>2) AND (ps.students.FTEID='40'))or ((count (*)>2) AND (ps.students.FTEID='38'))) and (CODE_ENTITY.CE_CODE='Unexcused')))
group by studentid;

As you may be able to tell I am just getting started and this may be the worst way to get the data that I need. I am needing to replace some of the last HAVING statement. The FTEID that is in the students table is the current FTEID. A student could have several FTEIDs and they are stored in another table for enrollment history. The student table has a starting date, a ending date, and the FTEID. The enrollment history table has a starting date, a ending date and a FTEID. What I would like to do is pull the FTEID that a student had on the date that is in the attendance record rather than just use the current FTEID. I could change the HAVING statement to use "DayFTEID", but I am getting hung up on how to get what the FTEID is for that date where it could be in one of 2 tables.

A student may have enrolled on 06/01/2009 and then left on 10/10/2009 with a FTEID of 30, then came back on 10/31/2009 and left again on 11/12/2009 with a FTEID of 40, and then once again came back on 12/10/2009 with a FTEID of 35. With that there would be one record in the student table with a start date of 12/10/2009 and a end date of 05/25/2010( the end of school) and 2 records in the enrollment history table for the entries before. If the student had a attendance record on 08/05/2009 I need to be able to pull the FTEID for that date from the first enrollment getting a value of 30, and if there was a attenance record for 12/15/2009 I would need to pull the FTEID value that they had on that day (35).

I have tried to search the post to see if I can find something else that would work, but I have had no success. I think that I could make something work if all of the data was in one table, but I am having problems trying to find a way to pull the data when it may be in one table or the other ( but not both).

Thanks

[PAVB]

wow wow wow! ... too much info for my personal taste; can't find the question!

Would you consider asking the question in a short-compact sentence?

[jonest454]

How can you query a value that may be in one of 2 tables?

[PAVB]

select  1
from    table_a a,
        table_b b
where   a.column_x = 'desired_value'
    or  b.column_y = 'desired_value'
;

[jonest454]

for a example:
If I have:

Table 1
ID, Start Date, End Date, Value
1, 8/6/2009, 5/25/2010 2
2, 8/6/2009, 5/25/2010 5
3, 10/1/2009, 5/25/2010 6
4, 12/16/2009, 5/25/2010 9
5, 11/15/2009, 5/25/2010 8


Table 2
ID Start Date, End Date, Value
5, 8/6/2009, 9/9/2009, 3
3, 8/6/2009, 9/30/2009, 3
5 9/14/2009 11/10/2009, 5
4 8/6/2009, 12/15/2009, 2


Table 3
Row, ID, Date
1, 5, 8/12/2009
2, 5, 9/30/2009
3, 5, 12/1/2009

If I do a lookup for ID#5 for 08/12/2009 in row 1 from Table 3, I would find that date is between the starting and ending dates for a record in table 2 and I would get back the value of 3.
A lookup for ID#5 for a date of 12/01/2009 in row 3 would be a date between the starting and ending dates in table 1 and would return a value of 8.
ID# 5, date, 09/30/2009, row 2 value 5.

Select value
From Table_1 1,
Table_2 2,
Table_3 3
Where 3.Date between 1.start_date and 1.end_date
Or 3.Date between 2 start_date and 2.end_date
;

What I am not sure how to do is return the value from table one if the where is from table one and the value from table 2 if the where is from table 2.