-
1. Re: Elapsed time in business days?
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
-
2. Re: Elapsed time in business days?
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
-
3. Re: Elapsed time in business days?
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?
-
-
5. Re: Elapsed time in business days?
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')
-
6. Re: Elapsed time in business days?
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>
-
7. Re: Elapsed time in business days?
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>
-
8. Re: Elapsed time in business days?
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!
-
9. Re: Elapsed time in business days?
As you may have discovered, you can call the same function any number of times within the same page.
