CODEWITHSUNDEEP
mysqlsqljoin
Nicholas Koskowski
Nicholas Koskowski

Reputation: 849

Joining Dates to find Active Users

I have created a system to store my active accounts based on the date ranges of their last transaction and the next date that they will be set to be billed on.

I'm selecting the relevant data using the following.

SELECT * FROM `ss_usermeta` WHERE `meta_key` = 'last_trans_date';
SELECT * FROM `ss_usermeta` WHERE `meta_key` = 'next_recurring_date';

My objective is Query all of the accounts that are active between todays date or given date.

An example row for the last_transaction_date

umeta_id | user_id | meta_key | meta_value
901891   | 7886    | last_trans_date | 11/4/15

An example row for the next_recurring_date

umeta_id | user_id | meta_key | meta_value
901291   | 7886    | next_recurring_date | 12/4/15

An Active account will be one where todays date is between the last_trans_date and the next_recurring_date.

Upvotes: 0

Views: 71

Answers (1)

Hogan
Hogan

Reputation: 70523

I think you want last_trans_date to be less than active and next_recurring_date greater than active -- I'd do it like this 

 SELECT last.user_id
 FROM ss_usermeta as last 
 JOIN ss_usermeta as next on last.user_id = next.user_id
 WHERE last.meta_key = 'last_trans_date' and cast(last.meta_value as date) <= CURDATE()
   and next.meta_key = 'next_recurring_date' and cast(next.meta_value as date) >= CURDATE()

Upvotes: 1

Related Questions