pears.NetReportCandidateProgressHistory

CREATE PROCEDURE "pears"."NetReportCandidateProgressHistory"( IN "pWebUserID" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Company_Name" CHAR(100),"Vacancy_Name" CHAR(100),"Start_Date" CHAR(100),"Application_Date" CHAR(100),"Outcome" CHAR(100) ) 
BEGIN
  SELECT DISTINCT
    'Application History',
    "c"."name",
    "v"."position" AS "Position",
    "isnull"(CAST("dateformat"("v"."startdate",'Ddd dd Mmm yyyy') AS CHAR),'Not recorded') AS "StartDate",
    "isnull"(CAST("dateformat"("pr"."actiondate",'Ddd dd Mmm yyyy') AS CHAR),'Not recorded') AS "ActionDate",
    "s"."name" AS "State",
    "v"."startdate" AS "SortDate"
    FROM "iqxnetuserlink" AS "l"
      KEY JOIN "person" AS "pe"
      KEY JOIN "progress" AS "pr"
      KEY JOIN "vacancy" AS "v"
      KEY JOIN "employment"
      KEY JOIN "company" AS "c"
      ,"progress" AS "pr"
      JOIN "status" AS "s" ON "s"."status" = "pr"."status" AND "s"."type" = 'R'
    WHERE "l"."iqxnetuserid" = "pWebUserID"
    ORDER BY "SortDate" DESC
END /* DOC
2018-01-11 PC Combined two existing versions
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportCandidateProgressHistory" IS 
{CREATE PROCEDURE pears."NetReportCandidateProgressHistory"(IN pWebUserID CHAR(20))
RESULT(Title__H CHAR(100),Company_Name CHAR(100),Vacancy_Name CHAR(100),Start_Date CHAR(100),Application_Date CHAR(100),Outcome CHAR(100) )
BEGIN
	SELECT DISTINCT
    'Application History',
    c.name,
    v.position AS POSITION,
    isnull(CAST(dateformat(v.startdate,'Ddd dd Mmm yyyy') AS CHAR),'Not recorded') AS StartDate,
    isnull(CAST(dateformat(pr.actiondate,'Ddd dd Mmm yyyy') AS CHAR),'Not recorded') AS ActionDate,
    s.name AS State,
    v.startdate AS SortDate
FROM 
    iqxnetuserlink l
    KEY JOIN person pe
    KEY JOIN progress pr 
    KEY JOIN vacancy v
    KEY JOIN employment
    KEY JOIN company c,
    progress pr 
    JOIN STATUS s ON s.status=pr.status AND s.type='R'
WHERE 
    l.iqxnetuserid=pWebUserID
ORDER BY 
    SortDate DESC
 
END
 
/* DOC
2018-01-11 PC Combined two existing versions
 
*/
}