24 June 2010

Counting the number of weeks in given period (MySQL)

Прочети статията на български

Few days ago a friend of mine told me that he need to count number of weeks in given period. Simple division by 7 of the number of days wasn't what he needed - the weeks had to be counted as number of Monday-Sunday fragments (in addition to any incomplete weeks at the start or the end of the period). I wrote a simple stored function to do the task:

`week_count`
  (
    StartDate DATETIME,
    EndDate DATETIME,
    CountIncompleteWeeks
  )
  RETURNS INT
StartDate and EndDate define the period to count weeks from. CountIncompleteWeeks, determines if incomplete weeks have to be counted (CountIncompleteWeeks=1) or discarded (CountIncompleteWeeks=0).

The function returns number of weeks (minimum of 1) or -1 in case of error (if StartDate or EndDate aren't of proper DATETIME format, or if StartDate is bigger than EndDate).

NOTE: Both dates count towards the number of days (so if both date values are equal, that means an interval of 1 day, resp. the result will be 1 week).

CREATE FUNCTION `week_count`
   (
      StartDate DATETIME,
      EndDate DATETIME,
      CountIncompleteWeeks INT
   )
   RETURNS INT
   DETERMINISTIC
   BEGIN
      DECLARE Days, Weeks, WDay INT;
      SET Weeks = 0;
      /* Get the number of days in the given period */
      SET Days = IFNULL(DATEDIFF(EndDate, StartDate) + 1, -1);
      IF Days < 1 THEN RETURN -1; END IF;

      /* If the start date isn't Monday, subtract this incomplete week from the
         total days number (and count as 1 week if PlusIncomplete > 0) */
      SET WDay = IF(DAYOFWEEK(StartDate) = 1, 7, DAYOFWEEK(StartDate) - 1);
      IF WDay > 1 THEN
         IF CountIncompleteWeeks > 0 THEN SET Weeks = 1; END IF;
         SET Days = Days - (8 - WDay);
         -- If after subtracting no days have left, return 1.
         IF Days < 1 THEN RETURN Weeks; END IF;
      END IF;

      /* If the end date isn't Sunday, subtract this incomplete week from the
         total days number (and count as 1 week if PlusIncomplete > 0) */
      SET WDay = if(DAYOFWEEK(EndDate) = 1, 7, DAYOFWEEK(EndDate) - 1);
      IF WDay < 7
      THEN
         IF CountIncompleteWeeks > 0 THEN SET Weeks = Weeks + 1;
         SET Days = Days - WDay;

         /* If after subtracting there are no more days, return the number of weeks
            counted so far */
         IF Days < 1 THEN RETURN Weeks; END IF;
      END IF;

      /* Divide the remaining days to weeks, add any incomplete one,
         then return the result. */
      RETURN Weeks + (Days / 7);
   END;

I'd welcome any comments (if someone ever reads this post, of course) about this function. Please feel free to criticize or to propose better variants.

No comments:

Post a Comment