Interactive input 1

[marathon42]

I need some help to solve the follwing problem:
I have to sort out hospital inpatients who have been in more than once in a given period, listing those periods.
Here is the syntax that works, BUT how can I solve the problem of having to input the dates (date1 & date2) twice??

select .....
from PATIENT A, OBJEKTE B
Where a.clinik_id = b.obj_id
and a.pid in (SELECT c.pid
FROM Patient c
WHERE c.pid IN
(SELECT pid
FROM patient
where admis_date
between :=date1 and :=date2
and exit_date is not null
GROUP pid HAVING COUNT(pid) > 1)
group by c.pid having count(*) > 1)

and admis_date between :=date1 and :=date2
and exit_date is not null
group by a.p_nr, b.obj_name, a.p_name, a.p_vorname,
a.admis_date, a.exit_date, a.ENTL_DIAGNOSE, a.pid
order by a.pid;

 

[dnoeth]

If you're running a current version of Oracle/DB2/Teradata the new SQL:1999 OLAP-functions may help:

select ...
from
(
select .....
,count(*) over (partition by a.pid rows between unbounded preceding and unbounded following) as cnt
from PATIENT A, OBJEKTE B
Where a.clinik_id = b.obj_id
and admis_date between :=date1 and :=date2
and exit_date is not null
) dt
where cnt > 1
group by a.p_nr, b.obj_name, a.p_name, a.p_vorname,
a.admis_date, a.exit_date, a.ENTL_DIAGNOSE, a.pid
order by a.pid;


Dieter