Link to home
Start Free TrialLog in
Avatar of Judy Deo
Judy DeoFlag for United States of America

asked on

cfquery - finding records older than 24 hours with an exception (slight change to previous question)

i have the below query that gets records that have the value of 'Submitted By Customer' in the status field and the date in the RFQ_Date field is older than 24 hours.

<cfquery name="GetUnconfirmedLeads" datasource="dsname" dbtype="ODBC">    
     SELECT id FROM Sales_Leads
     WHERE status = 'Submitted By Customer' AND Now() > DateAdd('h', 24, RFQ_Date)          
</cfquery>

I am trying to put in an exception that says do the above, BUT don't count the time between 5 p.m FRIDAY PST and before 8 a.m MONDAY PST?

and by don't count i mean if a lead comes in at 3 p.m PST on friday, then on monday at 8 a.m, for our purposes only 2 hours have gone by. but in reality over
3 days have gone by.
Avatar of wytcom
wytcom

Build a function in the database:
============
CREATE FUNCTION addDaySkipWeekend(@date datetime)
RETURNS datetime
AS

DECLARE @nextDay datetime, @dayOfWeek int

SET @nextDay = DATEADD(d, 1, @date)
SET @dayOfWeek = DATEPART(dw, @nextDay)

IF @dayOfWeek = 7  SET @nextDay = DATEADD(d, 1, @date)
IF @dayOfWeek = 1  SET @nextDay = DATEADD(d, 1, @date)
 
RETURN @nextDay
============
Then change your query

<cfquery name="GetUnconfirmedLeads" datasource="dsname" dbtype="ODBC">    
     SELECT id FROM Sales_Leads
     WHERE status = 'Submitted By Customer' AND Now() > dbo.addDaySkipWeekend(RFQ_Date)          
</cfquery>

Note: the function is untested
ASKER CERTIFIED SOLUTION
Avatar of wytcom
wytcom

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial