|
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
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
<<eTrain.jpg>>
