SQL Help with Custom Fields

[citapinc]

SugarCRM CE 6.2.3

I'm trying to figure out an SQL Statement but I'm stuck. Any help would be appreciated.

I need an SQL statement that looks at a custom field called Move_Date_c which is defined as a Date field, finds all records where the move data is 30 days from today and the status is not "Dead" or "Converted".

I come from the old dBase days so I'm still having a hard time understanding JOINS and stuff. Any help in writing this SQL statement would be great.

Thanks.

[eggsurplus]

Haven't tested this (also guessing the module):

[PHP]
select *
from leads
inner join leads_cstm on leads.id = leads_cstm.id_c
where leads.status not in ('Dead','Converted')
and date_add(cast(now() as date), INTERVAL 30 DAY) = leads_cstm.move_date_c

[PHP]

[citapinc]

Haven't tested this (also guessing the module):

[PHP]
select *
from leads
inner join leads_cstm on leads.id = leads_cstm.id_c
where leads.status not in ('Dead','Converted')
and date_add(cast(now() as date), INTERVAL 30 DAY) = leads_cstm.move_date_c

[PHP]

I'll give that one a try.

I spent several hours last night working on this and this was the SQL Statement that I came up with:

SELECT a.id_c, a.move_date_c, b.* from leads_cstm as a, leads as b where a.move_date_c = date_add(curdate(), interval 1 month) and a.id_c = b.id and find_in_set(b.status, "Dead, Converted") = 0

[eggsurplus]

That looks fine at first glance. Good call on cur_date(). I should have used that instead.