Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetTimesheetsAllFilteredExtended"( 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( "temptimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"weekenddate" DATE,"timesheettype" CHAR(1),"timesheettotal" CHAR(12),"companyaddress" CHAR(250),"theirref" CHAR(50),"InvoiceNo" CHAR(25),"ExternalStatus" INTEGER,"ShiftList" CHAR(500),"RowCount" INTEGER ) -- 20121114 force two decimal places on total -- 20121218 add Shift List to allow searching -- 20130219 derived from GenevaNetTimesheetsAllFiltered BEGIN DECLARE "userClass" CHAR(20); DECLARE @companycount INTEGER; DECLARE "divid" CHAR(20); DECLARE "pstart" DATE; DECLARE "pend" DATE; DECLARE "RangeStart" INTEGER; DECLARE "SearchStr" CHAR(100); DECLARE LOCAL TEMPORARY TABLE "DSet"( "temptimesheetid" CHAR(20) NULL, "serialnumber" CHAR(20) NULL, "tempname" CHAR(60) NULL, "position" CHAR(60) NULL, "companyname" CHAR(60) NULL, "weekenddate" DATE NULL, "timesheettype" CHAR(1) NULL, "timesheettotal" DECIMAL(10,2) NULL, "companyaddress" CHAR(250) NULL, "theirref" CHAR(50) NULL, "InvoiceNo" CHAR(25) NULL, "ExternalStatus" bit NULL, "ShiftList" CHAR(500) NULL, ) NOT transactional; SET "RangeStart" = (("pPageNo"-1)*"SampleSize"); SET "SearchStr" = "trim"("replace"("SearchString",' ','%')); IF "RangeStart" = 0 THEN SET "RangeStart" = 1 END IF; IF "RangeStart" > 1 THEN SET "RangeStart" = "RangeStart"+1 END IF; SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID"); SET "pStart" = "isnull"("iqxnetstringtodate"("pStartDate"),"dateadd"("week",-1,CURRENT DATE)); SET "pEnd" = "isnull"("iqxnetstringtodate"("pEndDate"),"dateadd"("week",1,CURRENT DATE)); IF "userClass" = 'CANDIDATE' THEN INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress", "t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" BETWEEN "pStart" AND "pEnd" ORDER BY "t"."serialnumber" DESC ELSE IF "userClass" = 'AGENCY' THEN INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress", "t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid") AND "weekenddate" BETWEEN "pStart" AND "pEnd" ORDER BY "t"."serialnumber" DESC ELSE IF "userClass" = 'OWNER' THEN SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid"); INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress", "t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" WHERE "company"."divisionid" = "divid" AND "person"."divisionid" = "divid" ORDER BY "t"."serialnumber" DESC ELSE -- CLIENT SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitscharged"*"chargerate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress","t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "employment" AS "allemps" KEY JOIN "iqxnetuserlink" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid") AND "weekenddate" BETWEEN "pStart" AND "pEnd" ORDER BY "t"."serialnumber" DESC END IF END IF END IF; WITH "RowData" AS(SELECT "row_number"() OVER(ORDER BY "temptimesheetid" ASC) AS "RowNumber", "temptimesheetid","serialnumber","tempname","position","companyname","weekenddate","timesheettype","timesheettotal","companyaddress","theirref","InvoiceNo","ExternalStatus","ShiftList" FROM "DSet" WHERE("serialnumber" LIKE '%'+"SearchStr"+'%' OR "tempname" LIKE '%'+"SearchStr"+'%' OR "position" LIKE '%'+"SearchStr"+'%' OR "companyname" LIKE '%'+"SearchStr"+'%' OR "InvoiceNo" LIKE '%'+"SearchStr"+'%' OR "ShiftList" LIKE '%'+"SearchStr"+'%')) -- output full dataset without RowNumber filtered on RowNumber SELECT top "SampleSize" START at "RangeStart" "temptimesheetid","serialnumber","tempname","position","companyname","weekenddate","timesheettype",CAST("timesheettotal" AS DECIMAL(10,2)),"companyaddress","theirref","InvoiceNo","ExternalStatus","ShiftList",@@ROWCOUNT FROM "RowData" END GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheetsAllFilteredExtended" IS {CREATE PROCEDURE pears."NetTimesheetsAllFilteredExtended"( 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( "temptimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"weekenddate" DATE,"timesheettype" CHAR(1),"timesheettotal" CHAR(12),"companyaddress" CHAR(250),"theirref" CHAR(50),"InvoiceNo" CHAR(25),"ExternalStatus" INTEGER,"ShiftList" CHAR(500),"RowCount" INTEGER ) -- 20121114 force two decimal places on total -- 20121218 add Shift List to allow searching -- 20130219 derived from GenevaNetTimesheetsAllFiltered BEGIN DECLARE "userClass" CHAR(20); DECLARE @companycount INTEGER; DECLARE "divid" CHAR(20); DECLARE "pstart" DATE; DECLARE "pend" DATE; DECLARE "RangeStart" INTEGER; DECLARE "SearchStr" CHAR(100); DECLARE LOCAL TEMPORARY TABLE "DSet"( "temptimesheetid" CHAR(20) NULL, "serialnumber" CHAR(20) NULL, "tempname" CHAR(60) NULL, "position" CHAR(60) NULL, "companyname" CHAR(60) NULL, "weekenddate" DATE NULL, "timesheettype" CHAR(1) NULL, "timesheettotal" DECIMAL(10,2) NULL, "companyaddress" CHAR(250) NULL, "theirref" CHAR(50) NULL, "InvoiceNo" CHAR(25) NULL, "ExternalStatus" bit NULL, "ShiftList" CHAR(500) NULL, ) NOT transactional; SET "RangeStart" = (("pPageNo"-1)*"SampleSize"); SET "SearchStr" = "trim"("replace"("SearchString",' ','%')); IF "RangeStart" = 0 THEN SET "RangeStart" = 1 END IF; IF "RangeStart" > 1 THEN SET "RangeStart" = "RangeStart"+1 END IF; SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID"); SET "pStart" = "isnull"("iqxnetstringtodate"("pStartDate"),"dateadd"("week",-1,CURRENT DATE)); SET "pEnd" = "isnull"("iqxnetstringtodate"("pEndDate"),"dateadd"("week",1,CURRENT DATE)); IF "userClass" = 'CANDIDATE' THEN INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress", "t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" BETWEEN "pStart" AND "pEnd" ORDER BY "t"."serialnumber" DESC ELSE IF "userClass" = 'AGENCY' THEN INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress", "t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid") AND "weekenddate" BETWEEN "pStart" AND "pEnd" ORDER BY "t"."serialnumber" DESC ELSE IF "userClass" = 'OWNER' THEN SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid"); INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal",'' AS "CompanyAddress", "t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" WHERE "company"."divisionid" = "divid" AND "person"."divisionid" = "divid" ORDER BY "t"."serialnumber" DESC ELSE -- CLIENT SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); INSERT INTO "DSet" SELECT "t"."temptimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate", IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W' AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1 OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T' ELSE 'C' endif endif AS "timesheettype", (SELECT "sum"("unitscharged"*"chargerate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid") AS "timesheettotal", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress","t"."theirref", (SELECT FIRST "string"(' ',"iqacdocument"."ourref") FROM "iqacjournal" KEY JOIN "iqacdocument","iqacjournal" JOIN "temptimesheetline" ON "iqacjournal"."xref" = 'T' AND "iqacjournal"."xrefid" = "temptimesheetline"."temptimesheetlineid" WHERE "temptimesheetline"."temptimesheetid" = "t"."temptimesheetid") AS "InvoiceNo", "t"."ExternalStatus", (SELECT "replace"("list"("tp"."shiftserialnumber"),',',' ') FROM "tempshiftplan" AS "tp" KEY JOIN "tempshift" KEY JOIN "temptimesheet" AS "ts" WHERE "ts"."temptimesheetid" = "t"."temptimesheetid") AS "ShiftList" FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company" KEY JOIN "employment" AS "allemps" KEY JOIN "iqxnetuserlink" ,"temptimesheet" AS "t" KEY JOIN "tempdesk" ,"temptimesheet" AS "t" KEY JOIN "person" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid") AND "weekenddate" BETWEEN "pStart" AND "pEnd" ORDER BY "t"."serialnumber" DESC END IF END IF END IF; WITH "RowData" AS(SELECT "row_number"() OVER(ORDER BY "temptimesheetid" ASC) AS "RowNumber", "temptimesheetid","serialnumber","tempname","position","companyname","weekenddate","timesheettype","timesheettotal","companyaddress","theirref","InvoiceNo","ExternalStatus","ShiftList" FROM "DSet" WHERE("serialnumber" LIKE '%'+"SearchStr"+'%' OR "tempname" LIKE '%'+"SearchStr"+'%' OR "position" LIKE '%'+"SearchStr"+'%' OR "companyname" LIKE '%'+"SearchStr"+'%' OR "InvoiceNo" LIKE '%'+"SearchStr"+'%' OR "ShiftList" LIKE '%'+"SearchStr"+'%')) -- output full dataset without RowNumber filtered on RowNumber SELECT top "SampleSize" START at "RangeStart" "temptimesheetid","serialnumber","tempname","position","companyname","weekenddate","timesheettype",CAST("timesheettotal" AS DECIMAL(10,2)),"companyaddress","theirref","InvoiceNo","ExternalStatus","ShiftList",@@ROWCOUNT FROM "RowData" END }