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