CODEWITHSUNDEEP
mysql
Nicolai Iversen
Nicolai Iversen

Reputation: 41

specify time period given interval in MySQL

I have the following tables:

enter image description here

In other words I have a set of customers I follow throughout time. I have a column of the date of their first purchase, and another column with another purchase they made at another time. I want to make a column which specifies the time period in which the specific purchase was made. The time periods should be defined such that a new period starts the 20th each month and the first period for each customer should therefore be the day they made their first purchase and until the 20th. This is illustrated below:

What I need:

enter image description here

I have tried to implement this via a handful of if statements like:

WHEN DAY(c.created)<21 and DAY(s.created)<21 and year(c.created)-year(s.created)=0
THEN MONTH(c.created)-MONTH(s.created)+1

WHEN DAY(c.created)>20 and DAY(s.created)<21 and year(c.created)-year(s.created)=0
THEN MONTH(c.created)-MONTH(s.created)+2

and so on. I want to know if there is an easy(er) and simple(r) way to handle this problem?

I work in MySQL Workbench 6.3 CE

Upvotes: 2

Views: 71

Answers (1)

O. Jones
O. Jones

Reputation: 108796

The date of the first day of the calendar month in which a customer made a purchase can be found like this:

 DATE_FORMAT(First_purchase , '%Y-%m-01')

So, the date of the first day of your fiscal month, which starts on the 20th of each calendar month, can be found like this.

DATE_FORMAT(First_purchase  - INTERVAL 19 DAY, '%Y-%m-01') + INTERVAL 19 DAY

Then, you can use TIMESTAMPDIFF as follows to get the number of months between two of these sorts of numbers.

TIMESTAMPDIFF(
         MONTH,
         DATE_FORMAT(First_purchase - INTERVAL 19 DAY, '%Y-%m-01') + INTERVAL 19 DAY,
         DATE_FORMAT(Date_created - INTERVAL 19 DAY, '%Y-%m-01') + INTERVAL 19 DAY) + 1

That will get you the number of the fiscal month, starting with First_purchase, in which Date_created is found, where the first fiscal month has the number 1.

Using native date arithmetic in place of DAY(), MONTH(), and YEAR() functions is much more likely to keep working over ends of years and in leap years and all that.

Upvotes: 2

Related Questions