Re: Date range grouping

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 07 Oct 1998 14:00:18 GMT
Message-ID: <361c6f44.1230259@dcsun4.us.oracle.com>

On Wed, 7 Oct 1998 12:41:09 +0100, "Alan D. Mills" <alanmNOSPAM_at_uk.europe.mcd.mot.com> wrote:

>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?

What about just adding a where clause "trunc(myDate) between trunc(sysdate-1) and trunc(sysdate+2)"??

eg.

  1 select
  2 decode( TRUNC(MyDate),

  3      trunc(sysdate-1), 'Yesterday ('||trunc(sysdate-1)||')',
  4      trunc(sysdate),   'Today     ('||trunc(sysdate)||')',
  5      trunc(sysdate+1), 'Tomorrow  ('||trunc(sysdate+1)||')',
  6                    'Day After ('||trunc(sysdate+2)||')' ) "When",

  7 SUM(MyVal) "The Sum"
  8 from MyTab
  9 where trunc(myDate) between trunc(sysdate-1) and trunc(sysdate+2)  10* group by TRUNC(MyDate)
SQL> /

When                     The Sum
--------------------- ----------
Yesterday (06-OCT-98)         95
Today     (07-OCT-98)          3
Tomorrow  (08-OCT-98)         92
Day After (09-OCT-98)         46

I added the decode just to make it look nice.

chris.

>
>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.
Received on Wed Oct 07 1998 - 09:00:18 CDT