9 Replies Latest reply on May 21, 2014 12:19 PM by BKBK

    Elapsed time in business days?

    seasonedweb Level 1

      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

        • 1. Re: Elapsed time in business days?
          BKBK Adobe Community Professional & MVP

          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?
            seasonedweb Level 1

            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?
              BKBK Adobe Community Professional & MVP

              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?

              • 4. Re: Elapsed time in business days?
                seasonedweb Level 1

                BKBK:

                 

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

                 

                450_printscreen.jpg

                • 5. Re: Elapsed time in business days?
                  ion Level 1

                  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?
                    BKBK Adobe Community Professional & MVP

                    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?
                      BKBK Adobe Community Professional & MVP

                      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?
                        seasonedweb Level 1

                        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?
                          BKBK Adobe Community Professional & MVP

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