pears.NetReportPersonHEExpiryDates

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
}