Counting work days between dates using a Time Dimension table???

[lennym]

I have created a table called time_dimension which lists all dates from 1996 to 2001.  The table contains a column called the_date with the continuous date values from 1996 to 2001 and a column called countable_day which has a value of 1 for dates that are work days and 0 for dates that are weekends or holidays. I have another table called Cases that contains records with columns of dates such as start_date and end_date.  I want to be able to link the tables and sum the countable_day for a range of records where the record in the cases table has a start_date of "X" and and end_date of "Y". This will give me the number of days that a cases record was active.  Any help anyone????

 

[carp]

I think this might do it:<br><br>SELECT c.case_id, sum(d.countable_day)<br>FROM time_dimension d, cases c<br>WHERE d.the_date BETWEEN c.start_date AND c.end_date<br>AND c.start_date = X <br>AND c.end_date = Y<br>GROUP BY c.case_id;