Post Author: ru4real
CA Forum: General
I have this statement provided to extract via sql but i cannot emulate this in crystal. can anybody help? here is the run down. essentially iam trying to list records in one table auanmast which is not present in the other table auanregs based on reg dte (from) and reg til (to) if this record is not in the auanreg table then list the ani_num from the auanmast. (however there is not a null field present in the auanreg table), so a left outerjoin won't work.
ani_num reg_date reg_til
5555 01/07/08 30/06/09
5555 01/07/05 30/07/06
so the record missing in auanreg is reg_date 01/07/07 and reg_til = 30/06/08 and iam trying to get the query to list all ani_num from auanmast that have no date assigned with reg_date & reg_til date the link being ani_num to
select ani_num from auanmast a where ani_num not in (select ani_num from auanregs where reg_dte = '01-Jul-07' and reg_til = '30-Jun-08' and ani_num = a.ani_num) and ani_dth is null and ani_dep is null and ani_num not in (select ani_num from auantags where ani_num = a.ani_num and tag_set != '2007/08') order by ani_num /
Hope someone is upto the challenge for this one....cheers