====== pears.NetTimesheetsAllFilteredExtended ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
}
</code>