Elapsed time in business days?

[seasonedweb]

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

[BKBK]

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

[seasonedweb]

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]

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?

[seasonedweb]

BKBK:

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

[BKBK]

I shall now give you a suggestion for one of the queries. Extending it to the second query should be straightforward.

<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

<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>

<cfif get_client_tat.recordcount GT 0>

<table>

<tr><th>Order ID</th><th>Client turn-around times (business days)</th></tr>

<cfoutput query="get_client_tat">

    <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>

    </cfif>

</cfoutput>

</table>

</cfif>

[BKBK · Accepted solution]

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>

[seasonedweb]

Thanks so much for your solution. I found that I did not need to have the second or third queries- I just used the main query:

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

SELECT mo.order_ID, mo.order_number, mo.order_client_ID, mo.order_status_ID, mo.order_appraiser_ID, mo.order_create_date, mo.order_property_street, mo.order_report_sent_date, mo.order_inspection_comp_date, lc.client_ID, lc.client_company, la.appraiser_ID, la.appraiser_fname, la.appraiser_lname

FROM  (main_orders mo

        LEFT JOIN lookup_clients lc

        ON mo.order_client_ID = lc.client_ID)

        LEFT JOIN lookup_appraisers la

        ON mo.order_appraiser_ID = la.appraiser_ID

WHERE order_status_ID = 9

OR order_status_ID = 10

GROUP   

    BY     mo.order_ID, mo.order_number, mo.order_client_ID, mo.order_status_ID, mo.order_appraiser_ID, mo.order_create_date, mo.order_property_street, mo.order_report_sent_date, mo.order_inspection_comp_date, lc.client_ID, lc.client_company, la.appraiser_ID, la.appraiser_fname, la.appraiser_lname

ORDER BY order_create_date desc

</cfquery>

Then your script:

<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>

Then the query results:

<table width="860" border="1" class="borders2">

<tr>

<td width="10%" class="tdheaderdynamic">Create Date</td>

<td width="25%" class="tdheaderdynamic">Client</td>

<td width="25%" class="tdheaderdynamic">Property</td>

<td width="20%" class="tdheaderdynamic">Appraiser</td>

<td width="10%" class="tdheaderdynamic">Status</td>

<td width="10%" class="tdheaderdynamic">Turn-around time</td></tr>

<cfoutput query="get_appraiser_tat">

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

<cfset dt1=parseDateTime(order_create_date)>

<cfset dt2=parseDateTime(order_inspection_comp_date)>

<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('d9ecff'))#">

<td width="10%" class="tddynamic">#DateFormat(order_create_date, "mm/dd/yyyy")#</td>

<td width="25%" class="tddynamic">#client_company#</td>

<td width="25%" class="tddynamic">#order_property_street#</td>

<td width="20%" class="tddynamic">#appraiser_lname#</td>

<td width="10%" class="tddynamic"><cfif #order_status_ID# EQ 9>Invoiced

<cfelseif #order_status_ID# EQ 10>Closed-Paid</cfif></td>

<td width="10%" class="tddynamic">#businessDaysBetween(dt1,dt2)#</td>

</tr>

</cfif>

</cfoutput>

</table>

</td>

</tr>

</table>

However, I needed to split the results pages - iow one for appraiser TAT and one for client TAT because:

The following information is meant for the website developer for debugging purposes.

Error Occurred While Processing Request

Routines cannot be declared more than once. The routine businessDaysBetween has been declared twice in the same file.

I tried to use another script on the same page with function businessDaysBetween(date3,date4) {

so I could get both appraiser TAT and client TAT on one page- this would come in handy because I know the next question from the admins is if they can perform multiple-criteria like appraisers to client TATs .

Thanks again for the help!

[BKBK]

As you may have discovered, you can call the same function any number of times within the same page.

[ion]

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')