Extracting time periods from between start and end dates

[cjashwell]

I wonder if anyone can help with this problem.

I have two tables, edhistory and cal_term. The edhistory records the start and end dates of a pupil’s time at a particular school. The relevant fields are:

SchoolID
PupilID
StartDate
EndDate

The cal_term table contains details about the school calendar, broken down into terms (we mainly use terms rather than semesters in the UK).

Relevant fields are:

TermStartDate
TermEndDate
TermName
SchoolYear

Sample data from edhistory:

SCHOOLID PUPILID STARTDATE ENDDATE
2035200 22686 09/01/2001 06/09/2004
2032024 22686 07/09/2004 27/07/2006
2034271 22686 05/09/2006

Sample data from cal_term:

AC_YEAR TERM_NAME TERM_START_DATE TERM_END_DATE
06/07 Autumn 04/09/2006 15/12/2006
06/07 Spring 03/01/2007 30/03/2007
06/07 Summer 16/04/2007 25/07/2007
07/08 Autumn 03/09/2007 21/12/2007
07/08 Spring 07/01/2007 04/04/2008
07/08 Summer 21/04/2008 23/07/2008

What I want to achieve is a query that will show for each term what school the pupil was registered at. So, for this example, the pupil with PupilID 22686, it would show:

06/07 Autumn 2034271
06/07 Spring 2034271
06/07 Summer 2034271
07/08 Autumn 2034271

Since the date is now 23/11/07, and this falls within the Autumn term of the 07/08 year, and the pupil has no EndDate for the school 2034271, this would be the last record.

Since there are no rows in the cal_term table for earlier than September 2006, no rows would be returned for the edhistory records up to July 2006.

Is this possible, and if so, what would be the best SQL?

(For information, the actual implementation needs to be in Oracle (9i), but I don’t know of any specific functions in Oracle that would help).

Thanks in advance for any help.

 

[PHV]

Perhaps this ?
SELECT AC_YEAR, TERM_NAME, SCHOOLID
FROM cal_term INNER JOIN edhistory ON TERM_END_DATE >= STARTDATE
WHERE PUPILID = 22686
AND (TERM_START_DATE <= ENDDATE OR ENDDATE IS NULL)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cjashwell]

PHV - thanks for your swift reply! That works well.

(I may come back to this thread with an additional related question if I can't figure the rest of my problem out)

Best wishes,
cjashwell

 

[cjashwell]

OK, I need a bit more help.

Call the edhistory table sen_provision. It has the following columns:

location_id
referral_id
start_date
end_date
provision_code
hours_per_week
date_agreed

The primary key is a composite of referral_id, provision_code and date_agreed.

The cal_term table has this structure:

term_start_date
term_end_date
term_name
term_id
ac_year
template_name

The primary key is a composite of ac_year, template_name and term_id

Sample data from sen_provision:

LOCATION_ID REFERRAL_ID START_DATE END_DATE PROVISION_CODE HOURS_PER_WEEK DATE_AGREED
3034007 44463 03/09/2007 IST 1 03/09/2007
2032923 44463 12/09/2006 02/10/2006 LSA 10 12/09/2006
2032923 44463 02/10/2006 30/03/2007 LSA 15 02/10/2006
3034007 44463 31/03/2007 LSA 15 31/03/2007
2032923 44463 12/09/2006 02/10/2006 TS 1 12/09/2006

Sample data from cal_term:

AC_YEAR TEMPLATE_NAME TERM_START_DATE TERM_END_DATE TERM_ID TERM_NAME
07/08 0708_a 03/09/2007 21/12/2007 1 Autumn
07/08 0708_a 07/01/2007 04/04/2008 2 Spring
07/08 0708_a 21/04/2008 23/07/2008 3 Summer
07/08 0708_f 01/04/2007 31/08/2007 1 Summer
07/08 0708_f 01/09/2007 31/12/2007 2 Autumn
07/08 0708_f 01/01/2008 31/03/2008 3 Spring

The cal_term table shows different sets of dates relating to schools. TemplateNames ending in ‘a’ are academic terms, those ending in ‘f’ are financial year – i.e. April to March.

Sample data from sen_provider_cost:

PROVIDER_TYPE COST_PER_HOUR
LSA 12.63
IST 44.59

What I want to achieve is a report that will show for each School, each Pupil who has provision (i.e. one or more rows in sen_provision) for each term, and then show the total cost for each provision type, based on the number of hours of provision over the period (i.e. term), within a financial year.

Because I only want a single financial year’s data returned, I can use the TemplateName in a where condition.

I want the SchoolID, then the name of the year and the terms, then the total LSA for the term, and then the total IST for the term, and then a sum of these two figures.

The query I have so far, based on PHV’s help above, looks like this:

SELECT sp.LOCATION_ID, ct.AC_YEAR, ct.TERM_NAME,  
(select ((sp.HOURS_PER_WEEK * round((case when (sp.END_DATE < ct.TERM_END_DATE or sp.end_date is null) then ct.TERM_END_DATE else sp.END_DATE end -
case when sp.start_date < ct.term_start_date then ct.TERM_START_DATE else sp.START_DATE end)/7))*spc.COST_PER_HOUR)
from sen_provider_cost spc
where spc.provider_type = sp.PROVISION_CODE
and sp.PROVISION_CODE = 'LSA') lsa_amnt,
(select ((sp.HOURS_PER_WEEK * round((case when (sp.END_DATE < ct.TERM_END_DATE or sp.end_date is null) then ct.TERM_END_DATE else sp.END_DATE end -
case when sp.start_date < ct.term_start_date then ct.TERM_START_DATE else sp.START_DATE end)/7))*spc.COST_PER_HOUR)
from sen_provider_cost spc
where spc.provider_type = sp.PROVISION_CODE
and sp.PROVISION_CODE = 'IST') ist_amnt
FROM cal_term ct INNER JOIN sen_provision sp ON ct.TERM_END_DATE >= sp.START_DATE
WHERE sp.REFERRAL_ID = 44463
AND (ct.TERM_START_DATE <= sp.END_DATE OR sp.END_DATE IS NULL) 
and ct.template_name = '0708_f'

And this gives me:

LOCATION_ID AC_YEAR TERM_NAME LSA_AMNT IST_AMNT
3034007 07/08 Autumn 758.03
3034007 07/08 Spring 579.67
3034007 07/08 Summer 4167.9
3034007 07/08 Autumn 3220.65
3034007 07/08 Spring 2462.85

I want only three rows, because the provision start and end dates all fall within the three terms (Summer, Autumn and Spring) of the 07/08 financial year. I then want the amount (based on the cost_per_hour from sen_provider_cost, multiplied by the number of hours_per_week, multiplied by the number of weeks between the start and end dates of the provision) for each provision type (in the example above, LSA and IST).

What I want to get as a result would look something like this.

LOCATION_ID AC_YEAR TERM_NAME LSA_AMNT IST_AMNT TOTAL
3034007 07/08 Summer 4167.9
3034007 07/08 Autumn 3220.65 758.03 3978.68
3034007 07/08 Spring 2462.85 579.67 3042.52


I think I need to group the query and use sub-queries for the LSA_AMNT and IST_AMNT and TOTAL columns, but whenever I try to get the group right, I just can’t get a handle on it.

As you can see, I have used Oracle’s CASE function to derive the start and end dates for the provision within the sub-query. I guess this isn’t supported in ANSI SQL, so if you think I should take this question to an Oracle forum please let me know! Alternatively, if you can guide me towards doing this in pure ANSI SQL that would be great.