Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: date counting help

    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:
    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:
    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. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Log_Journal?

    This will get you going for the first part, now you just need to add the 90 days between visits:
    Code:
    Select Gal_Asmn_Id||';'||Day_Arrived||';'||Day_Left
      From Log_Journal L
     Where 1 < (
       Select Count(*) From Log_Journal J
        Where J.Gal_Asmn_Id  = L.Gal_Asmn_Id
          And J.Day_Arrived >= To_Date('2007.01.01', 'YYYY.MM.DD')
          And J.Day_Left    <  To_Date('2007.08.31', 'YYYY.MM.DD');
    Last edited by LKBrwn_DBA; 10-21-08 at 10:00.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Just try this.

    Or you can just try this:
    Code:
    SELECT gal_asmn_id || ';' || day_arrived || ';' || day_left
      FROM log_journal l
     WHERE 1 <
              (SELECT COUNT (*)
                 FROM log_journal j
                WHERE j.gal_asmn_id = l.gal_asmn_id
                  AND j.day_arrived >= TO_DATE ('2007.01.01', 'YYYY.MM.DD')
                  AND j.day_left < TO_DATE ('2007.08.31', 'YYYY.MM.DD'))
       AND 90 <=
              (SELECT ABS (d.day_left -  LEAD (d.day_arrived, 1) OVER (ORDER BY d.day_arrived))
                 FROM log_journal d
                WHERE j.gal_asmn_id = l.gal_asmn_id)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Giving a clue for homework is fine, personally I would not give the final solution.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Ooops

    Quote Originally Posted by beilstwh
    Giving a clue for homework is fine, personally I would not give the final solution.
    Ooops, you are correct...Should have known better.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •