John,<br /> Sub-selects to the rescue. See below.<br /><br /> select s1.asx_code, s1.bdate AS date, s1.low, s1.high,
s2.open,s3.close, s1.volume<br /> from (select asx_code, date_trunc('week', date) AS bdate, max(date) AS edate,
min(low)AS low, max(high) AS high, sum(volume) AS volume<br /> from sales_summaries<br /> group by
asx_code,date_trunc('week', date)) s1, sales_summaries s2, sales_summaries s3<br /> where s1.bdate = s2.date<br /> and
s1.asx_code=s2.asx_code<br/> and s1.edate = s3.date<br /> and s1.asx_code=s3.asx_code;<br /><br /> asx_code
| date | low | high | open | close | volume <br />
------------+------------------------+------+------+------+-------+-----------<br/> TLSCA | 2006-12-04
00:00:00-05| 2.28 | 2.52 | 2.31 | 2.51 | 243406646<br /> TLSCA | 2006-12-11 00:00:00-05 | 2.5 | 2.65 | 2.5 |
2.62| 170551800<br /><br /> The "date" is based on ISO-8601 (in other words the week starts on Monday). Be warned, as
itis questionable how this will scale. It may require expression (function based) indexes.<br /><br />Oracle has a
featurecalled analytic functions, which would allow you to use functions such as LEAD, LAG, FIRST_VALUE, LAST_VALUE.
Inparticular FIRST_VALUE and LAST_VALUE would have been useful to determine the open and close for a week, but before
analyticsin Oracle you would use sub-selects or multiple joins.<br /><br /><br />
