Have you tried using 'Select Distinct '???
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Behalf Of Tracey Maule
Sent: Thursday, 6 March 2003 12:11 p.m.
To: Multiple recipients of list delphi
Subject: Re: [DUG]: arrggghh more sql
humble apologies
----- Original Message -----
From: "Chris Milham" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Thursday, March 06, 2003 11:40 AM
Subject: Re: [DUG]: arrggghh more sql
> Hi Tracey,
>
> Not an answer (yet) but a request....
>
> Please don't post images like this. Put it on a website somewhere and
> post a link.
> Also, the image you sent is very hard to read as it's small and
> compression has munched it.
>
> Ta,
>
> Chris
>
> Tracey Maule wrote:
>
> > Its all getting a bit much for me... help
> >
> > I have a Db...
> > can this usergroup pass images? I hope so, this will be easier:
> >
> >
> > I have a query which retreives all records of skills that are due to
> > expire within 28 days:
> >
> > SQL.Add('Select sa.skillattemptid, s.name sname,
> > sa.validto, sa.result, p.name pname, p.companyid, s.skillid,
p.personid');
> > SQL.Add('from skillattempt sa');
> > SQL.Add('join skill s on s.skillid = sa.skillid');
> > SQL.Add('join person p on p.personid = sa.personid');
> > SQL.Add('where sa.result = ''P''');
> > SQL.Add('and sa.validto < :28Days');
> > SQL.Add('order by companyid, skillid');
> > ParamByName('28Days').AsDate := (Now + 28);
> >
> > From this i build an email message (the following skills need to be
> > renewed.. blah blah)
> > SKILLNAME
> > PERSON_WHO_NEEDS_TO_RENEW
> > OTHER_PERSON_WHO_NEEDS_TO_RENEW
> >
> > OTHERSKILLNAME
> > PERSON_WHO_NEEDS_TO_RENEW
> > OTHER_PERSON_WHO_NEEDS_TO_RENEW
> >
> > I also need to list under each skill, the people who need to have
> > acheived that skill within 28 days, but who is not already listed in
> > the first query
> >
> > heres what ive been trying
> >
> > SQL.Add(' select pe.commencedate, sn.skillid, s.name sname,
> > sn.timeperiodid, tp.weeks, pe.personid, p.name pname');
> > SQL.Add('from personemployment pe');
> > SQL.Add('join skillneeded sn on sn.employmentid =
pe.employmentid');
> > SQL.Add('join timeperiod tp on tp.timeperiodid = sn.timeperiodid');
> > SQL.Add('join skill s on s.skillid = sn.skillid');
> > SQL.Add('join person p on p.personid = pe.personid');
> > SQL.Add('where');
> > SQL.Add('sn.skillid not in ');
> > SQL.Add(' (select skillid from skillattempt where (personid =
> > :personID and skillid = sn.skillid'); //supposed to not
> > retrieve the passed records still current,
> >
> >
> > //not doing its job
> > SQL.Add(' and result = ''P'' and (validto is null or
> > validto > :today )))');
> > SQL.Add('and (pe.commencedate + (tp.weeks * 7) < :28Days)');
> > //here i have to calculate jobstartdate +
> > timeperiod required by = daterequired
> > SQL.Add('and skillid = :skillid');
> > SQL.Add('order by s.name, weeks');
> > ParamByName('28Days').AsDate := (Now + 28);
> > ParamByName('today').AsDate := Now;
> >
> > But this seems to bring back the same people, or at least records that
> > are already returned, and im getting duplicated stuff.
> > My newbie status is giving me grief here. I know its really hard to
> > do this stuff when u dont know the Db.. but without your Guru advise ,
> > I may be here till xmas pulling out my hair.
> >
> > so far with these queries, i am producing this: (names against margin
> > are from 2nd query)
> > This email has been generated by etime to inform you of Training
> > Requirements
> > which are due in 28 days
> >
> > HAZARD IDENTIFICATION
> > Tim DeYoung
> > Hata Gordine
> > Hata Gordine
> > Hata Gordine
> >
> > BASIC FIRST AID
> > Chris Smith
> > Hata Gordine
> >
> > TYPING 101
> > Hata Gordine
> > Hata Gordine
> >
> > and here is the current data as per my (working) report
> >
> >
> >
> > Summary Training Report
> >
> >
> > Name
> >
> >
> > Job
> >
> >
> > Skill Required
> >
> >
> > Due
> >
> > *Tim DeYoung*
> >
> > FORKLIFT DRIVER FORKLIFT LICENSE Not Current 1/03/2003
> >
> >
> > BASIC FIRST AID Current 5/03/2005
> >
> > SUPERVISOR BASIC FIRST AID Current 5/03/2005
> >
> >
> > HAZARD IDENTIFICATION Current 1/04/2003
> > *Hata Gordine*
> >
> > COURIER CLASS A LICENSE Current 4/03/2006
> >
> >
> > BASIC FIRST AID Current 10/04/2003
> >
> > ALL ROUNDER TYPING 101 Current 12/03/2003
> >
> >
> > CLASS A LICENSE Current 4/03/2006
> >
> >
> > BASIC FIRST AID Current 10/04/2003
> >
> >
> > BASIC FIRST AID Current 10/04/2003
> >
> >
> > BASIC FIRST AID Current 10/04/2003
> >
> >
> > HAZARD IDENTIFICATION Not Current 18/03/2003
> >
> >
> > HAZARD IDENTIFICATION Not Current 18/03/2003
> >
> >
> > HAZARD IDENTIFICATION Not Current 18/03/2003
> >
> >
> > HAZARD IDENTIFICATION Not Current 18/03/2003
> >
> >
> > FORKLIFT LICENSE Not Current 13/05/2003
> >
> >
> > BASIC FIRST AID Current 10/04/2003
> > *Chris Smith*
> >
> > ADMINISTRATOR TYPING 101 Not Current 1/02/2003
> >
> >
> > BASIC FIRST AID Current 2/04/2003
> >
> >
> > HELP HELP HELP ME PLEASE??
> >
> > Tracey
> > Software Developer / Web Master
> > Logis
> > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> > (025) 213-1065
>
>
> --
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Chris Milham BD BSc
> Software Development and I.T. Services
> Email: [EMAIL PROTECTED]
> Ph. 0212580380 (Intl. +64 21 2580380)
> Post: P.O. Box 101627, N.S.M.C., Auckland, New Zealand
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
>
>
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
> Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
