pears.BroadBeanFields

Broadbean working table

CREATE VIEW "pears"."BroadBeanFields"( "VacancyID",
  "JobType","JobTitle","JobRef","StartDate","Days","Weeks","Months","Duration","Salary_From","Salary_To",
  "Salary_Per","Salary_Benefits","Skills","Description","BroadBeanLocationID","BroadBeanIndustry" ) 
  AS SELECT "Vacancy"."vacancyid" AS "VacancyID",
    IF "Vacancy"."temp" = 1 THEN 'Contract' ELSE 'Permanent' endif AS "JobType",
    "Vacancy"."position" AS "JobTitle",
    "Vacancy"."RefCode" AS "JobRef",
    "dateformat"("Vacancy"."StartDate",'Dd Mmmmmmmmmm yyyy') AS "StartDate",
    IF "vacancy"."FinishDate" IS NOT NULL THEN "datediff"("day","Vacancy"."StartDate","Vacancy"."FinishDate") endif AS "Days",
    IF "vacancy"."FinishDate" IS NOT NULL THEN "datediff"("week","Vacancy"."StartDate","Vacancy"."FinishDate") endif AS "Weeks",
    IF "vacancy"."FinishDate" IS NOT NULL THEN "datediff"("month","Vacancy"."StartDate","Vacancy"."FinishDate") endif AS "Months",
    IF "Days" > 0 THEN
      CASE WHEN "Months" > 1 THEN "string"("Months",' months')
      WHEN "Weeks" > 1 THEN "string"("Weeks",' weeks')
      ELSE "string"("Days",' days')
      END
    endif AS "Duration","round"(CAST("replace"("pears"."GetPayOrCharge"('P','V',"VacancyID"),',','') AS DOUBLE),0) AS "Salary_From",
    IF(("Vacancy"."temp" = 1) OR "isnull"("Vacancy"."salaryto",0) = 0) THEN
      "round"(CAST("replace"("pears"."GetPayOrCharge"('P','V',"VacancyID"),',','') AS DOUBLE),0)
    ELSE "vacancy"."salaryto"
    endif AS "Salary_To",IF "Vacancy"."Temp" = 1 THEN 'hour' ELSE 'annum' endif AS "Salary_Per",
    NULL AS "Salary_Benefits",
    NULL AS "Skills",
    IF "pears"."WPKMaintainGetSwitchValue"('BBVACNOTES','GLOBAL','L') = 'Y' THEN "Vacancy"."Othernotes" ELSE "Vacancy"."notes" endif AS "Description",
    "isnull"(
    (SELECT FIRST "BroadBeanLocation"."BroadBeanLocationID" FROM "pears"."BroadBeanLocation" WHERE "isnull"("vacancy"."town",(SELECT "company"."town" FROM "pears"."company" KEY JOIN "pears"."employment" WHERE "employment"."employmentid" = "vacancy"."employmentid")) LIKE "BroadBeanLocation"."Level3"),
    (SELECT FIRST "BroadBeanLocation"."BroadBeanLocationID" FROM "pears"."BroadBeanLocation" WHERE "isnull"("vacancy"."county",(SELECT "company"."county" FROM "pears"."company" KEY JOIN "pears"."employment" WHERE "employment"."employmentid" = "vacancy"."employmentid")) LIKE "BroadBeanLocation"."Level3"),
    (SELECT FIRST "BroadBeanLocation"."BroadBeanLocationID" FROM "pears"."BroadBeanLocation" WHERE "isnull"("vacancy"."county",(SELECT "company"."country" FROM "pears"."company" KEY JOIN "pears"."employment" WHERE "employment"."employmentid" = "vacancy"."employmentid")) LIKE "BroadBeanLocation"."Level3")) AS "BroadBeanLocationID",
    (SELECT FIRST "BroadBeanIndustry"."Name" FROM "pears"."BroadBeanIndustry" WHERE "locate"("BroadBeanIndustry"."Name",(SELECT "Department"."name" FROM "pears"."Department" WHERE "Department"."DepartmentID" = "Vacancy"."DepartmentID"),0) > 0) AS "BroadBeanIndustry"
    FROM "pears"."Vacancy"
GO
 
COMMENT ON VIEW "pears"."BroadBeanFields" IS 
	'Broadbean working table'
GO
 
COMMENT TO PRESERVE FORMAT ON VIEW "pears"."BroadBeanFields" IS 
{CREATE VIEW BroadBeanFields 
(VacancyID,JobType,JobTitle,JobRef,StartDate,Days,Weeks,Months,Duration,Salary_From,Salary_To,  
Salary_Per,Salary_Benefits,Skills,"Description",BroadBeanLocationID,BroadBeanIndustry)    
AS 
SELECT
 vacancyid AS VacancyID, 
 IF temp=1 THEN 'Contract' ELSE 'Permanent' endif AS JobType, 
 POSITION AS JobTitle, 
 RefCode AS JobRef,    
 dateformat(Vacancy.StartDate,'Dd Mmmmmmmmmm yyyy') AS StartDate, 
 IF vacancy.FinishDate IS NOT NULL THEN datediff(DAY,Vacancy.StartDate,Vacancy.FinishDate) endif AS Days, 
 IF vacancy.FinishDate IS NOT NULL THEN datediff(week,Vacancy.StartDate,Vacancy.FinishDate) endif AS Weeks, 
 IF vacancy.FinishDate IS NOT NULL THEN datediff(MONTH,Vacancy.StartDate,Vacancy.FinishDate) endif AS Months, 
 IF Days > 0 THEN  
 CASE  
   WHEN Months > 1 THEN string(Months,' months')  
   WHEN Weeks > 1 THEN string(Weeks,' weeks')   
   ELSE string(Days,' days')  
 END endif AS Duration,    
 round(CAST(REPLACE(GetPayOrCharge('P','V',VacancyID),',','') AS DOUBLE),0)AS Salary_From,  
 IF ((temp=1) OR isnull(salaryto,0)=0) THEN 
   round(CAST(REPLACE(GetPayOrCharge('P','V',VacancyID),',','') AS DOUBLE),0) 
 ELSE vacancy.salaryto endif  AS Salary_To, 
 IF Temp=1 THEN 'hour' ELSE 'annum' endif AS Salary_Per,    
 NULL AS Salary_Benefits,   
 NULL AS Skills,      
 IF WPKMaintainGetSwitchValue('BBVACNOTES','GLOBAL','L')= 'Y' THEN Othernotes ELSE notes endif AS "Description", 
  isnull(   
   (SELECT FIRST BroadBeanLocationID FROM BroadBeanLocation WHERE isnull(vacancy.town,(SELECT company.town FROM company KEY JOIN employment WHERE employment.employmentid = vacancy.employmentid )) LIKE Level3),  
   (SELECT FIRST BroadBeanLocationID FROM BroadBeanLocation WHERE isnull(vacancy.county,(SELECT company.county FROM company KEY JOIN employment WHERE employment.employmentid = vacancy.employmentid )) LIKE Level3),  
   (SELECT FIRST BroadBeanLocationID FROM BroadBeanLocation WHERE isnull(vacancy.county,(SELECT company.country FROM company KEY JOIN employment WHERE employment.employmentid = vacancy.employmentid )) LIKE Level3)  
)AS BroadBeanLocationID,
(SELECT FIRST BroadBeanIndustry.Name FROM BroadBeanIndustry WHERE locate(BroadBeanIndustry.Name,(SELECT name FROM Department WHERE Department.DepartmentID = Vacancy.DepartmentID),0) > 0 ) AS BroadBeanIndustry 
 FROM Vacancy
}