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/
