pears.NetReportVacShiftsCountOfHoursIncSiteLines

CREATE PROCEDURE "pears"."NetReportVacShiftsCountOfHoursIncSiteLines"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pShiftDate" CHAR(15) ) 
RESULT( "Shift_Date__R" DATE,"Shift_Times__R" CHAR(50),"Break_Time__R" CHAR(20),"State__R" CHAR(1),"Candidate__R" CHAR(50),"Total_Hours__R" DECIMAL(10,2) ) 
BEGIN
  SELECT "TempShift"."ShiftDate" AS "Shift_Date",
    "string"("dateformat"("TempShift"."TimeFrom",'hh:nn'),' - ',"dateformat"("TempShift"."TimeTo",'hh:nn')) AS "Shift_Times",
    "TempShift"."BreakMinutes",
    "TempShift"."State",
    "String"("Temp"."Surname",', ',"GetWord"("Temp"."Forenames",1)) AS "Candidate",
    "getshiftlength"("TempShift"."TimeFrom","TempShift"."TimeTo","isnull"("TempShift"."BreakMinutes",0)) AS "Total_Hours"
    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" = "pVacancyID" AND "ShiftDate" = "pShiftDate"
    ORDER BY "Tempshift"."TimeFrom" ASC,"Candidate" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportVacShiftsCountOfHoursIncSiteLines" IS 
{CREATE PROCEDURE pears."NetReportVacShiftsCountOfHoursIncSiteLines"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pShiftDate" CHAR(15) ) 
RESULT( "Shift_Date__R" DATE,"Shift_Times__R" CHAR(50),"Break_Time__R" CHAR(20),"State__R" CHAR(1),"Candidate__R" CHAR(50),"Total_Hours__R" DECIMAL(10,2) ) 
BEGIN
  SELECT "TempShift"."ShiftDate" AS "Shift_Date",
    "string"("dateformat"("TempShift"."TimeFrom",'hh:nn'),' - ',"dateformat"("TempShift"."TimeTo",'hh:nn')) AS "Shift_Times",
    "TempShift"."BreakMinutes",
    "TempShift"."State",
    "String"("Temp"."Surname",', ',"GetWord"("Temp"."Forenames",1)) AS "Candidate",
    "getshiftlength"("TempShift"."TimeFrom","TempShift"."TimeTo","isnull"("TempShift"."BreakMinutes",0)) AS "Total_Hours"
    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" = "pVacancyID" AND "ShiftDate" = "pShiftDate"
    ORDER BY "Tempshift"."TimeFrom" ASC,"Candidate" ASC
END
}