SQL Query

[jsin14]

Hi,

I am creating a report using Oracle reports, I am just stuck in middle of nowhere. I think you can help me. I have two problem.

(1)

I want to select data P_ID from a table with the last time he/she got the concession like Max(effective_start_date). The client is entitled for concession after every two years from its effective start date. So i need to create a formula column adding 24 months in it.

my SQL is like this:

select cg.person_id, max(case when cg.conc_subtype_id = 1 then (cg.effective_start_date) else NULL end ) "Last Lense conc", add_months(max(cg.effective_start_date),24) "Next Lense"
from conc_given cg
group by cg.person_id


But some time the client hasn't got conc. The formula column is adding 24 months the last time he got the conc. (it could be 10 years ago). What I want is if "Last Lense Conc" is NULL then "Next Lense" column value should also be NULL.


(2). The client also have some dependents and they also got the concession along with the client.

I can put the dependents in the query related to clients but I want to show the same results for dependents as well in the differnet row.

For example:
person_id - relative_id - Last Lense Conc - Next Lense
111 - - 01/01/2002 - 01/01/2004
- 1232 - 05/08/2000 - 05/08/2004
- - -
Means 1232 is a relation with 111 but it shows the results in different row.

Thanks

[tamilselvan]

SQL> select * from t1 ;

ID EFF_DATE
---------- ---------
100 05-JUL-03
100 08-AUG-03
100 10-SEP-03
200
300 08-MAY-01

SQL>
1 select id, max(eff_date) last_eff_date,
2 decode(max(eff_date), null,null , add_months(max(eff_date),24))
3 from t1
4* group by id;

ID LAST_EFF_ DECODE(MA
---------- --------- ---------
100 10-SEP-03 10-SEP-05
200
300 08-MAY-01 08-MAY-03

Is this you want?

Tamil