====== pears.NetReportVacShiftsCountOfHoursIncSite ======
<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"."NetReportVacShiftsCountOfHoursIncSite"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20),in "Site__XClientSite" long varchar ) 
result( "Title__H" char(100),"Company_Name__1" char(100),"Position__2" char(50),"Shift_Date_Sort__H" char(15),"Shift_Date" char(15),"VacID__H" char(20),"Total_Shifts__IRT" integer,"Total_Hours__MRT" decimal(10,2),"Details__Eta" char(200) ) 
--20120305 second intermediate temp table inserted, to speed up render 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "sites" long varchar;
  declare "rTitle" char(100);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  -- load main query into here, to smooth out subqueries. 
  declare local temporary table "MainLoop"(
    "Company_Name" char(100) null,
    "Position" char(100) null,
    "Shift_Date_Sort" char(20) null,
    "Shift_Date" char(20) null,
    "VacID" char(20) null,
    "Total_Shifts" integer null,
    "Total_Hours" real null,
    "Details" char(255) null,
    ) not transactional;
  set "sites" = "string"("char"(9),"Site__XClientSite","char"(9));
  insert into "Cos"( "CompanyID" ) select distinct "companyid" from "employment" key join "iqxnetuserlink"
      where "iqxnetuserid" = "pWebUserID" and "charindex"("string"("char"(9),"companyid","char"(9)),"sites") > 0;
  set "pstart" = "iqxnetstringtodate"("From__D");
  set "pend" = "iqxnetstringtodate"("To__D");
  set "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  insert into "MainLoop"( "Company_Name","Position","Shift_Date_Sort","Shift_Date","VacID","Total_Shifts","Total_Hours","Details" ) (
    select distinct "Company"."Name"+', '+"Company"."addr1" as "Company_Name",
      "Vacancy"."Position" as "Position",
      "TempShift"."ShiftDate" as "Shift_Date_Sort",
      "dateformat"("TempShift"."ShiftDate",'Ddd dd/mm/yyyy') as "Shift_Date",
      "Vacancy"."VacancyID" as "VacID",
      (select "count"() from "Vacancy" key join "TempShift" key join "Person" as "Temp"
          ,"vacancy" key join("employment" key join("company","person"))
          ,"vacancy" join "status" on "vacancy"."status" = "status"."status" and "status"."type" = 'V'
        where "TempShift"."State" in( 'P','B','W' ) and "Vacancy"."VacancyID" = "VacID" and "ShiftDate" = "Shift_Date_Sort") as "Total_Shifts",
      (select "sum"("getshiftlength"("TempShift"."TimeFrom","TempShift"."TimeTo","isnull"("TempShift"."BreakMinutes",0))) from "Vacancy" key join "TempShift" key join "Person" as "Temp"
          ,"vacancy" key join("employment" key join("company","person"))
          ,"vacancy" join "status" on "vacancy"."status" = "status"."status" and "status"."type" = 'V'
        where "TempShift"."State" in( 'P','B','W' ) and "Vacancy"."VacancyID" = "VacID" and "ShiftDate" = "Shift_Date_Sort") as "Total_Hours",
      "string"('NetReportVacShiftsCountOfHoursIncSiteLines&pVacancyID=',"vacancy"."vacancyID",'&pshiftdate=',"Shift_Date_Sort") as "Details"
      from "Vacancy" key join "TempShift" key join "Person" as "Temp"
        ,"vacancy" key join("employment" key join(("company" join "Cos" on "Cos"."CompanyID" = "company"."CompanyID"),"person"))
        ,"vacancy" join "status" on "vacancy"."status" = "status"."status" and "status"."type" = 'V'
      where "TempShift"."State" in( 'P','B','W' ) 
      and "ShiftDate" between "pstart" and "pend"
      order by "Company_Name" asc,"Position" asc,"Shift_Date_Sort" asc);
  select "rTitle","Company_Name","Position","Shift_Date_Sort","Shift_Date","VacID","Total_Shifts","Total_Hours","Details" from "MainLoop"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportVacShiftsCountOfHoursIncSite" IS 
{create procedure pears."NetReportVacShiftsCountOfHoursIncSite"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20),in "Site__XClientSite" long varchar ) 
result( "Title__H" char(100),"Company_Name__1" char(100),"Position__2" char(50),"Shift_Date_Sort__H" char(15),"Shift_Date" char(15),"VacID__H" char(20),"Total_Shifts__IRT" integer,"Total_Hours__MRT" decimal(10,2),"Details__Eta" char(200) ) 
--20120305 second intermediate temp table inserted, to speed up render 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "sites" long varchar;
  declare "rTitle" char(100);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  -- load main query into here, to smooth out subqueries. 
  declare local temporary table "MainLoop"(
    "Company_Name" char(100) null,
    "Position" char(100) null,
    "Shift_Date_Sort" char(20) null,
    "Shift_Date" char(20) null,
    "VacID" char(20) null,
    "Total_Shifts" integer null,
    "Total_Hours" real null,
    "Details" char(255) null,
    ) not transactional;
  set "sites" = "string"("char"(9),"Site__XClientSite","char"(9));
  insert into "Cos"( "CompanyID" ) select distinct "companyid" from "employment" key join "iqxnetuserlink"
      where "iqxnetuserid" = "pWebUserID" and "charindex"("string"("char"(9),"companyid","char"(9)),"sites") > 0;
  set "pstart" = "iqxnetstringtodate"("From__D");
  set "pend" = "iqxnetstringtodate"("To__D");
  set "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  insert into "MainLoop"( "Company_Name","Position","Shift_Date_Sort","Shift_Date","VacID","Total_Shifts","Total_Hours","Details" ) (
    select distinct "Company"."Name"+', '+"Company"."addr1" as "Company_Name",
      "Vacancy"."Position" as "Position",
      "TempShift"."ShiftDate" as "Shift_Date_Sort",
      "dateformat"("TempShift"."ShiftDate",'Ddd dd/mm/yyyy') as "Shift_Date",
      "Vacancy"."VacancyID" as "VacID",
      (select "count"() from "Vacancy" key join "TempShift" key join "Person" as "Temp"
          ,"vacancy" key join("employment" key join("company","person"))
          ,"vacancy" join "status" on "vacancy"."status" = "status"."status" and "status"."type" = 'V'
        where "TempShift"."State" in( 'P','B','W' ) and "Vacancy"."VacancyID" = "VacID" and "ShiftDate" = "Shift_Date_Sort") as "Total_Shifts",
      (select "sum"("getshiftlength"("TempShift"."TimeFrom","TempShift"."TimeTo","isnull"("TempShift"."BreakMinutes",0))) from "Vacancy" key join "TempShift" key join "Person" as "Temp"
          ,"vacancy" key join("employment" key join("company","person"))
          ,"vacancy" join "status" on "vacancy"."status" = "status"."status" and "status"."type" = 'V'
        where "TempShift"."State" in( 'P','B','W' ) and "Vacancy"."VacancyID" = "VacID" and "ShiftDate" = "Shift_Date_Sort") as "Total_Hours",
      "string"('NetReportVacShiftsCountOfHoursIncSiteLines&pVacancyID=',"vacancy"."vacancyID",'&pshiftdate=',"Shift_Date_Sort") as "Details"
      from "Vacancy" key join "TempShift" key join "Person" as "Temp"
        ,"vacancy" key join("employment" key join(("company" join "Cos" on "Cos"."CompanyID" = "company"."CompanyID"),"person"))
        ,"vacancy" join "status" on "vacancy"."status" = "status"."status" and "status"."type" = 'V'
      where "TempShift"."State" in( 'P','B','W' ) 
      and "ShiftDate" between "pstart" and "pend"
      order by "Company_Name" asc,"Position" asc,"Shift_Date_Sort" asc);
  select "rTitle","Company_Name","Position","Shift_Date_Sort","Shift_Date","VacID","Total_Shifts","Total_Hours","Details" from "MainLoop"
end
}
</code>