CODEWITHSUNDEEP
mysqlsql
Mohd Shahid
Mohd Shahid

Reputation: 1606

MySql Select record before x days every month

I have a table tbl_subscriptions and columns like this "id, user_name, join_date(date)", I want to select the users before 7 days every month based on join_date so that I can send them notifications to continue their subscription for the next month. I have records like this

1, user1, 2014-05-02
2, user2, 2014-05-04
3, user3, 2014-06-12
4, user4, 2014-06-20
4, user5, 2014-07-24

If today is 2014-07-28, then I want to get records 1 and 2. I tried below query

SELECT  *, 
        datediff( date_format(date, '2014-07-%d'), now() ) as daysLeft 
FROM    tbl_subscriptions 
HAVING  daysLeft >= 0 
    AND daysLeft < 7

the problem with above sql is that it is selecting the record of the current month only, plz suggest any better query.

Upvotes: 4

Views: 878

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Does this do what you want?

SELECT s.*, datediff(date, curdate()) as daysLeft
FROM tbl_subscriptions s
WHERE date >= curdate() and date < curdate() + interval 7 day;

EDIT:

I see. These are recurrent subscriptions and you want to find the next ones. The following logic should work:

select s.*,
       (case when day(date) >= day('2014-07-28')
             then day(date) - day('2014-07-28')
             else day(date) + day(last_day('2014-07-28')) - day('2014-07-28')
        end) as diff
from tbl_subscriptions s
having diff <= 7;

Here is the SQL Fiddle.

Upvotes: 1

Anuj Patel
Anuj Patel

Reputation: 17859

Ok, first of all I dont know what is subscription renewal period. And idea of only checking date (and not the whole period) doesnt make sense to me. But this will get you your required output.

SELECT *, 
       day(date) days, 
       day(last_day('2014-07-28')) as lastday, 
       day('2014-07-28') today, day(last_day('2014-07-28'))-day('2014-07-28') as diff
FROM tbl_subscriptions
having days <= (7-diff) or (days > today and days <= today+7)

And here goes the demo (schema thanks to one of the deleted answer) -> http://sqlfiddle.com/#!2/3cc4f

Upvotes: 0

Related Questions