CODEWITHSUNDEEP
mysqlsqldatediffdate-difference
czmudzin
czmudzin

Reputation: 299

MySQL: Calculate Remaining Selling Days in Month

I have a daily revenue goal calculation that states how much revenue I need to earn daily to achieve the monthly revenue goal:

'Revenue_Goal' / 'Selling_Days'

(Note: 'Selling_Days' is a whole already in the dataset.)

What I'm trying to accomplish is getting an adjusted daily goal based on how many days have passed. I PARTIALLY accomplished this with:

('Revenue_Goal' - SUM('Revenue')) / DATEDIFF(MAX('Date'),NOW())

However, the part that I can't figure out is only calculating selling days in the adjusted formula. I need to use the formula above (or something similar) that only counts days when:

So, if today is 3/25/16, my DATEDIFF calculation would return 6, but my goal is to get it to return 4.5.

Any help is appreciated!

Upvotes: 1

Views: 586

Answers (2)

Bampfer
Bampfer

Reputation: 2220

It makes sense to write this as a stored function. I can think of three methods, each improving on the previous (though method 2 is probably good enough.)

Method 1: Start at first date, increment until second date. For each date, call DAYOFWEEK(). If Sunday then add 0, if Saturday add 0.5, otherwise add 1.

DROP FUNCTION IF EXISTS `count_days_goal`;

DELIMITER $$
CREATE FUNCTION `count_days_goal`(start_date DATE, end_date DATE) RETURNS FLOAT
    DETERMINISTIC
BEGIN
    DECLARE sell_days FLOAT;
    DECLARE dow INT;

    SET sell_days = 0.0;
    WHILE start_date <= end_date DO
        SET dow = DAYOFWEEK(start_date);
        IF (dow > 1) THEN 
            IF (dow = 7) THEN SET sell_days = sell_days + 0.5;
            ELSE SET sell_days = sell_days + 1.0;
            END IF;
        END IF;
        SET start_date = start_date + INTERVAL 1 DAY;
    END WHILE;

    RETURN (sell_days);
END$$
DELIMITER ;

A couple tests (note that my implementation includes the last day in the counted days):

select count_days_goal(DATE('2016-03-21'), DATE('2016-03-27'));
select count_days_goal(DATE('2016-03-01'), DATE('2016-03-27'));

This is straightforward but it's inefficient. It takes longer to run the further apart the days are. Mind you, one call will still be faster than an eyeblink, but it would be slightly offensive to most developers. Fortunately we can do better.

Method 2: Each full week is 5.5 selling days. So divide the number of days between the dates by 7, ignore the remainder for a moment, and multiple by 5.5.

Example: 32 days is (4 weeks + 4 days), which is 22 selling days plus 4 remainder.

That remainder is where things get tricky. Those 4 days might include a Saturday, or they might not. The easiest solution is to use method 1 for those last few days. It will never loop over more than 6 days so it's fast.

So now the code looks like this:

DROP FUNCTION IF EXISTS `count_days_goal`;
DELIMITER $$
CREATE FUNCTION `count_days_goal`(start_date DATE, end_date DATE) RETURNS FLOAT
    DETERMINISTIC
BEGIN
    DECLARE sell_days FLOAT;
    DECLARE dow INT;
    DECLARE whole_weeks INT;

    SET whole_weeks = FLOOR(DATEDIFF(end_date, start_date)/7);
    SET start_date = start_date + (7 * whole_weeks);
    SET sell_days = whole_weeks * 5.5;

    WHILE start_date <= end_date DO
        SET dow = DAYOFWEEK(start_date);
        IF (dow > 1) THEN 
            IF (dow = 7) THEN SET sell_days = sell_days + 0.5;
            ELSE SET sell_days = sell_days + 1.0;
            END IF;
        END IF;
        SET start_date = start_date + INTERVAL 1 DAY;
    END WHILE;

    RETURN (sell_days);
END$$
DELIMITER ;

Method 3: You can also use method 2 but rid of the loop entirely. To see how it might be done, it helps to have a calendar in front of you. (I didn't bother implementing this one; method 2 is quite good.)

Instead of looping, call DAYOFWEEK on the first and last dates. Let's call those dow_start and dow_end.

If dow_start < dow_end then those pesky remainder days all lie inside the week, they don't wrap around from Saturday back to Sunday. So all you need to do is:

SET answer = DATEDIFF(dow_end, dow_start) + 1;
IF dow_start = 1 (i.e. Sunday) THEN subtract 1 from answer
IF dow_end is 7 (i.e. Saturday) THEN subtract 0.5 from answer

If dow_start > dow_end then those pesky remainder days do wrap around. I believe in that case you can swap dow_start and dow_end (returning us to the previous case). Compute it as before, but then subtract the answer from 5.5 and add 1. (Explanation: count the days of week that are NOT between day_start and day_end, then subtract them from a week's total.)

Like I said I didn't write or test that one, so it may not be completely correct.

How to handle bank holidays: If you have them in a table you can simply COUNT the number of rows between the two dates. Subtract that from your answer. Easy! (Assuming the bank holidays are never on a weekend.) There are websites that list bank holidays for various countries, even one that will generate a list of dates for you far into the future, making it easy to build a holiday table.

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

This one of n ways to calc this

SELECT
  SUM( 
    ELT( DAYOFWEEK(CONCAT( DATE_FORMAT(now(), '%Y-%m-'),nr)),
    '0','1','1','1','1','1','0.5') ) calc_days
FROM (
SELECT d2.a*10+d1.a  AS nr FROM (
  SELECT 0  a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
  UNION ALL SELECT 
  5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8  UNION ALL SELECT 9) AS d1
  CROSS JOIN (
  SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  ) AS d2
) AS x
WHERE nr BETWEEN  DAY(NOW()) AND DAY(LAST_DAY(NOW()))
ORDER BY nr;

Upvotes: 2

Related Questions