pears.NetOwnerDashboardDiaryCountWeek

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
*/
}