u are kidding right? ----- Original Message ----- From: "Traci Sumpter" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Thursday, March 06, 2003 12:17 PM Subject: RE: [DUG]: arrggghh more sql
> 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/ > --------------------------------------------------------------------------- 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/
