pears.NetReportPersonHEExpiryDates [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetReportPersonHEExpiryDates ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetReportPersonHEExpiryDates"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__Hr" char(50),"Name" char(100),"TJ" char(15),"TN" char(15),"TI" char(15),"TK" char(15),"TO__" char(15),"TG" char(15),"TF" char(15),"TC" char(15),"TH" char(15),"TM" char(15),"TB" char(15),"TA" char(15),"TD" char(15),"TP" char(15) ) 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "rTitle" char(50);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  declare local temporary table "Pes"(
    "PersonID" char(20) null,
    "FirstDate" date null,
    "LatestDate" date null,
    ) not transactional;insert into "Cos"( "CompanyID" ) 
    select distinct "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  set "pStart" = "isnull"("iqxnetstringtodate"("From__D"),"dateadd"("month",-6,current date));
  set "pEnd" = "isnull"("iqxnetstringtodate"("To__D"),"dateadd"("month",1,current date));
  -- Get all the People who have worked in the period for those Companies
  insert into "Pes"( "PersonID" ) 
    select distinct "e"."personid"
      from "employment" as "e" key join "placement"
        ,"employment" as "e" join "Cos" on "e"."CompanyID" = "Cos"."CompanyID"
      where not "e"."personid" = any(select "personid" from "employment" where "companyid" = "e"."companyid" and "noreemploy" = 1)
      and "e"."startdate" >= "IQXNetCompanyStartDate"("e"."companyid");
  -- Add the dates
  update "pes" set "firstdate" = (select "min"("e"."startdate") from "employment" as "e" join "cos" on "e"."companyid" = "cos"."companyid" where "personid" = "pes"."personid" and "e"."startdate" is not null);
  update "pes" set "latestdate" = (select "max"("isnull"("e"."leavedate",current date)) from "employment" as "e" join "cos" on "e"."companyid" = "cos"."companyid" where "personid" = "pes"."personid");
  set "rTitle" = "dateformat"("From__D",'dd/mm/yyyy')+' - '+"dateformat"("To__D",'dd/mm/yyyy');
  -- Get the Peoples details
  select "rTitle","person"."name",
    "dateformat"("GetQuestDate"('AHE','TJ',"Person"."Personid"),'dd/mm/yyyy') as "QdateTJ",
    "dateformat"("GetQuestDate"('AHE','TN',"Person"."Personid"),'dd/mm/yyyy') as "QdateTN",
    "dateformat"("GetQuestDate"('AHE','TI',"Person"."Personid"),'dd/mm/yyyy') as "QdateTI",
    "dateformat"("GetQuestDate"('AHE','TK',"Person"."Personid"),'dd/mm/yyyy') as "QdateTK",
    "dateformat"("GetQuestDate"('AHE','TO',"Person"."Personid"),'dd/mm/yyyy') as "QdateTO",
    "dateformat"("GetQuestDate"('AHE','TG',"Person"."Personid"),'dd/mm/yyyy') as "QdateTG",
    "dateformat"("GetQuestDate"('AHE','TF',"Person"."Personid"),'dd/mm/yyyy') as "QdateTF",
    "dateformat"("GetQuestDate"('AHE','TC',"Person"."Personid"),'dd/mm/yyyy') as "QdateTC",
    "dateformat"("GetQuestDate"('AHE','TH',"Person"."Personid"),'dd/mm/yyyy') as "QdateTH",
    "dateformat"("GetQuestDate"('AHE','TL',"Person"."Personid"),'dd/mm/yyyy') as "QdateTL",
    "dateformat"("GetQuestDate"('AHE','TM',"Person"."Personid"),'dd/mm/yyyy') as "QdateTM",
    "dateformat"("GetQuestDate"('AHE','TB',"Person"."Personid"),'dd/mm/yyyy') as "QdateTB",
    "dateformat"("GetQuestDate"('AHE','TA',"Person"."Personid"),'dd/mm/yyyy') as "QdateTA",
    "dateformat"("GetQuestDate"('AHE','TD',"Person"."Personid"),'dd/mm/yyyy') as "QdateTD",
    "dateformat"("GetQuestDate"('AHE','TA',"Person"."Personid"),'dd/mm/yyyy') as "QdateTP",
    "dateformat"("GetQuestDate"('AHE','TD',"Person"."Personid"),'dd/mm/yyyy') as "QdateT"
    from "person" join "PES" on "Pes"."PersonID" = "Person"."PersonID"
    where "pes"."latestdate" between "pStart" and "pEnd"
    order by "person"."keyname" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportPersonHEExpiryDates" IS 
{create procedure pears."NetReportPersonHEExpiryDates"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__Hr" char(50),"Name" char(100),"TJ" char(15),"TN" char(15),"TI" char(15),"TK" char(15),"TO__" char(15),"TG" char(15),"TF" char(15),"TC" char(15),"TH" char(15),"TM" char(15),"TB" char(15),"TA" char(15),"TD" char(15),"TP" char(15) ) 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "rTitle" char(50);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  declare local temporary table "Pes"(
    "PersonID" char(20) null,
    "FirstDate" date null,
    "LatestDate" date null,
    ) not transactional;insert into "Cos"( "CompanyID" ) 
    select distinct "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  set "pStart" = "isnull"("iqxnetstringtodate"("From__D"),"dateadd"("month",-6,current date));
  set "pEnd" = "isnull"("iqxnetstringtodate"("To__D"),"dateadd"("month",1,current date));
  -- Get all the People who have worked in the period for those Companies
  insert into "Pes"( "PersonID" ) 
    select distinct "e"."personid"
      from "employment" as "e" key join "placement"
        ,"employment" as "e" join "Cos" on "e"."CompanyID" = "Cos"."CompanyID"
      where not "e"."personid" = any(select "personid" from "employment" where "companyid" = "e"."companyid" and "noreemploy" = 1)
      and "e"."startdate" >= "IQXNetCompanyStartDate"("e"."companyid");
  -- Add the dates
  update "pes" set "firstdate" = (select "min"("e"."startdate") from "employment" as "e" join "cos" on "e"."companyid" = "cos"."companyid" where "personid" = "pes"."personid" and "e"."startdate" is not null);
  update "pes" set "latestdate" = (select "max"("isnull"("e"."leavedate",current date)) from "employment" as "e" join "cos" on "e"."companyid" = "cos"."companyid" where "personid" = "pes"."personid");
  set "rTitle" = "dateformat"("From__D",'dd/mm/yyyy')+' - '+"dateformat"("To__D",'dd/mm/yyyy');
  -- Get the Peoples details
  select "rTitle","person"."name",
    "dateformat"("GetQuestDate"('AHE','TJ',"Person"."Personid"),'dd/mm/yyyy') as "QdateTJ",
    "dateformat"("GetQuestDate"('AHE','TN',"Person"."Personid"),'dd/mm/yyyy') as "QdateTN",
    "dateformat"("GetQuestDate"('AHE','TI',"Person"."Personid"),'dd/mm/yyyy') as "QdateTI",
    "dateformat"("GetQuestDate"('AHE','TK',"Person"."Personid"),'dd/mm/yyyy') as "QdateTK",
    "dateformat"("GetQuestDate"('AHE','TO',"Person"."Personid"),'dd/mm/yyyy') as "QdateTO",
    "dateformat"("GetQuestDate"('AHE','TG',"Person"."Personid"),'dd/mm/yyyy') as "QdateTG",
    "dateformat"("GetQuestDate"('AHE','TF',"Person"."Personid"),'dd/mm/yyyy') as "QdateTF",
    "dateformat"("GetQuestDate"('AHE','TC',"Person"."Personid"),'dd/mm/yyyy') as "QdateTC",
    "dateformat"("GetQuestDate"('AHE','TH',"Person"."Personid"),'dd/mm/yyyy') as "QdateTH",
    "dateformat"("GetQuestDate"('AHE','TL',"Person"."Personid"),'dd/mm/yyyy') as "QdateTL",
    "dateformat"("GetQuestDate"('AHE','TM',"Person"."Personid"),'dd/mm/yyyy') as "QdateTM",
    "dateformat"("GetQuestDate"('AHE','TB',"Person"."Personid"),'dd/mm/yyyy') as "QdateTB",
    "dateformat"("GetQuestDate"('AHE','TA',"Person"."Personid"),'dd/mm/yyyy') as "QdateTA",
    "dateformat"("GetQuestDate"('AHE','TD',"Person"."Personid"),'dd/mm/yyyy') as "QdateTD",
    "dateformat"("GetQuestDate"('AHE','TA',"Person"."Personid"),'dd/mm/yyyy') as "QdateTP",
    "dateformat"("GetQuestDate"('AHE','TD',"Person"."Personid"),'dd/mm/yyyy') as "QdateT"
    from "person" join "PES" on "Pes"."PersonID" = "Person"."PersonID"
    where "pes"."latestdate" between "pStart" and "pEnd"
    order by "person"."keyname" asc
end
}
</code>