====== pears.NetOwnerDiaryCount ======
<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"."NetOwnerDiaryCount"( 
  in "pWebUserID" char(20),
  in "pConsultantID" char(20) default null,
  in "pStartDate" char(30) default null,
  in "pEndDate" char(30) default null ) 
result( 
  "count" integer ) 
begin
  declare "xStartDate" timestamp;
  declare "xEndDate" timestamp;
  declare "WhoseDiary" char(20);
  set "xStartDate" = "isnull"("NetStringToTimestamp"("pStartDate"),current date-7);
  set "xEndDate" = "isnull"("NetStringToTimestamp"("pEndDate"),current date+30);
  set "WhoseDiary" = "isnull"("pConsultantID",(select top 1 "staffid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID" order by "loginid" asc));
  select "count"()
    from "diary" as "d"
    where "d"."staffid" = "WhoseDiary"
    and "d"."diaryfrom" <= "xEndDate"
    and "isNull"("d"."diaryto","d"."diaryfrom") >= "xStartDate"
end /* DOC
2018-08-08 ET created procidure to return total number of items in diary for a specific date range
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerDiaryCount" IS 
{create PROCEDURE pears."NetOwnerDiaryCount"(
    in pWebUserID char(20),
    in pConsultantID char(20) default null,
    in pStartDate char(30) default null,
    in pEndDate char(30) default null
)

RESULT( count INTEGER  )
BEGIN
    declare xStartDate timestamp;
    declare xEndDate timestamp;
    declare WhoseDiary char(20);

    set xStartDate=isnull(NetStringToTimestamp(pStartDate), current date - 7);
    set xEndDate=isnull(NetStringToTimestamp(pEndDate), current date + 30);
    set WhoseDiary=isnull(pConsultantID,(select top 1 staffid from iqxnetuser where iqxnetuserid=pWebUserID order by loginid));

SELECT count(*)

from
   diary d
where
   d.staffid=WhoseDiary
   and d.diaryfrom <= xEndDate
   and isNull(d.diaryto, d.diaryfrom) >= xStartDate

END

/* DOC
2018-08-08 ET created procidure to return total number of items in diary for a specific date range
*/
}
</code>