pears.NetReportClientStats [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetReportClientStats ======
<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"."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
}
</code>