Re: Date range grouping

From: Grinberg L. <leon_at_grant.kharkov.ua>
Date: Mon, 12 Oct 1998 12:03:33 +0300
Message-ID: <6vsg4g$kpo@grant.grant.UUCP>

Alan D. Mills wrote in message <6vfjvc$dbp$1_at_schbbs.mot.com>...
>This is simplified. My table has two columns a date col and a number
column
>(say MyDate and MyVal)
>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.

Try to combine DECODE and SIGN. For example, next statement selects one record four sums.

SELECT

  sum(decode(sign(trunc(mydate)-trunc(sysdate)),  - 1, MyVal,0)) last_sum,
  sum(decode(sign(trunc(mydate)-trunc(sysdate)),    0, MyVal,0)) today_sum,
  sum(decode(sign(trunc(mydate)-trunc(sysdate+1)), 0,MyVal,0)) tomorrow_sum,
  sum(decode(sign(trunc(mydate)-trunc(sysdate+1)), 1,MyVal,0))

after_tom_sum,
FROM MyTable; Received on Mon Oct 12 1998 - 04:03:33 CDT