Skip to main content
S
seasonedweb
Inspiring
May 18, 2014
Answered

Elapsed time in business days?

  • May 18, 2014
  • 2 replies
  • 1283 views

Greetings

I am trying to get query results that would show elapsed time in business days. I have been so far unsuccessful in getting the elapsed days at all.

An appraiser's clock starts ticking when an order is entered in the system (order_create_date). The clock stops for the appraiser when an order is completed (order_complete_date) and the turn-around time to the client is (order_report_sent_date).

I attempted:

<cfquery name="get_client_tat" datasource="#Request.BaseDSN#">

SELECT order_ID, order_create_date,

      DateDiff("d", order_create_date, order_report_sent_date) AS client_tat

   FROM main_orders

   WHERE order_ID = #list_orders_all.order_ID#

</cfquery>

<cfquery name="get_appraiser_tat" datasource="#Request.BaseDSN#">

SELECT order_ID, order_create_date,

      DateDiff("d", order_create_date, order_complete_date) AS appraiser_tat

   FROM main_orders

   WHERE order_ID = #list_orders_all.order_ID#

</cfquery>

I simply get null results in

<cfloop query="list_orders_all">

#get_appraiser_tat.appraiser_tat# ,

#get_client_tat.client_tat#</cfloop>

Would it be better to <cfset get_appraiser_tat = #DateDiff('d', DateA, DateB)#> etc. or in the SQL statement, and how would one count business days only?

Any help would be greatly appreciated.

Norman

This topic has been closed for replies.
Correct answer BKBK

BKBK:

I hope you meant the variable dump screenshot....


The following is more complete

<cfif isDate(order_create_date) and isDate(order_report_sent_date)>

        <cfset dt1=parseDateTime(order_create_date)>

        <cfset dt2=parseDateTime(order_report_sent_date)>

        <tr><td>#orderID#</td><td>#businessDaysBetween(dt1,dt2)#</td></tr>

<cfelse>

      <tr><td>#orderID#</td><td>(Order_create_date or order_report_sent_date is not a date)</td></tr>

</cfif>

2 replies

I
ion
Inspiring
May 19, 2014

you're probably better off with a sql function like:

CREATE FUNCTION dbo.GETWEEKDAYS

(

    @startdate as DATETIME,

    @enddate as DATETIME

)

RETURNS INT

AS

BEGIN

    DECLARE @res int

SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)

    -(DATEDIFF(wk, @startdate, @enddate) * 2)

    -(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)

    -(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)

    RETURN @res

END

Then call your function like: select dbo.getWeekdays('2014-05-02', '2014-05-19')

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
May 19, 2014

I wish to make a few remarks.

(1) Your SQL code seems to be using the ColdFusion definition of dateDiff. If so, that would be a mistake. Each database management brand has its own definition of the dateDiff function. You should use that instead.

(2) The variable, list_orders_all.order_ID, may represent a number of IDs, rather than just one. In that case, the where-clause should be

WHERE order_ID IN (#valueList(list_orders_all.order_ID)#)

(3) There is one advantage in calculating the number of business days in CFML code instead of in SQL. Then the implementation will be independent of the database brand.

You can find the Coldfusion code you need on the web. A quick search produces the following 2 references:

CFLib.org – businessDaysBetween

Number of working days between two dates in ColdFusion - Stack Overflow

S
seasonedwebAuthor
Inspiring
May 19, 2014

BKBK:

I appreciate your help. I am not there yet, however.

BTW SQL Server.

Is this how the query and variable (date) values should be set up:

<cfquery name="get_client_tat" datasource="#Request.BaseDSN#">

SELECT order_ID, order_create_date, order_report_sent_date

   FROM main_orders

   WHERE order_ID IN (#valueList(list_orders_all.order_ID)#)

</cfquery>

<cfset date1 = "#get_client_tat.order_create_date#">

<cfset date2 = "#get_client_tat.order_report_sent_date#">

<cfquery name="get_appraiser_tat" datasource="#Request.BaseDSN#">

SELECT order_ID, order_create_date, order_complete_date

   FROM main_orders

   WHERE order_ID IN (#valueList(list_orders_all.order_ID)#)

</cfquery>

<cfset date3 = "#get_appraiser_tat.order_create_date#">

<cfset date4 = "#get_appraiser_tat.order_complete_date#">

and then:

<cfscript>

function businessDaysBetween(date1,date2) {

var numberOfDays = 0;

   

    while (date1 LT date2) {

        date1 = dateAdd("d",1,date1);

        if(dayOfWeek(date1) GTE 2 AND dayOfWeek(date1) LTE 6) numberOfDays = incrementValue(numberOfDays);

    }

    return numberOfDays;

}

</cfscript>

Client turn-around time: <cfoutput query="get_client_tat">#businessDaysBetween(CreateDate(#date1#),CreateDate(#date2#))# day(s).

</cfoutput>

Appraiser turn-around time:

<cfoutput query="get_appraiser_tat">#businessDaysBetween(CreateDate(#date3#),CreateDate(#date4#))# day(s).

</cfoutput>?

Invalid CFML construct found on line 239 at column 101.

ColdFusion was looking at the following text:

date1

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
May 19, 2014

Something seems awkward about CreateDate(#date1#). The function should have 3 arguments.

Before you go any further, see what comes out of the query. Do a dump, as follows

<cfquery name="get_client_tat" datasource="#Request.BaseDSN#">

SELECT order_ID, order_create_date, order_report_sent_date

   FROM main_orders

   WHERE order_ID IN (#valueList(list_orders_all.order_ID)#)

</cfquery>

<cfquery name="get_appraiser_tat" datasource="#Request.BaseDSN#">

SELECT order_ID, order_create_date, order_complete_date

   FROM main_orders

   WHERE order_ID IN (#valueList(list_orders_all.order_ID)#)

</cfquery>

<cfdump var="#get_client_tat#">

<cfdump var="#get_appraiser_tat#">

<cfabort>

Could you share the printscreen with us?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices