pears.NetOwnerDiaryCount

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