====== pears.NetOwnerDashboardDiaryCountWeek ======
<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"."NetOwnerDashboardDiaryCountWeek"( in "pWebUserID" char(20) ) 
result( "thisweeksevents" char(5) ) 
begin
  select "COUNT"() as "thisweeksevents"
    from "diary" as "D"
      key join "staff" as "S"
      left outer join "iqxnetuser" as "U"
    where "U"."iqxnetuserid" = "pWebUserID"
    and "dateformat"("d"."diaryfrom",'yyyy-mm-dd') >= "dateformat"((select "dateadd"("dd",("datepart"("caldayofweek","today"())-1)*-1,"today"())),'yyyy-mm-dd')
    and "dateformat"("d"."diaryto",'yyyy-mm-dd') <= "dateformat"((select "dateadd"("dd",(7-"datepart"("caldayofweek","today"())),"today"())),'yyyy-mm-dd')
end /* DOC
2017-03-23 | counts the number of events this week
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerDashboardDiaryCountWeek" IS 
{create PROCEDURE pears."NetOwnerDashboardDiaryCountWeek"(in pWebUserID char(20))
RESULT(thisweeksevents char(5))
BEGIN
    SELECT 
        COUNT(*) as thisweeksevents
    FROM
        diary D 
        KEY JOIN staff S
        LEFT JOIN iqxnetuser U
    WHERE
        U.iqxnetuserid=pWebUserID
    AND
        dateformat(d.diaryfrom, 'yyyy-mm-dd') >= dateformat((select dateadd(dd,(datepart (caldayofweek,today(*))-1)*-1,today(*))), 'yyyy-mm-dd')
    AND
        dateformat(d.diaryto, 'yyyy-mm-dd') <= dateformat((select dateadd(dd,(7-datepart (caldayofweek,today(*))),today(*))), 'yyyy-mm-dd')
END

/* DOC
    2017-03-23 | counts the number of events this week
*/
}
</code>