pears.NetReportClientStats

CREATE PROCEDURE "pears"."NetReportClientStats"( IN "pWebUserID" CHAR(20),IN "Start_Date__d" CHAR(20) DEFAULT NULL,IN "Weeks__i" CHAR(20) DEFAULT NULL ) 
RESULT( "Serial__H" INTEGER,"Week_Start_Date__DX1Z" DATE,"Week_End_Date__DH" DATE,"New_Vacancies__GRT" INTEGER,"New_Vacancies_Temp__RT" INTEGER,"New_Vacancies_Perm__RT" INTEGER,"New_Placements__GRT" INTEGER,"New_Placements_Temp__RT" INTEGER,"New_Placements_Perm__RT" INTEGER ) 
BEGIN
  DECLARE "pWeeks" INTEGER;
  DECLARE "pStartDate" DATE;
  DECLARE LOCAL TEMPORARY TABLE "Cos"(
    "CompanyID" CHAR(20) NOT NULL,
    PRIMARY KEY("CompanyID"),
    ) NOT transactional;
  SET "pWeeks" = "isnull"("nullif"("trim"("Weeks__I"),''),52);
  SET "pStartDate" = "IQXNetStringToDate"("Start_Date__D");
  IF "pStartDate" IS NULL THEN
    SET "pStartDate" = "dateadd"("week",-52,CURRENT DATE)
  END IF;
  INSERT INTO "Cos"( "CompanyID" ) SELECT DISTINCT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  SELECT("a"."row_num"-1)*255+("b"."row_num"-1) AS "Serial",
    "dateadd"("week","Serial","pStartDate") AS "WeekStartDate",
    "dateadd"("day",6,"WeekStartDate") AS "WeekEndDate",
    (SELECT "count"("vacancyid") FROM "vacancy" KEY JOIN "employment" WHERE "EntryDate" BETWEEN "WeekStartDate" AND "WeekEndDate" AND "employment"."companyid" = any(SELECT "companyid" FROM "Cos")) AS "NewVacanciesAll",
    (SELECT "count"("vacancyid") FROM "vacancy" KEY JOIN "employment" WHERE "EntryDate" BETWEEN "WeekStartDate" AND "WeekEndDate" AND "employment"."companyid" = any(SELECT "companyid" FROM "Cos") AND "vacancy"."temp" = 1) AS "NewVacanciesTemp",
    "NewVacanciesAll"-"NewVacanciesTemp" AS "NewVacanciesPerm",
    (SELECT "count"("placementid") FROM "placement" KEY JOIN "employment" WHERE "PlaceDate" BETWEEN "WeekStartDate" AND "WeekEndDate" AND "employment"."companyid" = any(SELECT "companyid" FROM "Cos")) AS "NewPlacementsAll",
    (SELECT "count"("placementid") FROM "placement" KEY JOIN "employment" WHERE "PlaceDate" BETWEEN "WeekStartDate" AND "WeekEndDate" AND "employment"."companyid" = any(SELECT "companyid" FROM "Cos") AND "employment"."temp" = 1) AS "NewPlacementsTemp",
    "NewPlacementsAll"-"NewPlacementsTemp" AS "NewPlacementsPerm"
    FROM "rowgenerator" AS "a" CROSS JOIN "rowgenerator" AS "b"
    WHERE "Serial" < "pWeeks"
    ORDER BY "Serial" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportClientStats" IS 
{CREATE PROCEDURE pears."NetReportClientStats"(IN pWebUserID CHAR(20),IN Start_Date__d CHAR(20) DEFAULT NULL,IN Weeks__i CHAR(20) DEFAULT NULL)
RESULT(Serial__H INTEGER,Week_Start_Date__DX1Z DATE,Week_End_Date__DH DATE,New_Vacancies__GRT INTEGER,New_Vacancies_Temp__RT INTEGER,New_Vacancies_Perm__RT INTEGER,New_Placements__GRT INTEGER,New_Placements_Temp__RT INTEGER,New_Placements_Perm__RT INTEGER)
BEGIN
  DECLARE pWeeks INTEGER;
  DECLARE pStartDate DATE;
  DECLARE LOCAL TEMPORARY TABLE Cos(
    CompanyID CHAR(20) NOT NULL,
    PRIMARY KEY(CompanyID),
    ) NOT transactional;
  SET pWeeks=isnull(NULLIF(TRIM(Weeks__I),''),52);
  SET pStartDate=IQXNetStringToDate(Start_Date__D);
  IF pStartDate IS NULL THEN
    SET pStartDate=dateadd(week,-52,CURRENT DATE)
  END IF;
  INSERT INTO Cos( CompanyID) SELECT DISTINCT companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID;
  SELECT(a.row_num-1)*255+(b.row_num-1) AS Serial,
    dateadd(week,Serial,pStartDate) AS WeekStartDate,
    dateadd(DAY,6,WeekStartDate) AS WeekEndDate,
    (SELECT COUNT(vacancyid) FROM vacancy KEY JOIN employment WHERE EntryDate BETWEEN WeekStartDate AND WeekEndDate AND employment.companyid = any(SELECT companyid FROM Cos)) AS NewVacanciesAll,
    (SELECT COUNT(vacancyid) FROM vacancy KEY JOIN employment WHERE EntryDate BETWEEN WeekStartDate AND WeekEndDate AND employment.companyid = any(SELECT companyid FROM Cos) AND vacancy.temp = 1) AS NewVacanciesTemp,
    NewVacanciesAll-NewVacanciesTemp AS NewVacanciesPerm,
    (SELECT COUNT(placementid) FROM placement KEY JOIN employment WHERE PlaceDate BETWEEN WeekStartDate AND WeekEndDate AND employment.companyid = any(SELECT companyid FROM Cos)) AS NewPlacementsAll,
    (SELECT COUNT(placementid) FROM placement KEY JOIN employment WHERE PlaceDate BETWEEN WeekStartDate AND WeekEndDate AND employment.companyid = any(SELECT companyid FROM Cos) AND employment.temp = 1) AS NewPlacementsTemp,
    NewPlacementsAll-NewPlacementsTemp AS NewPlacementsPerm
    FROM rowgenerator AS a CROSS JOIN rowgenerator AS b
    WHERE Serial < pWeeks ORDER BY
    Serial ASC
END
}