Re: Date range grouping

You could try;

SELECT 'Before' WhenDate sum(MyVal) from MyTab where MyDate < Sysdate group by WhenDate
UNION
SELECT 'Today' WhenDate, sum(MyVal) from MyTab where MyDate = Sysdate group by WhenDate
UNION
SELECT 'Tomorrow' WhenDate, sum(MyVal) from MyTab where MyDate = Sysdate + 1 group by WhenDate
UNION
SELECT 'After' WhenDate, sum(MyVal) from MyTab where MyDate > Sysdate + 1 group by WhenDate

with the syntax corrected, can't remember where the 'group by' & column aliases go.

Alan D. Mills wrote in message <6vfjvc$dbp$1_at_schbbs.mot.com>...
>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 - 09:05:54 CDT