Quarterly reports based on Join Date (Membership Renewal Report)

Question

Hi. 
 I have a request from a new Sales member of our team to show on their CRM dashboard when an existing member is up for renewal. We renew a member every year (so yearly) - the month of their join date (aka startdate) is what triggers the invoice. 
 She also wishes to see it 3 months prior (quarterly) to the renewal date in order to send a quick reminder message to the member. 
 I'm hoping someone can direct me on how to create this report (search query) correctly. I'm pulling on the Company Summary - I selected the necessary fields for the report. 
 However, I get confused on the search (join date field). 
 
 I'm not 100% sure what to use here - relative vs for any - and when I select for any, it wants to use a between. I think I need to use relative - then choose quarter - but not sure about current vs next - I assume current is Jan though March - and next would be March through Jun. 
 Just curious if there is any "can" report I can use to create this for her. 
 Let me know if anyone has any other approach - maybe we should be using a different field to work this quarterly report better. 
 Thanks, Stephane

Sage CRM · Accepted Answer

Stephane Ah.... Sean got in ahead of me. A report in Sage CRM that uses a relative quarter is using standard SQL functions DatePart and DateAdd. So for example Opportunities that are closing this quarter will generate a SQL that looks like SELECT oppo_stage,oppo_description,comp_name,oppo_forecast_CID,oppo_forecast,oppo_targetclose,pers_fullname,pers_phonefullnumber FROM vReportOpportunity WITH (NOLOCK) WHERE ((oppo_secterr is null OR (oppo_secterr>=-1610612729 AND oppo_secterr<=-1342177275) OR (oppo_secterr>=-2147483639 AND oppo_secterr<=-1879048185) OR (oppo_AssignedUserId=4) OR (oppo_ChannelId=2) OR (oppo_ChannelId=3) OR (oppo_ChannelId=4) OR (oppo_ChannelId=5) OR (oppo_ChannelId=1) OR (oppo_CreatedBy=4)) AND (comp_secterr is null OR (comp_CreatedBy=4) OR (comp_ChannelId=2) OR (comp_ChannelId=3) OR (comp_ChannelId=4) OR (comp_ChannelId=5) OR (comp_ChannelId=1) OR (Comp_PrimaryUserId=4) OR (comp_secterr>=-2147483639 AND comp_secterr<=-1879048185) OR (comp_secterr>=-1610612729 AND comp_secterr<=-1342177275)) AND (pers_secterr is null OR (pers_CreatedBy=4) OR (pers_ChannelId=2) OR (pers_ChannelId=3) OR (pers_ChannelId=4) OR (pers_ChannelId=5) OR (pers_ChannelId=1) OR (Pers_PrimaryUserId=4) OR (pers_secterr>=-2147483639 AND pers_secterr<=-1879048185) OR (pers_secterr>=-1610612729 AND pers_secterr<=-1342177275))) AND ( (DATEPART(QQ, oppo_targetclose) = DATEPART(QQ, DATEADD(QQ, 0, GETDATE())) AND DATEPART(YY, oppo_targetclose) = DATEPART(YY, DATEADD(QQ, 0, GETDATE()))) )ORDER BY oppo_stage,oppo_forecast,oppo_description ASC,comp_name ASC,oppo_targetclose ASC,pers_fullname ASC,pers_phonefullnumber ASC The DATEPART function returns an integer representing the specified datepart of the specified date. January-March returns 1 April-June returns 2 July-Sep returns 3 Oct-Dec returns 4 Does that make sense?

Sean from Spire · Answer

Hi Stephane, Looks like you don't store a renewal date or renewal month, so using the relative date parameter for current month or quarter on the join date wont return what you need. 
 I would create a derived column in a new view. 
 This is just a quick example, probably not the most efficient, but I haven't enough coffee yet. It should see you on the way though. 
 SELECT *, DATENAME ( month , GETDATE ()) AS 'Current Month Name' , DATENAME ( month , comp_c_yourjoinfield ) AS 'Join Month Name' , MONTH ( GETDATE ()) AS 'Current Month Number' , MONTH ( comp_c_yourjoinfield ) AS 'Join Month Number' from vReportCompany Where MONTH ( comp_c_yourjoinfield ) = MONTH ( GETDATE ()) OR MONTH ( comp_c_yourjoinfield ) = MONTH ( GETDATE ())+ 1 OR MONTH ( comp_c_yourjoinfield ) = MONTH ( GETDATE ())+ 2 
 Regards, Sean

Sage CRM

Quarterly reports based on Join Date (Membership Renewal Report)

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING