Hi,
I am trying to develop a report based on an SQL script, without actually using SQL commands. I've created all of the links, and am trying to tell Crystal to not include clients who have registration dates before Apr 1, 2010 - e.g. if they registered in june 01, 2008 AND in Apr. 27, 2010 I don't want to include them.
The SQL for this reads:
and pa.party_id not in
(select pp2.party_id
from asa.training_agreement ta2,
asa.program_participation pp2
where ta2.registration_date < a.registration_date -- no previous registration date
Would anyone know the best way to translate this into Crystal? I have included the full script below. Thank you very much, and please let me know if there's any other info I can provide.
select ts.toss_code,
count(distinct(pa.party_id)) Reg_Count
from asa.training_agreement a,
asa.curr_training_agreement_status d,
asa.program_participation pa,
asa.apc_program pr,
asa.trade_occupation_skill_set ts,
asa.curr_staff_location_status sl,
--asa.program p,
asa.party c,
asa.staff_location_status_hist Sloc
where a.registration_date between
to_date('01/04/2010', 'dd/mm/yyyy') and to_date('31/03/2011', 'dd/mm/yyyy')
and pa.special_program_id is NULL -- No OYAP Apprentices
and pa.party_id not in
(select pp2.party_id
from asa.training_agreement ta2,
asa.program_participation pp2
where ta2.registration_date < a.registration_date -- no previous registration date
and pp2.program_participation_id = ta2.program_participation_id)
and d.code_value <> 'PEND'
and d.code_value <> 'DEC'
and sl.code_value = 'ACT'
and sl.status_id in (select min(sf.status_id)
from asa.curr_staff_location_status sf
where sl.user_id = sf.user_id
and sf.code_value = 'ACT'
group by sf.user_id)
and a.program_participation_id = d.program_participation_id
and a.program_participation_id = pa.program_participation_id
and pa.program_id = pr.program_id
and pr.toss_code = ts.toss_code
and a.current_tc_id = sl.user_id
and sl.party_id = c.party_id
and sloc.party_id = sl.party_id
group by ts.toss_code --,ts.sector;
I am trying to develop a report based on an SQL script, without actually using SQL commands. I've created all of the links, and am trying to tell Crystal to not include clients who have registration dates before Apr 1, 2010 - e.g. if they registered in june 01, 2008 AND in Apr. 27, 2010 I don't want to include them.
The SQL for this reads:
and pa.party_id not in
(select pp2.party_id
from asa.training_agreement ta2,
asa.program_participation pp2
where ta2.registration_date < a.registration_date -- no previous registration date
Would anyone know the best way to translate this into Crystal? I have included the full script below. Thank you very much, and please let me know if there's any other info I can provide.
select ts.toss_code,
count(distinct(pa.party_id)) Reg_Count
from asa.training_agreement a,
asa.curr_training_agreement_status d,
asa.program_participation pa,
asa.apc_program pr,
asa.trade_occupation_skill_set ts,
asa.curr_staff_location_status sl,
--asa.program p,
asa.party c,
asa.staff_location_status_hist Sloc
where a.registration_date between
to_date('01/04/2010', 'dd/mm/yyyy') and to_date('31/03/2011', 'dd/mm/yyyy')
and pa.special_program_id is NULL -- No OYAP Apprentices
and pa.party_id not in
(select pp2.party_id
from asa.training_agreement ta2,
asa.program_participation pp2
where ta2.registration_date < a.registration_date -- no previous registration date
and pp2.program_participation_id = ta2.program_participation_id)
and d.code_value <> 'PEND'
and d.code_value <> 'DEC'
and sl.code_value = 'ACT'
and sl.status_id in (select min(sf.status_id)
from asa.curr_staff_location_status sf
where sl.user_id = sf.user_id
and sf.code_value = 'ACT'
group by sf.user_id)
and a.program_participation_id = d.program_participation_id
and a.program_participation_id = pa.program_participation_id
and pa.program_id = pr.program_id
and pr.toss_code = ts.toss_code
and a.current_tc_id = sl.user_id
and sl.party_id = c.party_id
and sloc.party_id = sl.party_id
group by ts.toss_code --,ts.sector;