CODEWITHSUNDEEP
mysqlsql
Shaonline
Shaonline

Reputation: 1637

How to write sql query for the following

table - fu_plan_user (id_plan_user,id_user, id_plan, valid_from, valid_to, ...)
table - fu_user (id_user, name, company....)
table - fu_plan (id_plan, name, duration, ...)

This is the table structure somewhat.

I want to produce a list of accounts that have a close(valid_to) date which is the end of a month(30th day) and this date should be in the future, like grater than todays date (NOW()). The name of the company should not contain "trial" word.

The result should contain the following fields 

id_user, name (from table fu_plan), company (from table fu_user), valid_to (from table fu_plan_user)

Something like this Raw Query (not correct)

SELECT usr.id_user, payplan.name, usr.foretag, planUser.da_valid_to
from fu_plan_user planUser, fu_user usr, fu_plan payplan left join
     fu_user usr
     on planUser.id_user=usr.id_user
 where planUser.da_valid_to > now() and
       planUser.da_valid_to >= DATEADD(d, -30, getdate()) ;

Upvotes: 0

Views: 79

Answers (1)

luisurrutia
luisurrutia

Reputation: 586

After your explanation and only if your database is MySQL, i suggest to use this query:

SELECT user.id_user, plan.name, user.company, pbyu.valid_to 
FROM fu_plan_user AS fbyu
    JOIN fu_user AS user
        ON user.id_user = fbyu.id_user 
    JOIN fu_plan AS plan
        ON plan.id_plan = fbyu.id_plan
WHERE pbyu.valid_to > NOW()
    AND LAST_DAY(pbyu.valid_to) = pbyu.valid_to
    AND  user.company NOT LIKE '%trial%';

If company isn't only lowercase values, you should use LOWER(). LAST_DAY() function will get the last day from month of valid_to date.

Upvotes: 1

Related Questions