pears.NetReportFulfilment

CREATE PROCEDURE "pears"."NetReportFulfilment"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Position__1A" CHAR(100),"Bookings_Received__RT" INTEGER,"Bookings_Filled__RT" INTEGER,"Fill_Percentage__RMT" DOUBLE ) 
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  DECLARE "rTitle" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "Cos"(
    "CompanyID" CHAR(20) NULL,
    ) NOT transactional;
  SET "pstart" = "iqxnetstringtodate"("From__D");
  SET "pend" = "iqxnetstringtodate"("To__D");
  SET "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  -- Get all the Companies the WebUser is Associated with
  INSERT INTO "Cos"( "CompanyID" ) 
    SELECT DISTINCT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  SELECT "rTitle","vacancy"."position","sum"("isnull"("nullif"("vacancy"."noofposts",0),1)) AS "Books",
    "sum"((SELECT "count"() FROM "placement" WHERE "vacancyid" = "vacancy"."vacancyid")) AS "Fills",
    100.0*(CAST("Fills" AS DOUBLE)/CAST("Books" AS DOUBLE)) AS "FillPercent"
    FROM "vacancy" KEY JOIN "employment" KEY JOIN "company" JOIN "cos" ON "company"."companyid" = "cos"."companyid"
    WHERE "vacancy"."entrydate" BETWEEN "pstart" AND "pend" AND "vacancy"."entrydate" >= "IQXNetCompanyStartDate"("Company"."companyid")
    GROUP BY GROUPING sets((),("rTitle","vacancy"."position"))
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportFulfilment" IS 
{CREATE PROCEDURE pears.NetReportFulfilment(IN pWebUserID CHAR(20),IN From__D CHAR(20),IN To__D CHAR(20))
RESULT(Title__H CHAR(100),Position__1A CHAR(100),Bookings_Received__RT INTEGER,Bookings_Filled__RT INTEGER,Fill_Percentage__RMT DOUBLE)
BEGIN
  DECLARE pstart DATE;
  DECLARE pend DATE;
  DECLARE rTitle CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE Cos(
    CompanyID CHAR(20) NULL,
    ) NOT transactional;
  SET pstart=iqxnetstringtodate(From__D);
  SET pend=iqxnetstringtodate(To__D);
  SET rTitle=dateformat(pstart,'dd/mm/yyyy')+' - '+dateformat(pend,'dd/mm/yyyy');
  -- Get all the Companies the WebUser is Associated with
  INSERT INTO Cos( CompanyID) 
    SELECT DISTINCT companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID;
  SELECT rTitle,vacancy.position,SUM(isnull(NULLIF(vacancy.noofposts,0),1)) AS Books,
    SUM((SELECT COUNT(*) FROM placement WHERE vacancyid = vacancy.vacancyid)) AS Fills,
    100.0*(CAST(Fills AS DOUBLE)/CAST(Books AS DOUBLE)) AS FillPercent
    FROM vacancy KEY JOIN employment KEY JOIN company JOIN cos ON company.companyid = cos.companyid
    WHERE vacancy.entrydate BETWEEN pstart AND pend AND vacancy.entrydate >= IQXNetCompanyStartDate(Company.companyid)
    GROUP BY GROUPING sets((),(rTitle,vacancy.position))
END
}