Date range grouping

I'm producing a report. Ok, it's in Business Objects but that's really just a front end to producing the Oracle SQL.

This is simplified. My table has two columns a date col and a number column (say MyDate and MyVal)

I want to sum MyVal by date. Easy

Select TRUNC(MyDate), SUM(MyVal)
from MyTab
group by TRUNC(MyDate)

This is easy enough. basically I get a value for each date. The bit that's puzzling me is that I want four records to be displayed.

Sum of values with a date before today, Sum of values with date today, thSum of values ose with date tomorrow and Sum of values with date after tomorrow. i.e. I need to come up with an expression on MyDate so that I'll get four records back. It seems like I need a version of DECODE that will work on inequalities. Any ideas anyone?

I'm trying to avoid any pre-processing on the underlying table i.e. add an extra column and populate it with SQL scripts. This is becuase I can;t do this in Business Objects. At least, I don't know how.

--
Alan D. Mills Received on Wed Oct 07 1998 - 06:41:09 CDT