Found the answer:
CREATE OR REPLACE VIEW VI_SYSTEM_DAYS
(DAYDATE)
AS
select TRUNC(SYSDATE)-L+1 as DAYDATE from (
select level L from dual connect by level < INTERVAL_DAYS(systimestamp -
TO_TIMESTAMP('2004-02-03','YYYY-MM-DD')) + 2
)
ORDER BY DAYDATE;
"Björn Wächter" <Bjoern.Waechter_at_p3-solutions.de> schrieb im Newsbeitrag
news:3f5k1dF65bslU1_at_news.dfncis.de...
> Hi all,
>
> I have to generate some reportviews.
> The report shall show one row for each
> day since day X. Since the tables where
> I get the information from don't have
> to have an entry for each day I don't
> know how to generate the row for
> missing days.
>
> An example:
>
> Table BIRTHAY:
>
> | DAY | Born_Baby |
> ---------------------------
> | 2005-11-01 | Alex |
> | 2005-11-01 | Olli |
> | 2005-11-01 | Marc |
> | 2005-11-03 | Tom |
> | 2005-11-04 | Sven |
> | 2005-11-04 | Bjoern |
>
>
> SELECT
> DAY,
> COUNT(*) BORN
> FROM
> BIRTHAY
> GROUP BY DAY;
>
>
> Result:
>
> | DAY | BORN |
> ----------------------
> | 2005-11-01 | 3 |
> | 2005-11-03 | 1 |
> | 2005-11-04 | 2 |
>
>
> The problem is that I need the
> row with the entry:
>
> | DAY | BORN |
> ----------------------
> | 2005-11-02 | 0 |
>
>
> How can I get the row?
>
>
> Thanks Björn
>
>
>
>
>
>
Received on Fri May 20 2005 - 07:59:18 CDT
