Today I was asked about creating an opportunity group in SalesLogix that contained opportunities estimated to close this month.  This is a similar request to the previously posted creating a SalesLogix Group of Contacts with Activities Completed Today.

This query once again requires the use of an IN statement.  We know we are looking for Opportunities, so we will look for a specific list of OpportunityIDs in a list created/provided by us in a SQL statement.

So we will have SELECT * FROM OPPORTUNITY WHERE OPPORTUNITYID IN (**SQL statement that returns the list of opportunities we want.**).

Our SQL statement needs to return the OpportunityIDs of Opportunities estimated to close this month, so we need to find the first and last days of the month.  These two SQL statements provide these dates:

----First Day of Current Month

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)

----Last Day of Current Month

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)

In both of the above SQL statements we are taking todays date and adding or subtracting from it to get the desired day of the month.  That date is then converted into US standard date format, mm/dd/yyyy.

We then insert these into a SQL statement that compares these dates to each Opportunities Estimated Close Date and returns a list of OpportunityIDs:

SELECT OPPORTUNITYID FROM OPPORTUNITY WHERE ESTIMATEDCLOSE <= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101) AND ESTIMATEDCLSE >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)

We end up with this group:

SalesLogix Query Builder Est Close this Month
Posted in:

Start a Project With Us

Submit your email below to get in touch with our team.