1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

date counting help

Discussion in 'SQL PL/SQL' started by tomukaz, Oct 20, 2008.

  1. tomukaz

    tomukaz Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I need to find a list list of persons, who had had a visit at hotel from 2007.01.01 until 2008.08.31
    One visit is when person arrived and left.
    Main job: to find persons who had TWO or more visits AND the date between visits is 90 less days.

    day_arrived - day_left -- first visit
    .... <= 90 days pass ...
    day_arrived - day_left -- second visit
    .... <= 90 days pass ...
    day_arrived - day_left -- trird visit
    .... <= 90 days pass ...

    ... and so on. Did something, but I think is wrong. Any help from more experienced?



    Code (Text):
    SELECT personal_code||';'||day_arrived||';'||day_left
    FROM log_journal, persons
    WHERE
      GAL_ASMN_ID = ASM_ID AND
      day_arrived >= TO_DATE('2007.01.01', 'YYYY.MM.DD') AND
      day_left < TO_DATE('2008.08.31', 'YYYY.MM.DD') AND
      (SELECT count(*) FROM log_journal WHERE GAL_ASMN_ID = ASM_ID) >=2 AND
      (SELECT count(*) FROM log_journal a, log_journal b
      WHERE ABS(a.day_left - b.day_arrived) <= 90 AND
      a.GAL_ASMN_ID = ASM_ID AND b.GAL_ASMN_ID = ASM_ID AND a.GAL_ID != b.GAL_ID) > 0
    GROUP BY
    personal_code, day_arrived, day_left
    ORDER BY day_arrived;
    Tables:
    Code (Text):
    SQL> desc log_journal;
    Name                            Null?    Type
    ------------------------------- -------- ----
    GAL_ID                          NOT NULL NUMBER(20)
    day_arrived                      NOT NULL DATE
    GAL_ASMN_ID                              NUMBER(20)
    day_left                             DATE

    SQL> desc persons;
    Name                            Null?    Type
    ------------------------------- -------- ----
    ASM_ID                          NOT NULL NUMBER(20)
    personal_code                            VARCHAR2(11)
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Please dont forget to add insert script to generate the example from next time. Anyway I managed to produce some data .

    Code (Text):

    SQL> select * from  LOG_JOURNAL ORDER BY 3,2;

    GAL_ID DAY_ARRIV GAL_ASMN_ID DAY_LEFT
    ------ --------- ----------- ---------
       101 01-JAN-08          10 01-MAR-08
       103 15-MAR-08          10 15-APR-08
       105 25-JUL-08          10 30-JUL-08
       100 15-MAR-07          11 15-DEC-07
       102 01-JAN-08          11 01-MAR-08
       104 15-JUN-08          11 01-JUL-08

    6 rows selected.

    SQL>
     
    We can generate the result simply by using correlated subquery. We need to use LAG and LEAD aggregate functions. Please refer the link for more details

    We need to to genrate the following code to get a start. which gives the duration between stays.

    Code (Text):

    SQL> SELECT gal_id,
      2         gal_asmn_id,
      3         day_arrived ,day_left,
      4         LEAD( day_arrived ,1,day_left ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) ld1,
      5         LAG( day_left ,1,day_arrived ) OVER ( PARTITION BY GAL_ASMN_ID ORDER  BY GAL_ASMN_ID , DAY_ARRIVED ) lg1,
      6         LEAD( day_arrived ,1 ,day_left) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) - day_left prd1,
      7         day_arrived -LAG( day_left ,1,day_arrived ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) prd2
      8  FROM LOG_JOURNAL
      9  ;

    GAL_ID GAL_ASMN_ID DAY_ARRIV DAY_LEFT  LD1       LG1        PRD1  PRD2
    ------ ----------- --------- --------- --------- --------- ----- -----
       101          10 01-JAN-08 01-MAR-08 15-MAR-08 01-JAN-08    14     0
       103          10 15-MAR-08 15-APR-08 25-JUL-08 01-MAR-08   101    14
       105          10 25-JUL-08 30-JUL-08 30-JUL-08 15-APR-08     0   101
       100          11 15-MAR-07 15-DEC-07 01-JAN-08 15-MAR-07    17     0
       102          11 01-JAN-08 01-MAR-08 15-JUN-08 15-DEC-07   106    17
       104          11 15-JUN-08 01-JUL-08 01-JUL-08 01-MAR-08     0   106

    6 rows selected.

    SQL>
     
    Anyway following code may give you a good start you are looking for.

    Code (Text):

    SQL> WITH RECSET AS (
      2  SELECT gal_id,
      3         gal_asmn_id,
      4         day_arrived ,day_left,
      5         LEAD( day_arrived ,1,day_left ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) ld1,
      6         LAG( day_left ,1,day_arrived ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) lg1,
      7         LEAD( day_arrived ,1 ,day_left) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) - day_left prd1,
      8         day_arrived -LAG( day_left ,1,day_arrived ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) prd2
      9  FROM LOG_JOURNAL )
     10  SELECT gal_id,
     11         gal_asmn_id,
     12         day_arrived ,day_left
     13  FROM   RECSET r1
     14  WHERE day_arrived >= TO_DATE('2007.01.01', 'YYYY.MM.DD')
     15  AND   day_left < TO_DATE('2008.08.31', 'YYYY.MM.DD')
     16  AND   ( prd1 between 1 and 90 or prd2 between 1 and 90 )
     17  AND   2>= ( Select COUNT(*)
     18              FROM   RECSET r2
     19              WHERE  r2.GAL_ID  = r1.GAL_ID
     20              AND    r2.day_arrived >= TO_DATE('2007.01.01', 'YYYY.MM.DD')
     21              AND    r2.day_left < TO_DATE('2008.08.31', 'YYYY.MM.DD')  )
     22  ;

    GAL_ID GAL_ASMN_ID DAY_ARRIV DAY_LEFT
    ------ ----------- --------- ---------
       101          10 01-JAN-08 01-MAR-08
       103          10 15-MAR-08 15-APR-08
       100          11 15-MAR-07 15-DEC-07
       102          11 01-JAN-08 01-MAR-08

    SQL>
     
     
  3. tomukaz

    tomukaz Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    SQL> WITH RECSET AS (
    unknown command beginning "WITH RECSE..." - rest of line ignored.

    Maby because I have orcle 8.0.5.0.0?
    p.s. my expected data result is something like:
    123456789 2007.02.01 2007.03.05
    123456789 2007.05.01 2007.10.12 ; <=90 days between first and this visit AND two visits or more
    777777777 2007.01.18 2007.10.12
    777777777 2007.12.06 2008.01.27
    777777777 2008.03.01 2008.05.01
    666666666 2008.01.01 2008.01.05
    666666666 2008.01.06 2008.01.15
    ... and so on...
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    WITH clause and Analytical functions are not supported in your version . Analytical is introduced in Oracle 8i and WITH clause in 9i. I am not pretty sure how it is possible in Old versions.