pears.NetClientDatedPreviousEmployeesFiltered

CREATE PROCEDURE "pears"."NetClientDatedPreviousEmployeesFiltered"( IN "pWebUserID" CHAR(20),IN "pFromDate" CHAR(20),IN "pToDate" CHAR(20) ) 
RESULT( "Name" CHAR(100),"PersonID" CHAR(20),"KeyName" CHAR(100),"FirstDate" DATE,"LatestDate" DATE ) 
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  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"("pFromDate"),"dateadd"("month",-6,CURRENT DATE));
  SET "pEnd" = "isnull"("iqxnetstringtodate"("pToDate"),"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");
  -- Get the Peoples details
  SELECT "person"."name","person"."personid","person"."keyname","pes"."FirstDate","pes"."LatestDate"
    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"."NetClientDatedPreviousEmployeesFiltered" IS 
{CREATE PROCEDURE pears."NetClientDatedPreviousEmployeesFiltered"( IN "pWebUserID" CHAR(20),IN "pFromDate" CHAR(20),IN "pToDate" CHAR(20) ) 
RESULT( "Name" CHAR(100),"PersonID" CHAR(20),"KeyName" CHAR(100),"FirstDate" DATE,"LatestDate" DATE ) 
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  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"("pFromDate"),"dateadd"("month",-6,CURRENT DATE));
  SET "pEnd" = "isnull"("iqxnetstringtodate"("pToDate"),"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");
  -- Get the Peoples details
  SELECT "person"."name","person"."personid","person"."keyname","pes"."FirstDate","pes"."LatestDate"
    FROM "person" JOIN "PES" ON "Pes"."PersonID" = "Person"."PersonID"
    WHERE "pes"."latestdate" BETWEEN "pStart" AND "pEnd"
    ORDER BY "person"."keyname" ASC
END
}