====== pears.NetSearchDateOffsetValues ======
<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"."NetSearchDateOffsetValues"( in "pWebUserID" char(20),in "pGroupID" char(10),in "pFormName" char(25) ) 
result( "StartDate" char(10),"EndDate" char(10) ) 
-- 20121109 look up default dates for searches on IQXNet.
-- 20130201 convert from Geneva to Standard Extended
begin
  declare "TagString" char(2);
  declare "StartOffset" char(25);
  declare "EndOffset" char(25);
  declare "StartDefault" integer;
  declare "EndDefault" integer;
  declare "StartUnit" char(2);
  declare "EndUnit" char(2);
  -- defaults in case agency questions not set
  set "StartDefault" = 31;
  set "EndDefault" = 7;
  set "StartUnit" = 'd';
  set "EndUnit" = 'd';
  set "TagString" = "pGroupID"+"pFormName";
  --look up value in question. d7= seven days, m1= one month. 
  select "textvalue" into "StartOffset" from "tagvalue" where "tagid" = "TagString"+'S' and "taglocation" = 'X';
  select "textvalue" into "EndOffset" from "tagvalue" where "tagid" = "TagString"+'E' and "taglocation" = 'X';
  if "StartOffset" is not null then
    set "StartUnit" = "substring"("StartOffset",1,1);
    set "StartDefault" = "substring"("StartOffset",2,5)
  end if;
  if "EndOffset" is not null then
    set "EndUnit" = "substring"("EndOffset",1,1);
    set "EndDefault" = "substring"("EndOffset",2,5)
  end if;
  if "StartUnit" = 'd' then
    select "dateformat"("dateadd"("dd",-"StartDefault","today"()),'dd/mm/yyyy') into "StartOffset" from "dummy"
  end if;
  if "StartUnit" = 'm' then
    select "dateformat"("dateadd"("mm",-"StartDefault","today"()),'dd/mm/yyyy') into "StartOffset" from "dummy"
  end if;
  if "StartUnit" = 'b' then
    select "string"('01/',"month"("today"()),'/',"year"("today"())) into "StartOffset" from "dummy"
  end if;
  if "StartUnit" = 'w' then
    select "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+2,"today"()),'dd/mm/yyyy') into "StartOffset" from "dummy"
  end if;
  if "EndUnit" = 'd' then
    select "dateformat"("dateadd"("dd","EndDefault","today"()),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  if "EndUnit" = 'm' then
    select "dateformat"("dateadd"("mm","EndDefault","today"()),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  if "EndUnit" = 'e' then
    select "dateformat"("dateadd"("day",-1,cast("dateformat"("dateadd"("month",1,"now"()),'yyyy-mm')+'-01' as date)),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  if "EndUnit" = 'w' then
    select "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+8,"today"()),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  select "StartOffset","EndOffset"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetSearchDateOffsetValues" IS 
{create procedure pears."NetSearchDateOffsetValues"( in "pWebUserID" char(20),in "pGroupID" char(10),in "pFormName" char(25) ) 
result( "StartDate" char(10),"EndDate" char(10) ) 
-- 20121109 look up default dates for searches on IQXNet.
-- 20130201 convert from Geneva to Standard Extended
begin
  declare "TagString" char(2);
  declare "StartOffset" char(25);
  declare "EndOffset" char(25);
  declare "StartDefault" integer;
  declare "EndDefault" integer;
  declare "StartUnit" char(2);
  declare "EndUnit" char(2);
  -- defaults in case agency questions not set
  set "StartDefault" = 31;
  set "EndDefault" = 7;
  set "StartUnit" = 'd';
  set "EndUnit" = 'd';
  set "TagString" = "pGroupID"+"pFormName";
  --look up value in question. d7= seven days, m1= one month. 
  select "textvalue" into "StartOffset" from "tagvalue" where "tagid" = "TagString"+'S' and "taglocation" = 'X';
  select "textvalue" into "EndOffset" from "tagvalue" where "tagid" = "TagString"+'E' and "taglocation" = 'X';
  if "StartOffset" is not null then
    set "StartUnit" = "substring"("StartOffset",1,1);
    set "StartDefault" = "substring"("StartOffset",2,5)
  end if;
  if "EndOffset" is not null then
    set "EndUnit" = "substring"("EndOffset",1,1);
    set "EndDefault" = "substring"("EndOffset",2,5)
  end if;
  if "StartUnit" = 'd' then
    select "dateformat"("dateadd"("dd",-"StartDefault","today"()),'dd/mm/yyyy') into "StartOffset" from "dummy"
  end if;
  if "StartUnit" = 'm' then
    select "dateformat"("dateadd"("mm",-"StartDefault","today"()),'dd/mm/yyyy') into "StartOffset" from "dummy"
  end if;
  if "StartUnit" = 'b' then
    select "string"('01/',"month"("today"()),'/',"year"("today"())) into "StartOffset" from "dummy"
  end if;
  if "StartUnit" = 'w' then
    select "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+2,"today"()),'dd/mm/yyyy') into "StartOffset" from "dummy"
  end if;
  if "EndUnit" = 'd' then
    select "dateformat"("dateadd"("dd","EndDefault","today"()),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  if "EndUnit" = 'm' then
    select "dateformat"("dateadd"("mm","EndDefault","today"()),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  if "EndUnit" = 'e' then
    select "dateformat"("dateadd"("day",-1,cast("dateformat"("dateadd"("month",1,"now"()),'yyyy-mm')+'-01' as date)),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  if "EndUnit" = 'w' then
    select "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+8,"today"()),'dd/mm/yyyy') into "EndOffset" from "dummy"
  end if;
  select "StartOffset","EndOffset"
end
}
</code>