Howdy:

I have a query:

[snip code]
select
m.contract,
m.mbr_num,
m.zip,
m.bu,
m.mbr_sex,
m.product,
p.c_pgm as pgm,
p.c_prov_num as prov_num,
greatest(m.effdt,p.d_eff_dt) as effdt,
least(m.enddt, p.d_end_dt) as enddt
from x_rmms_b m,
pre_cn2pprov p
where
trim(m.contract)=trim(p.c_contract_num)
and trim(m.mbr_num)=trim(p.c_mbr_num)
and trim(m.bu)=trim(p.c_bu)
and p.d_eff_dt < p.d_end_dt
and not (m.effdt > p.d_end_dt
or
m.enddt < p.d_eff_dt );
[/snip code]

In this, I get the clients with the above info
and the greatest and least values of dates
(d_eff_dt and d_end_dt) between the two tables.
each one represents a starting point
and end point of a span of time for each person.

Now, I have been asked to do the following:

[snip from email]
create SPANS table without dropping members when they
don't have a PRE_CN2PPROV span that corresponds to their
other active spans.

Maybe you can do an outer join and insert a dummy PROV_NUM
and RISKGRP? The dummy provider will have to be
distinguishable from the already existing dummy STAR PCP
numbers, P99**. How about the value 'NOSPAN' in both fields?
[/snip from email]

How I can get all dates from PRE_CN2PPROV, assign them
XYZ values while using left joins?

This seems to me like something I would need to do with
CASE - that's the route I'm leaning, but I'm getting static
from the upper elephants about it ...

Thanks!

-X
Oracle LazyDBA home page