Tiny Penguin wrote:Not very many people (who haven't already participated in it) are going to read a thread that already has 16 replies. If you have a new variation on an old problem, like this, I think you'll get better response if you start a new thread. You can include a link to the original thread, if you think that will help.
I was wondering, is it possible to use analytical functions (maybe using Keep?) to get the value of another field associated with a subquery's min/max start/end dates (i.e. the name of the person associated with the min/max value) for periods?KEEP is a keyword (not a function) that is used with the FIRST and LAST functions. If you want to collapse the results down to one row for every distinct combination of pid and period, then aggregate FIRST and LAST is a good way to do it, especially since the column used for ordering (that is, startdate) is unique for each pid.
For example, for period 1, is it possible to return the teacher at the start of the period was 'Mr Smith' and the teacher at the end of the period was 'Mrs Allan' in the same row as the min/max start/end date?
This assumies that you need to derive period, instead of using the desired column in the table.
WITH got_period AS ( SELECT m.* , COUNT ( CASE WHEN startcode IN ('B', 'S') THEN 1 ELSE NULL -- This is the default, but you can explicitly say it if you want to END ) OVER ( PARTITION BY pid ORDER BY startdate ) AS period FROM mytable4 m ) SELECT pid , MIN (startdate) AS period_startdate , MAX (enddate) AS period_enddate , MIN (teacher) KEEP (DENSE_RANK FIRST ORDER BY startdate) AS first_teacher , MIN (teacher) KEEP (DENSE_RANK LAST ORDER BY startdate) AS last_teacher FROM got_period GROUP BY pid, period ORDER BY pid, period ;
The rows returned would look like:Do you want the output to say 'first teacher' and 'last teacher', or do you want it to say things like 'Mr Smith' and 'Mrs Allan'? Please post the exact results that you want, and/or include the desired results in your INSERT statements, like you did before. (Including the desired results in your table is very hady; it makes it easy to see if the answer is right or not.)
pid, startdate1, enddate1, first teacher, last teacher
pid, startdate2, enddate2, first teacher, last teacher
PID PERIOD_STA PERIOD_END FIRST_TEACHER LAST_TEACHER ---- ---------- ---------- ------------- ------------ A657 11/12/2008 25/11/2009 Mr Smith Mrs Allan A657 04/05/2010 17/06/2010 Mr Johnson Mr Johnson A657 17/06/2010 19/07/2010 Mr Coates Mr Coates A657 29/09/2010 20/04/2011 Mrs Jackson Mr Watson A657 20/04/2011 Mr Robson Mr Robson
If not, I'd be interested to know how you would get the teachers associated with the start and end of the period. I kind of feel that the way I'd do it - self joining the query to the original table based on PID and startdate/enddate - is wrong?You have good instincts! Just curious, why do you feel that approach is wrong?