Geek Answers Handbook

MySql Select an entry up to x days each month

I have a tbl_subscriptions table and columns like this "id, user_name, join_date (date)", I want to select users up to 7 days each month based on join_date so that I can send them notifications to continue subscribing next month. I have entries 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 2014-07-28, then I want to get records 1 and 2. I tried the following query

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

The problem with the above sql is that it selects the record of only the current month, PLZ offers the best query.

+4
source share
2 answers

Does it 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:

. , . :

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;

- SQL.

+1

, , , . ( ) . .

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)

( ) → http://sqlfiddle.com/#!2/3cc4f

0

More articles:


All Articles