IQX Quick Menu

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
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
}
pears.NetReportVacShiftsCountOfHoursIncSite

Original SQL

iqx
