Re: problem with date-funktion

From: Ralf Butter <ralf_butter_at_drewag.de>
Date: 11 Feb 2003 23:50:44 -0800
Message-ID: <822f10df.0302112350.6e35f75c_at_posting.google.com>

"Patrick Sch fer" <schaeferpatrick_at_gmx.de> wrote in message news:<3e4927f5$0$28053$9b622d9e_at_news.freenet.de>...
> Hallo,
> i have a problem:
>
> i have a database table, that includes all dates, when a hotelroom is taken.
> example.
>
> for room number 1 (is taken)
>
> 1 2003-03-02 - 2003-03-20
> 1 2003-03-25 - 2003-03-28
> 1 2003-04-10 - 2003-04-15
> .
> .
> .
> who can i create a table that describe when room number 1 is not taken?
> like for example
>
>
> 1 - 2003-03-02
> 1 2003-03-20 - 2003-03-25
> 1 2003-03-28 - 2003-04-10
> 1 2003-04-15 -
>
>
> thank you for your help

Hallo, Patrick,

this could be the solution

SELECT * FROM zimmer
ORDER BY nr,von;

        NR VON      BIS
         1 02.03.02 20.03.02
         1 25.03.02 28.03.02
         1 10.04.02 15.04.02
         2 01.03.02 05.03.02
         2 05.03.02 12.03.02

SELECT nr,von_leer, bis_leer
FROM (SELECT nr

            ,lag(bis) OVER (PARTITION BY nr ORDER BY von) von_leer
            ,von bis_leer
      FROM scott.zimmer
      ORDER BY nr,von)

WHERE nvl(von_leer,to_date('01.01.1900','dd.mm.yyyy'))<>bis_leer UNION ALL
SELECT nr,von_leer, to_DATE(NULL) bis_leer FROM (SELECT nr,bis,max(bis) OVER (PARTITION BY nr ) von_leer

      FROM scott.zimmer
      ORDER BY nr,von)

WHERE bis=von_leer
ORDER BY nr
;

       NR VON_LEER BIS_LEER
        1          02.03.02
        1 20.03.02 25.03.02
        1 28.03.02 10.04.02
        1 15.04.02
        2          01.03.02
        2 12.03.02

best regards

Ralf Received on Wed Feb 12 2003 - 08:50:44 CET