Quarterly reports based on Join Date (Membership Renewal Report)

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

  • 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

  • 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?