x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 1 reply
  • Subscribers 74 subscribers
  • Views 224 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Tebasaki

SQL for checking Date

Posted By

I'm trying to create an escalation that checks on a date and returns if it's after three days not including Weekends. In the escalation for my trigger SQL I have

oppo_type = 'projectprebid' AND
oppo_deleted IS NULL AND
oppo_biddate IS NOT NULL AND
oppo_status = 'In Progress' AND
oppo_biddatepastdue = '0' AND
oppo_stage = 'nextstage' AND
oppo_biddate

But it's not quite working for the obvious reasons that the DATEADD implementation is wrong and it doesn't have the weekends added either.

oppo_type = 'projectprebid' This is they type of opportunity
oppo_deleted IS NULL check to make sure it's not deleted
oppo_biddate IS NOT NULL the field itself is not empty
oppo_status = 'In Progress' The status of the opportunity is still working
oppo_biddatepastdue = '0' Make sure this is the first time returning this opportunity (if all returns true then email will be sent to assigned user and this will change to 1)
oppo_stage = 'biddatestage' Ideally, the user will advance the workflow before the three days are up, if it's still the biddate stage then it will email saying "Update the opportuniry!"
oppo_biddate 's where it compares today (hopefully minus Saturday and Sunday with the biddate to return true

Any ideas?

  • 0

    Hi Nick,

    The approach I used in a similar situation was to create a custom view that listed the elapsed business days for each record. The key part was (adapted to be a simple example):

    select oppo_opportunityid, oppo_updateddate, GETDATE() AS 'Today',
    (DATEDIFF(dd, oppo_updateddate, GETDATE()) + 1)
    -(DATEDIFF(wk, oppo_updateddate, GETDATE()) * 2)
    -(CASE WHEN DATENAME(dw, oppo_updateddate) = 'Sunday' THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END) AS 'LastUpdate'
    from opportunity
    where oppo_deleted is null

    The output of the view should look something like:

    1 2014-11-07 08:30:00.000 2014-11-12 15:16:09.860 4
    2 2013-03-14 16:17:00.000 2014-11-12 15:16:09.860 435
    3 2013-03-14 16:17:00.000 2014-11-12 15:16:09.860 435

    Once that view is available to CRM, just reference it in your Escalation (instead of the Opportunity table) and set your trigger SQL clause to check the 'LastUpdate' derived field.

*Community Hub is the new name for Sage City