×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Not In Statement in Crystal

Not In Statement in Crystal

Not In Statement in Crystal

(OP)
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;



 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close