Judy Deo
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.
<cfquery name="GetUnconfirmedLeads"
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
============
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"
SELECT id FROM Sales_Leads
WHERE status = 'Submitted By Customer' AND Now() > dbo.addDaySkipWeekend(RFQ_
</cfquery>
Note: the function is untested