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.
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
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 1April-June returns 2July-Sep returns 3Oct-Dec returns 4
Does that make sense?