Home » SQL & PL/SQL » SQL & PL/SQL » date counting select (oracle 8.5)
date counting select [message #354678] Mon, 20 October 2008 15:03 Go to next message
tomukaz
Messages: 16
Registered: December 2007
Junior Member
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?



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:
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)






Re: date counting select [message #354693 is a reply to message #354678] Mon, 20 October 2008 20:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This problem sounds like an excellent opportunity for you to learn about Oracle Analytics. More specifically you should look at LEAD() and LAG().

You can use Lead to get the next row based on one of your dates. then you can do math across the rows (first_visit.date_left-second_visit.date_arrived)... to get days between visits.

Kevin Meade's Blog

A Simple Example of Oracle Analytics: Running Totals

Analytic functions by Example

Analytic Functions in Oracle 8i and 9i

Read these, then play with it, then show use what you got. Kevin
Re: date counting select [message #354714 is a reply to message #354678] Mon, 20 October 2008 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For all these questions you must post a test case: create table and insert statements along with the result you want with these data.

And also post your Oracle version with 4 decimals, the actual one, 8.5 never existed.

Unfortunatly if you have 8.0.5, analytical functions don't exist.

Regards
Michel
Re: date counting select [message #354735 is a reply to message #354678] Tue, 21 October 2008 01:12 Go to previous messageGo to next message
tomukaz
Messages: 16
Registered: December 2007
Junior Member
Sorry, my version: 8.0.5.0.0 Sad
Re: date counting select [message #354751 is a reply to message #354678] Tue, 21 October 2008 02:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ohoh . .

It a bit outdated version . Following could have solved your issue in Oracle new version.

WITH RECSET AS (
SELECT gal_id,
       gal_asmn_id,
       day_arrived ,day_left,
       LEAD( day_arrived ,1,day_left ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) ld1,
       LAG( day_left ,1,day_arrived ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) lg1,
       LEAD( day_arrived ,1 ,day_left) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) - day_left prd1,
       day_arrived -LAG( day_left ,1,day_arrived ) OVER ( PARTITION BY GAL_ASMN_ID ORDER BY GAL_ASMN_ID , DAY_ARRIVED ) prd2
FROM LOG_JOURNAL ) 
SELECT gal_id,
       gal_asmn_id,
       day_arrived ,day_left
FROM   RECSET r1
WHERE day_arrived >= TO_DATE('2007.01.01', 'YYYY.MM.DD') 
AND   day_left < TO_DATE('2008.08.31', 'YYYY.MM.DD')  
AND   ( prd1 between 1 and 90 or prd2 between 1 and 90 )
AND   2>= ( Select COUNT(*)
            FROM   RECSET r2
            WHERE  r2.GAL_ID  = r1.GAL_ID 
            AND    r2.day_arrived >= TO_DATE('2007.01.01', 'YYYY.MM.DD') 
            AND    r2.day_left < TO_DATE('2008.08.31', 'YYYY.MM.DD')  )


Anybody is having any idea on how to convert the above code to equivalent SQL in Oracle 8.0.5 ?

I am sure it will be slower even if it is Done .

Thumbs Up
Rajuvan.

[Updated on: Tue, 21 October 2008 02:23]

Report message to a moderator

Re: date counting select [message #354755 is a reply to message #354751] Tue, 21 October 2008 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did you post a solution that does not work for OP and which has already been posted by Kevin?

Seems your hand titillates you again.

By the way, keep your lines in 80 characters.

Regards
Michel
Re: date counting select [message #354761 is a reply to message #354678] Tue, 21 October 2008 02:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Michel ,

Please read the Post fully from next time .
The Posted Query is of no help for the OP ( even he knows) .

Anybody is having any idea on how to convert the above code to equivalent SQL in Oracle 8.0.5 ? 


Just wanted to invite the Alternative of equivalent SQL in Oracle 8.0.5 version .

Anyway , Good . you are trying to learn Good English Cool

Thumbs Up
Rajuvan.


Re: date counting select [message #354764 is a reply to message #354761] Tue, 21 October 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Anybody is having any idea on how to convert the above code to equivalent SQL in Oracle 8.0.5 ?

This is completly meaningless (and so ignored otherwise the answer would be harsher) as there is NO equivalent because there is NO analytical function.
You could find this "equivalent" by yourself instead of posting an useless answer (maybe not useless for you if it helps to release an internal tension). Rolling Eyes

Regards
Michel
Re: date counting select [message #354765 is a reply to message #354678] Tue, 21 October 2008 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This wil show you the list of person_ids who had two or more visits to the hotel between the dates specified, and where each visit was within 90 days of another visit:
drop table test_005;

create table test_005 (person_id  number, date_arr  date, date_left date);

insert into test_005 values (1, sysdate-90,sysdate-80);
insert into test_005 values (1, sysdate-290,sysdate-280);
insert into test_005 values (1, sysdate-260,sysdate-255);
insert into test_005 values (1, sysdate-250,sysdate-249);
insert into test_005 values (2, sysdate-90,sysdate-80);
insert into test_005 values (2, sysdate-390,sysdate-380);
insert into test_005 values (2, sysdate-370,sysdate-360);
insert into test_005 values (3, sysdate-90,sysdate-80);
insert into test_005 values (3, sysdate-390,sysdate-380);


select distinct a.person_id
from   test_005 a
      ,test_005 b
where  a.date_arr  >= to_date('20070101','yyyymmdd')
and    a.date_left <= to_date('20080831','yyyymmdd')
and    b.date_arr  >= to_date('20070101','yyyymmdd')
and    b.date_left <= to_date('20080831','yyyymmdd')
and    a.person_id = b.person_id
and    a.date_arr < b.date_arr
and    (b.date_arr - a.date_left) <= 90;

Re: date counting select [message #354783 is a reply to message #354678] Tue, 21 October 2008 04:14 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Wow !!! Nice solution JRowBottom ..

A union of a.* and b.* will give the OP the desired output (exactly)

Thumbs Up
Rajuvan.
Re: date counting select [message #354795 is a reply to message #354783] Tue, 21 October 2008 05:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only output he asked for was a list of person_ids, and that's exactly what I've provided.
Re: date counting select [message #354805 is a reply to message #354678] Tue, 21 October 2008 06:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes..

But What he is trying to in his query is to get the list of person and such details of visits.


Quote:
SELECT personal_code||';'||day_arrived||';'||day_left
FROM log_journal, persons
WHERE



Thumbs Up
Rajuvan.
Re: date counting select [message #354822 is a reply to message #354678] Tue, 21 October 2008 07:44 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
tomukaz wrote on Mon, 20 October 2008 16:03


Tables:
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)




Do you really have column names in lower case? If so, you must refer to them in double quotes every time you reference them. Not a good design.
Previous Topic: Advanced partitioning question
Next Topic: Update statement
Goto Forum:
  


Current Time: Fri Dec 02 21:05:52 CST 2016

Total time taken to generate the page: 0.07771 seconds