Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetClientVacanciesExtended"( IN "pWebUserID" CHAR(20),IN "pStartDate" CHAR(20) DEFAULT NULL,IN "pEndDate" CHAR(20) DEFAULT NULL,IN "pPageNo" INTEGER DEFAULT 1,IN "SearchString" CHAR(100) DEFAULT NULL,IN "SampleSize" INTEGER DEFAULT 25 ) RESULT( "RowID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishTime" TIME,"IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftPlanID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Status" CHAR(40),"Department" CHAR(30),"Consultant" CHAR(60),"OurRef" CHAR(20),"TheirReference" CHAR(50),"ContractRef" CHAR(20),"ShiftRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250),"PersonID" CHAR(20),"TextStatus" CHAR(100),"WhoCancelled" CHAR(5),"BlockColour" CHAR(50),"CancelButton" CHAR(10),"ConfirmButton" CHAR(10),"RejectButton" CHAR(10),"FillButton" CHAR(10),"Location" CHAR(50),"PONumber" CHAR(50),"StatusHead" CHAR(50),"ClientComment" long VARCHAR,"CreatedBy" CHAR(150),"DepartmentID" CHAR(20),"ClientCommentShort" CHAR(25),"CombinedStatus" CHAR(50),"OutStartDate" CHAR(20),"OutEndDate" CHAR(20),"RowCount" INTEGER ) -- 20110815 include shift date and time as well as plan date and time -- 20110823 use temporary table for lookup to improve speed -- 20111117 include candidateid for link to view details -- 20111128 incorporate Current Shift Status tab -- 20120213 split location and PO number column, and add Client Note. Also include requested candidate in red. -- 20120403 include '(S)' on temps from secondary agencies -- 20120601 lengthen ClientComment to 200 charcters -- 20120611 add extra detail at top of grey expansion box -- 20120614 include departmentid for department questionnaire on person details popup -- 20120619 include long and short comments for popup -- 20120704 include cancel reason, include Processing status, include updateable times -- 20120705 include new processing status for unfilled shifts -- 20120706 client comment insert modified -- 20120711 clientvacancies split into master and detail for speed -- 20120719 with paging and text filter -- 20120731 RowID added to Order by clause to ensure data always appears in same order -- 20120802 ShiftRef added to sort clause -- 20120809 reverse name+surname, rowcount, different colour for cancelled by temp -- 20121107 change colours -- 20121112 change Cancelled (G) to Unable To Supply -- 20121113 remove Deleted vacancies -- 20121123 modified by mhs -- 20130213 replace GenevaNetClientVacancies BEGIN DECLARE @DateRangeStart DATE; DECLARE @DateRangeEnd DATE; DECLARE @companycount INTEGER; DECLARE "SecAgencyCode" CHAR(1); DECLARE "pStart" DATE; DECLARE "pEnd" DATE; DECLARE "RangeStart" INTEGER; DECLARE "RangeEnd" INTEGER; DECLARE "SearchStr" CHAR(100); DECLARE LOCAL TEMPORARY TABLE "IDs"( "ID" CHAR(20) NOT NULL, "CompanyStartDate" DATE NULL, ) NOT transactional; DECLARE LOCAL TEMPORARY TABLE "DSet"( "RowID" CHAR(30) NULL, "StartDate" DATE NULL, "StartTime" TIME NULL, "FinishTime" TIME NULL, "IsShift" SMALLINT NULL, "VacancyID" CHAR(20) NULL, "TempShiftPlanID" CHAR(20) NULL, "Position" CHAR(50) NULL, "ShiftDescription" CHAR(50) NULL, "CompanyName" CHAR(60) NULL, "ContactName" CHAR(30) NULL, "Status" CHAR(40) NULL, "Department" CHAR(30) NULL, "Consultant" CHAR(60) NULL, "OurRef" CHAR(20) NULL, "TheirReference" CHAR(50) NULL, "ContractRef" CHAR(20) NULL, "ShiftRef" CHAR(20) NULL, "CascadeDateTime" TIMESTAMP NULL, "CascadeLevel" SMALLINT NULL, "WhenEntered" TIMESTAMP NULL, "FilledBy" CHAR(250) NULL, "Filled" SMALLINT NULL, "Unconfirmed" SMALLINT NULL, "LongDescription" long VARCHAR NULL, "CompanyAddress" CHAR(250) NULL, "PersonID" CHAR(20) NULL, "TextStatus" CHAR(100) NULL, "WhoCancelled" CHAR(5) NULL, "BlockColour" CHAR(50) NULL, "CancelButton" CHAR(10) NULL, "ConfirmButton" CHAR(10) NULL, "RejectButton" CHAR(10) NULL, "FillButton" CHAR(10) NULL, "Location" CHAR(50) NULL, "PONumber" CHAR(50) NULL, "StatusHead" CHAR(50) NULL, "ClientComment" long VARCHAR NULL, "CreatedBy" CHAR(150) NULL, "DepartmentID" CHAR(20) NULL, "ClientCommentShort" CHAR(25) NULL, "CombinedStatus" CHAR(50) NULL, "SortColumn" INTEGER NULL, ) NOT transactional; INSERT INTO "IDs"( "ID","CompanyStartDate" ) SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"; SET "pStart" = "iqxnetstringtodate"("pStartDate"); SET "pEnd" = "iqxnetstringtodate"("pEndDate"); IF "SampleSize" = 1 THEN SET @DateRangeStart = "dateadd"("year",-10,CURRENT DATE); SET "SearchString" = '' ELSE SET @DateRangeStart = "isnull"("pStart","dateadd"("month",-3,CURRENT DATE)) END IF; SET @DateRangeEnd = "isnull"("pEnd","dateadd"("year",1,CURRENT DATE)); SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); SET "SecAgencyCode" = 'A'; SET "RangeStart" = (("pPageNo"-1)*"SampleSize"); SET "RangeEnd" = ("pPageNo")*"SampleSize"; SET "SearchStr" = "trim"("replace"("SearchString",' ','%')); IF "RangeStart" > 1 THEN SET "RangeStart" = "RangeStart"+1 END IF; -- build recordset and insert into temporary table INSERT INTO "DSet" SELECT 'V'+"v"."vacancyid" AS "RowID", "v"."startdate", --changed here CAST(NULL AS TIME) AS "StartTime", CAST(NULL AS TIME) AS "FinishTime", 0 AS "IsShift", "v"."VacancyID", CAST(NULL AS CHAR(20)) AS "TempShiftPlanID", "v"."Position" AS "Position", CAST(NULL AS CHAR(50)) AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", "v"."RefCode" AS "OurRef", "v"."theirref" AS "TheirReference", "v"."ContractRef", "v"."refcode" AS "ShiftRef", "v"."CascadeDateTime", "v"."CascadeLevel", "v"."WhenEntered", (SELECT "list"("string"("person"."surname",', ',"person"."forenames",(IF "person"."status" = "SecAgencyCode" THEN ' (S)' endif))) FROM "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "placement"."vacancyid" = "v"."vacancyid") AS "FilledBy", "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", "v"."OtherNotes" AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("e"."companyid") ELSE '' endif) AS "CompanyAddress",CAST(NULL AS CHAR(20)) AS "PersonID",CAST(NULL AS CHAR(100)) AS "statusText", CAST(NULL AS CHAR(5)) AS "WhoCancelled",'B' AS "BlockColour",'0' AS "CancelButton",'0' AS "ConfirmButton",'0' AS "RejectButton",'0' AS "FillButton", CAST(NULL AS CHAR(50)) AS "TheirLocation", "v"."theirref" AS "PONumber", CAST(NULL AS CHAR(50)) AS "StatusHead", CAST(NULL AS CHAR(100)) AS "ClientComment", CAST(NULL AS CHAR(150)) AS "CreatedBy", "v"."DepartmentID", CAST(NULL AS CHAR(25)) AS "ClientCommentShort", CAST(NULL AS CHAR(50)) AS "CombinedStatus", "ShiftRef" AS "SortColumn" FROM "vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person") JOIN "IDs" ON "e"."CompanyID" = "IDs"."ID" WHERE "v"."status" NOT IN( 'D' ) AND "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart UNION ALL SELECT 'P'+"p"."TempShiftPlanID" AS "RowID", "isnull"("ts"."shiftdate","p"."ShiftDate") AS "StartDate", "isnull"("ts"."timefrom","p"."TimeFrom") AS "StartTime", "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime", 1 AS "IsShift", "v"."VacancyID", "p"."TempShiftPlanID", "v"."Position" AS "Position", "p"."Description" AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Pe"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", "v"."RefCode" AS "OurRef", "GetQuestAnswer"('S','TS1',"p"."tempshiftplanid") AS "TheirReference", "v"."ContractRef", CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "ShiftRef", "p"."CascadeDateTime", "p"."CascadeLevel", "p"."WhenEntered", "isnull"((SELECT "name" FROM "person" WHERE "personid" = "ts"."personid"),"GetQuestAnswer"('S','TS2',"p"."tempshiftplanid"))+(IF(SELECT "status" FROM "person" WHERE "personid" = "ts"."personid") = "SecAgencyCode" THEN ' (S)' endif) AS "FilledBy", "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", CAST(NULL AS long VARCHAR) AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress",'' AS "PersonID", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'In Progress' ELSE 'Pending' endif) AS "statusText",'' AS "WhoCancelled", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'I' ELSE 'U' endif) AS "BlockColour",'1' AS "CancelButton",'0' AS "ConfirmButton",'0' AS "RejectButton",'1' AS "FillButton", "ClientNote" AS "TheirLocation", "p"."ReferenceCode" AS "PONumber", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'In Progress' ELSE 'Pending' endif) AS "StatusHead", "substring"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid"),1,149) AS "ClientComment",('Ordered by: ' +"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) AS "CreatedBy", "v"."DepartmentID", "substring"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid"),1,23)+(IF "length"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid")) > 23 THEN ' >' endif) AS "ClientCommentShort", ("StatusHead"+' '+"WhoCancelled") AS "CombinedStatus", "ShiftRef" AS "SortColumn" FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person" AS "Pe") JOIN "IDs" ON "e"."CompanyID" = "IDs"."ID" ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "tempshift" AS "ts" ON "p"."tempshiftplanid" = "ts"."tempshiftplanid" AND "ts"."state" <> 'C' WHERE NOT "p"."tempshiftplanid" = any(SELECT "tempshiftplanid" FROM "tempshift" WHERE "vacancyid" = "p"."vacancyid" AND "shiftdate" = "p"."shiftdate" AND(NOT("state" = 'C' AND "crefill" = 1)) AND "tempshiftplanid" IS NOT NULL) AND "StartDate" BETWEEN @DateRangeStart AND @DateRangeEnd AND "v"."status" NOT IN( 'D' ) ORDER BY "SortColumn" DESC; -- build temp recordset with RowNumber in first column WITH "RowData" AS(SELECT "row_number"() OVER(ORDER BY "SortColumn" DESC) AS "RowNumber", "RowID","StartDate","StartTime","FinishTime","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirReference","ContractRef","ShiftRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","WhoCancelled","BlockColour","CancelButton","ConfirmButton","RejectButton","FillButton","Location","PONumber","StatusHead","ClientComment","CreatedBy","DepartmentID","ClientCommentShort","CombinedStatus","SortColumn" FROM "DSet" WHERE("position" LIKE '%'+"SearchStr"+'%' OR "Location" LIKE '%'+"SearchStr"+'%' OR "PONumber" LIKE '%'+"SearchStr"+'%' OR "FilledBy" LIKE '%'+"SearchStr"+'%' OR "ClientCommentShort" LIKE '%'+"SearchStr"+'%' OR "StartDate" LIKE '%'+"SearchStr"+'%' OR "ShiftRef" LIKE '%'+"SearchStr"+'%') ORDER BY "RowNumber" DESC) -- output full dataset without RowNumber filtered on RowNumber SELECT "RowID","StartDate","StartTime","FinishTime","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirReference","ContractRef","ShiftRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","WhoCancelled","BlockColour","CancelButton","ConfirmButton","RejectButton","FillButton","Location","PONumber","StatusHead","ClientComment","CreatedBy","DepartmentID","ClientCommentShort","CombinedStatus","pStartDate","pEndDate",@@ROWCOUNT FROM "RowData" WHERE("RowNumber" BETWEEN "RangeStart" AND "RangeEnd") ORDER BY "RowNumber" ASC END GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesExtended" IS {CREATE PROCEDURE pears."NetClientVacanciesExtended"( IN "pWebUserID" CHAR(20),IN "pStartDate" CHAR(20) DEFAULT NULL,IN "pEndDate" CHAR(20) DEFAULT NULL,IN "pPageNo" INTEGER DEFAULT 1,IN "SearchString" CHAR(100) DEFAULT NULL,IN "SampleSize" INTEGER DEFAULT 25 ) RESULT( "RowID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishTime" TIME,"IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftPlanID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Status" CHAR(40),"Department" CHAR(30),"Consultant" CHAR(60),"OurRef" CHAR(20),"TheirReference" CHAR(50),"ContractRef" CHAR(20),"ShiftRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250),"PersonID" CHAR(20),"TextStatus" CHAR(100),"WhoCancelled" CHAR(5),"BlockColour" CHAR(50),"CancelButton" CHAR(10),"ConfirmButton" CHAR(10),"RejectButton" CHAR(10),"FillButton" CHAR(10),"Location" CHAR(50),"PONumber" CHAR(50),"StatusHead" CHAR(50),"ClientComment" long VARCHAR,"CreatedBy" CHAR(150),"DepartmentID" CHAR(20),"ClientCommentShort" CHAR(25),"CombinedStatus" CHAR(50),"OutStartDate" CHAR(20),"OutEndDate" CHAR(20),"RowCount" INTEGER ) -- 20110815 include shift date and time as well as plan date and time -- 20110823 use temporary table for lookup to improve speed -- 20111117 include candidateid for link to view details -- 20111128 incorporate Current Shift Status tab -- 20120213 split location and PO number column, and add Client Note. Also include requested candidate in red. -- 20120403 include '(S)' on temps from secondary agencies -- 20120601 lengthen ClientComment to 200 charcters -- 20120611 add extra detail at top of grey expansion box -- 20120614 include departmentid for department questionnaire on person details popup -- 20120619 include long and short comments for popup -- 20120704 include cancel reason, include Processing status, include updateable times -- 20120705 include new processing status for unfilled shifts -- 20120706 client comment insert modified -- 20120711 clientvacancies split into master and detail for speed -- 20120719 with paging and text filter -- 20120731 RowID added to Order by clause to ensure data always appears in same order -- 20120802 ShiftRef added to sort clause -- 20120809 reverse name+surname, rowcount, different colour for cancelled by temp -- 20121107 change colours -- 20121112 change Cancelled (G) to Unable To Supply -- 20121113 remove Deleted vacancies -- 20121123 modified by mhs -- 20130213 replace GenevaNetClientVacancies BEGIN DECLARE @DateRangeStart DATE; DECLARE @DateRangeEnd DATE; DECLARE @companycount INTEGER; DECLARE "SecAgencyCode" CHAR(1); DECLARE "pStart" DATE; DECLARE "pEnd" DATE; DECLARE "RangeStart" INTEGER; DECLARE "RangeEnd" INTEGER; DECLARE "SearchStr" CHAR(100); DECLARE LOCAL TEMPORARY TABLE "IDs"( "ID" CHAR(20) NOT NULL, "CompanyStartDate" DATE NULL, ) NOT transactional; DECLARE LOCAL TEMPORARY TABLE "DSet"( "RowID" CHAR(30) NULL, "StartDate" DATE NULL, "StartTime" TIME NULL, "FinishTime" TIME NULL, "IsShift" SMALLINT NULL, "VacancyID" CHAR(20) NULL, "TempShiftPlanID" CHAR(20) NULL, "Position" CHAR(50) NULL, "ShiftDescription" CHAR(50) NULL, "CompanyName" CHAR(60) NULL, "ContactName" CHAR(30) NULL, "Status" CHAR(40) NULL, "Department" CHAR(30) NULL, "Consultant" CHAR(60) NULL, "OurRef" CHAR(20) NULL, "TheirReference" CHAR(50) NULL, "ContractRef" CHAR(20) NULL, "ShiftRef" CHAR(20) NULL, "CascadeDateTime" TIMESTAMP NULL, "CascadeLevel" SMALLINT NULL, "WhenEntered" TIMESTAMP NULL, "FilledBy" CHAR(250) NULL, "Filled" SMALLINT NULL, "Unconfirmed" SMALLINT NULL, "LongDescription" long VARCHAR NULL, "CompanyAddress" CHAR(250) NULL, "PersonID" CHAR(20) NULL, "TextStatus" CHAR(100) NULL, "WhoCancelled" CHAR(5) NULL, "BlockColour" CHAR(50) NULL, "CancelButton" CHAR(10) NULL, "ConfirmButton" CHAR(10) NULL, "RejectButton" CHAR(10) NULL, "FillButton" CHAR(10) NULL, "Location" CHAR(50) NULL, "PONumber" CHAR(50) NULL, "StatusHead" CHAR(50) NULL, "ClientComment" long VARCHAR NULL, "CreatedBy" CHAR(150) NULL, "DepartmentID" CHAR(20) NULL, "ClientCommentShort" CHAR(25) NULL, "CombinedStatus" CHAR(50) NULL, "SortColumn" INTEGER NULL, ) NOT transactional; INSERT INTO "IDs"( "ID","CompanyStartDate" ) SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"; SET "pStart" = "iqxnetstringtodate"("pStartDate"); SET "pEnd" = "iqxnetstringtodate"("pEndDate"); IF "SampleSize" = 1 THEN SET @DateRangeStart = "dateadd"("year",-10,CURRENT DATE); SET "SearchString" = '' ELSE SET @DateRangeStart = "isnull"("pStart","dateadd"("month",-3,CURRENT DATE)) END IF; SET @DateRangeEnd = "isnull"("pEnd","dateadd"("year",1,CURRENT DATE)); SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); SET "SecAgencyCode" = 'A'; SET "RangeStart" = (("pPageNo"-1)*"SampleSize"); SET "RangeEnd" = ("pPageNo")*"SampleSize"; SET "SearchStr" = "trim"("replace"("SearchString",' ','%')); IF "RangeStart" > 1 THEN SET "RangeStart" = "RangeStart"+1 END IF; -- build recordset and insert into temporary table INSERT INTO "DSet" SELECT 'V'+"v"."vacancyid" AS "RowID", "v"."startdate", --changed here CAST(NULL AS TIME) AS "StartTime", CAST(NULL AS TIME) AS "FinishTime", 0 AS "IsShift", "v"."VacancyID", CAST(NULL AS CHAR(20)) AS "TempShiftPlanID", "v"."Position" AS "Position", CAST(NULL AS CHAR(50)) AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", "v"."RefCode" AS "OurRef", "v"."theirref" AS "TheirReference", "v"."ContractRef", "v"."refcode" AS "ShiftRef", "v"."CascadeDateTime", "v"."CascadeLevel", "v"."WhenEntered", (SELECT "list"("string"("person"."surname",', ',"person"."forenames",(IF "person"."status" = "SecAgencyCode" THEN ' (S)' endif))) FROM "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "placement"."vacancyid" = "v"."vacancyid") AS "FilledBy", "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", "v"."OtherNotes" AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("e"."companyid") ELSE '' endif) AS "CompanyAddress",CAST(NULL AS CHAR(20)) AS "PersonID",CAST(NULL AS CHAR(100)) AS "statusText", CAST(NULL AS CHAR(5)) AS "WhoCancelled",'B' AS "BlockColour",'0' AS "CancelButton",'0' AS "ConfirmButton",'0' AS "RejectButton",'0' AS "FillButton", CAST(NULL AS CHAR(50)) AS "TheirLocation", "v"."theirref" AS "PONumber", CAST(NULL AS CHAR(50)) AS "StatusHead", CAST(NULL AS CHAR(100)) AS "ClientComment", CAST(NULL AS CHAR(150)) AS "CreatedBy", "v"."DepartmentID", CAST(NULL AS CHAR(25)) AS "ClientCommentShort", CAST(NULL AS CHAR(50)) AS "CombinedStatus", "ShiftRef" AS "SortColumn" FROM "vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person") JOIN "IDs" ON "e"."CompanyID" = "IDs"."ID" WHERE "v"."status" NOT IN( 'D' ) AND "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart UNION ALL SELECT 'P'+"p"."TempShiftPlanID" AS "RowID", "isnull"("ts"."shiftdate","p"."ShiftDate") AS "StartDate", "isnull"("ts"."timefrom","p"."TimeFrom") AS "StartTime", "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime", 1 AS "IsShift", "v"."VacancyID", "p"."TempShiftPlanID", "v"."Position" AS "Position", "p"."Description" AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Pe"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", "v"."RefCode" AS "OurRef", "GetQuestAnswer"('S','TS1',"p"."tempshiftplanid") AS "TheirReference", "v"."ContractRef", CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "ShiftRef", "p"."CascadeDateTime", "p"."CascadeLevel", "p"."WhenEntered", "isnull"((SELECT "name" FROM "person" WHERE "personid" = "ts"."personid"),"GetQuestAnswer"('S','TS2',"p"."tempshiftplanid"))+(IF(SELECT "status" FROM "person" WHERE "personid" = "ts"."personid") = "SecAgencyCode" THEN ' (S)' endif) AS "FilledBy", "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", CAST(NULL AS long VARCHAR) AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress",'' AS "PersonID", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'In Progress' ELSE 'Pending' endif) AS "statusText",'' AS "WhoCancelled", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'I' ELSE 'U' endif) AS "BlockColour",'1' AS "CancelButton",'0' AS "ConfirmButton",'0' AS "RejectButton",'1' AS "FillButton", "ClientNote" AS "TheirLocation", "p"."ReferenceCode" AS "PONumber", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'In Progress' ELSE 'Pending' endif) AS "StatusHead", "substring"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid"),1,149) AS "ClientComment",('Ordered by: ' +"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) AS "CreatedBy", "v"."DepartmentID", "substring"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid"),1,23)+(IF "length"("GetQuestAnswer"('S','TS3',"p"."tempshiftplanid")) > 23 THEN ' >' endif) AS "ClientCommentShort", ("StatusHead"+' '+"WhoCancelled") AS "CombinedStatus", "ShiftRef" AS "SortColumn" FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person" AS "Pe") JOIN "IDs" ON "e"."CompanyID" = "IDs"."ID" ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "tempshift" AS "ts" ON "p"."tempshiftplanid" = "ts"."tempshiftplanid" AND "ts"."state" <> 'C' WHERE NOT "p"."tempshiftplanid" = any(SELECT "tempshiftplanid" FROM "tempshift" WHERE "vacancyid" = "p"."vacancyid" AND "shiftdate" = "p"."shiftdate" AND(NOT("state" = 'C' AND "crefill" = 1)) AND "tempshiftplanid" IS NOT NULL) AND "StartDate" BETWEEN @DateRangeStart AND @DateRangeEnd AND "v"."status" NOT IN( 'D' ) ORDER BY "SortColumn" DESC; -- build temp recordset with RowNumber in first column WITH "RowData" AS(SELECT "row_number"() OVER(ORDER BY "SortColumn" DESC) AS "RowNumber", "RowID","StartDate","StartTime","FinishTime","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirReference","ContractRef","ShiftRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","WhoCancelled","BlockColour","CancelButton","ConfirmButton","RejectButton","FillButton","Location","PONumber","StatusHead","ClientComment","CreatedBy","DepartmentID","ClientCommentShort","CombinedStatus","SortColumn" FROM "DSet" WHERE("position" LIKE '%'+"SearchStr"+'%' OR "Location" LIKE '%'+"SearchStr"+'%' OR "PONumber" LIKE '%'+"SearchStr"+'%' OR "FilledBy" LIKE '%'+"SearchStr"+'%' OR "ClientCommentShort" LIKE '%'+"SearchStr"+'%' OR "StartDate" LIKE '%'+"SearchStr"+'%' OR "ShiftRef" LIKE '%'+"SearchStr"+'%') ORDER BY "RowNumber" DESC) -- output full dataset without RowNumber filtered on RowNumber SELECT "RowID","StartDate","StartTime","FinishTime","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirReference","ContractRef","ShiftRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","WhoCancelled","BlockColour","CancelButton","ConfirmButton","RejectButton","FillButton","Location","PONumber","StatusHead","ClientComment","CreatedBy","DepartmentID","ClientCommentShort","CombinedStatus","pStartDate","pEndDate",@@ROWCOUNT FROM "RowData" WHERE("RowNumber" BETWEEN "RangeStart" AND "RangeEnd") ORDER BY "RowNumber" ASC END }