-2

I have an up to date mysql database installation and I need a function to calculate the sales between the nth of the last month and today. The function will be called several times per day because I'm looking for the point at which the cumulative sales since the previous nth of the month cross a threshold.

The background is that I am developing a subscription site. Customers will sign up on ad-hoc days of the year and make purchases throughout the year. I want to be able to calculate the sales on the month-to-date basis.

If a customer signs up on the nth of the month then I need to calculate the sales between the previous nth of the month and today.

If the customer signs up on the 28th and today is the 30th then you'd think that there had only been 2 days of sales but if today is 30th March then it's been 30 days. In another example: if the customer signs up on Oct 31st how would this handle Feb 28th or April 30th?

I've looked at functions such as timestampdiff() but can not figure out a practical solution. By that I mean one that will do the job and can do the task efficiently without costing toooo many cpu cycles.

Thanks in anticipation

argyrg
  • 47
  • 6

3 Answers3

0

You might be looking for something like :

DATE_ADD(
    LAST_DAY(DATE_SUB(CURDATE(), interval 2 MONTH)), 
    INTERVAL 15 DAY
)

Given a number of days N, it returns the Nth day of last month.

As of today, when given 15 days, this yields '2018-12-15' (the 15th day of last month, eg December 15th, 2018).

If you want to ensure that the returned date will never exceed the last day of last month (like : current month is March and N = 31), you can use :

LEAST(
   DATE_ADD(
       LAST_DAY(DATE_SUB(CURDATE(), interval 2 MONTH)), 
       INTERVAL 15 DAY),
   LAST_DAY(DATE_SUB('CURDATE(), interval 1 MONTH))
)

Typically, this :

SELECT LEAST(
   DATE_ADD(
       LAST_DAY(DATE_SUB('2018-03-28', interval 2 MONTH)), 
       INTERVAL 31 DAY),
   LAST_DAY(DATE_SUB('2018-03-28', interval 1 MONTH))
)

Yieds : '2018-02-28' -(the last day of February).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • I like the look of this but I wonder how it behaves when the interval is set to 31 and the previous month doesn't stretch that far? (Feb, Apr, Jun etc...) – argyrg Jan 06 '19 at 14:25
  • Well, 31 days after February 1st is March 3rd, and that's what the query will return in that case... `SELECT DATE_ADD(LAST_DAY(DATE_SUB('2018-03-28', interval 2 MONTH)), INTERVAL 31 DAY)` – GMB Jan 06 '19 at 14:29
  • What do you want to see in this use case : February 28th ? – GMB Jan 06 '19 at 14:31
  • Hi GMB I don't really want to set an interval I want to read the dayofmonth from the record created when the user started their subscription. Let's say that they started their subscription on the 31st of some month. when I get to the 30th of some months everything will be fine, but the next day there's a problem if the next day is the 1st – argyrg Jan 06 '19 at 14:44
  • The day of month that I read from the subscription date record is fixed so I have to work with that number and then work out how to deal with the intricacies of the calendar – argyrg Jan 06 '19 at 14:45
0

I could recommend TIMESTAMP and INTERVAL. It is simple and efficient without the need to try to do something trivial enough

So consider the following schema and query

DROP TABLE IF EXISTS `example_sales`;
CREATE TABLE `example_sales`(
  `id` INT(11) UNSIGNED AUTO_INCREMENT,
  `id_customer` MEDIUMINT(8) UNSIGNED NOT NULL,
  `profits` DECIMAL(16,2) NOT NULL DEFAULT 0,
  `ts` TIMESTAMP NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- add some values, it doesn't matter the order in the example
INSERT INTO `example_sales`( `id_customer`, `profits`, `ts` ) VALUES
  ( 1, 10.00, NOW( ) - INTERVAL 12 DAY ),
  ( 1, 14.00, NOW( ) - INTERVAL 1 WEEK ),
  ( 1, 110.00, NOW( ) - INTERVAL 30 DAY ),
  ( 1, 153.00, NOW( ) - INTERVAL 8 DAY ),
  ( 1, 5.00, NOW( ) - INTERVAL 2 DAY ),
  ( 1, 97.00, NOW( ) - INTERVAL 13 DAY ),
  ( 1, 1.00, '2018-02-28 13:00:00' ),
  ( 1, 2.00, '2018-03-28 13:00:00' ),
  ( 1, 3.00, '2018-01-30 13:00:00' ),
  ( 1, 4.00, '2018-03-30 13:00:00' ),
  ( 1, 42.00, NOW( ) - INTERVAL 42 DAY );

Updated, since I've originally didn't fully understand the question. I've kept the timestamp though and made some corrections I didn't noticed earlier.

-- '2018-03-28' or '2018-03-29' instead of NOW( ) or anything you like
SET @last  := DATE( NOW( ) ); 
SET @first := LEAST( DATE_SUB( @last, INTERVAL 1 MONTH ), LAST_DAY( DATE_SUB( @last, INTERVAL 1 MONTH ) ) );

-- change last and first test different sets
SELECT `profits`, DATE( `ts` ) AS `date`
  FROM `example_sales`   
  WHERE `id_customer` = 1
 HAVING `date` BETWEEN @first AND @last 
 ORDER BY `date`;

And when you are confident enough that this will do the job

SELECT SUM( `profits` ), DATE( `ts` ) AS `date`
  FROM `example_sales`   
  WHERE `id_customer` = 1
 HAVING `date` BETWEEN @first AND @last;

Hope that this will do the trick this time.

gmastro
  • 126
  • 3
  • Thank you for this but unfortunately it doesn't address the key challenge. This calculates the past month's sales but I need it to calculate the sales since the Nth day of the previous month where N can be a number which doesn't exist in the previous month. For example: If N = 30 and today = March 29th then it needs to calculate from Feb 28th (or 29th) to today. – argyrg Jan 06 '19 at 14:31
0

Thanks to both of you for your contributions. I'd already spent several hours looking into the issue and some of the most promising answers came from stackoverflow which is why I thought to post my question here.

I agree that the question is ill formed having spent quite a bit more time trying to define the problem. As a consequence I'm going to change the problem into something which an be easily resolved.

Rather than rely on the subscription date I'm going to run on a calendar month basis. This forces changes elsewhere but I think I can live with them.

Thanks again

argyrg
  • 47
  • 6