====== pears.NetReportFulfilment ======
<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"."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
}
</code>