Home » SQL & PL/SQL » SQL & PL/SQL » date counting select (oracle 8.5)
date counting select [message #354678] |
Mon, 20 October 2008 15:03  |
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 #354751 is a reply to message #354678] |
Tue, 21 October 2008 02:22   |
 |
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 .

Rajuvan.
[Updated on: Tue, 21 October 2008 02:23] Report message to a moderator
|
|
|
|
|
|
Re: date counting select [message #354765 is a reply to message #354678] |
Tue, 21 October 2008 03:07   |
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 #354822 is a reply to message #354678] |
Tue, 21 October 2008 07:44  |
joy_division
Messages: 4963 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.
|
|
|
Goto Forum:
Current Time: Fri Feb 07 20:58:32 CST 2025
|