<html>
  <head>
    <meta content="text/html; charset=windows-1252"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">Alright, I'm glad it helped someone :)<br>
      <pre class="moz-signature" cols="72">
</pre>
      On 14-11-28 08:05 PM, DP . wrote:<br>
    </div>
    <blockquote cite="mid:BLU184-W2956EF2C0B14DD14248F2FF27F0@phx.gbl"
      type="cite">
      <style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style>
      <div dir="ltr">
        <div>
          <div dir="ltr">
            <div dir="ltr">Victor, I take back my initial response on
              this old email. 
              <div><br>
              </div>
              <div>We ran into a couple of cases with some carriers and
                multiple matching prefixes. After actually trying your
                sub query (modified for Mysql), it actually does return
                the true lowest rate from a carrier while importantly
                respecting the longest match per carrier. <span
                  style="font-size:12pt;">Unlike the reorder_by_rate
                  function that does not respect the longest match per
                  carrier. That function simply returns the lowest rate,
                  period.</span></div>
              <div><br>
              </div>
              <div>So thanks!<br>
                <div><br>
                  <div>
                    <hr id="ecxstopSpelling">From: <a class="moz-txt-link-abbreviated" href="mailto:hi-tecc@hotmail.com">hi-tecc@hotmail.com</a><br>
                    To: <a class="moz-txt-link-abbreviated" href="mailto:freeswitch-users@lists.freeswitch.org">freeswitch-users@lists.freeswitch.org</a><br>
                    Subject: RE: [Freeswitch-users] Mod LCR SQL
                    sub-query for better routing<br>
                    Date: Mon, 16 Dec 2013 18:36:06 -0500<br>
                    <br>
                    <style><!--
.ExternalClass .ecxhmmessage P {
padding:0px;
}

.ExternalClass body.ecxhmmessage {
font-size:12pt;
font-family:Calibri;
}


--></style>
                    <div dir="ltr">I agree. This definitely sounds like
                      he simply needed the "reorder_by_rate" param. It
                      will reorder the initial sql results strictly by
                      rate: 
                      <div><br>
                      </div>
                      <div>
                        <ul
style="line-height:19.200000762939453px;padding-right:0px;padding-left:0px;font-family:sans-serif;font-size:13px;">
                          <li style="">reorder_by_rate - Forces the LCR
                            module to re-order the query strictly on
                            rate basis. By default this is turned off,
                            but enabling this will always prefer rate
                            over anything else.</li>
                        </ul>
                        <div><br>
                        </div>
                        <div>Beware this may have an adverse effect! I
                          initially had this turned on then quickly
                          realized it would sometimes try to route ALL
                          calls by the lowest rate found. </div>
                        <div><br>
                        </div>
                        <div>Ex: flowroute lists all calls for the US
                          with a default NPA of "1" at .0098. Now a user
                          trying to call Jamaica with "1876" at a rate
                          of 0.19 (or whatever) will get both flowroute
                          rates returned. The reorder by rate will
                          assume 0.0098 is a valid rate since it will
                          now be the "cheapest" in the list and send the
                          call along its way to flowroute, whom will now
                          bill you at 0.19. Now if you have another
                          carrier in your list with 1876 at 0.15 you can
                          see why this would be a problem.</div>
                        <div><br>
                        </div>
                        <div>In this case you will always want the
                          longest matched NPANXX rate. 
                          <div><br>
                            <div>
                              <hr id="ecxstopSpelling">Date: Fri, 13 Dec
                              2013 15:58:54 -0500<br>
                              From: <a class="moz-txt-link-abbreviated" href="mailto:intralanman@freeswitch.org">intralanman@freeswitch.org</a><br>
                              To: <a class="moz-txt-link-abbreviated" href="mailto:freeswitch-users@lists.freeswitch.org">freeswitch-users@lists.freeswitch.org</a><br>
                              Subject: Re: [Freeswitch-users] Mod LCR
                              SQL sub-query for better routing<br>
                              <br>
                              <div class="ecxmoz-cite-prefix">On
                                12/12/2013 02:42 PM, Victor Chukalovskiy
                                wrote:<br>
                              </div>
                              <blockquote
                                cite="mid:52AA1192.6020005@gmail.com">
                                <pre>Hello,

For those interested, I added a piece to mod lcr wiki. It makes sorting 
/ routing logic better than default logic:

<a moz-do-not-send="true" class="ecxmoz-txt-link-freetext" href="http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real-life_ratesheet_complexities" target="_blank">http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real-life_ratesheet_complexities</a>

Why it helps:

Rates rates can often be given both on per-NPA or per-NPANXX level 
depending on the carrier and on the NPA. Moreover, some carriers may 
have NPANXX rate lower than the corresponding NPA rate, while others 
will have it inverse. Neither simple ORDER BY rate, prefix; nor ORDER BY 
prefix, rate; give the truly cheapest route. The LCR logic should be 
two-step process to accommodate this.

Cheers,
-Victor
</pre>
                              </blockquote>
                              Unless I misunderstand what you're saying,
                              this is what the reorder_by_rate param
                              does.<br>
                              <br>
                              You'll always want to pick the longest
                              digit match per carrier.  Then you
                              probably want to grab the cheapest overall
                              rate of the matches you got back.<br>
                              <br>
                              -Ray<br>
                              <br>
                                <span style="color:rgb(0, 0,
                                0);font-family:sans-serif;font-size:13px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:19.046875px;text-align:left;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:rgb(255,
                                255, 255);display:inline !important;"></span>
                              <br>
                              _________________________________________________________________________
Professional
                              FreeSWITCH Consulting Services:
                              <a class="moz-txt-link-abbreviated" href="mailto:consulting@freeswitch.org">consulting@freeswitch.org</a>
                              <a class="moz-txt-link-freetext" href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a>
                              FreeSWITCH-powered IP PBX: The CudaTel
                              Communication Server
                              <a class="moz-txt-link-freetext" href="http://www.cudatel.com">http://www.cudatel.com</a>
                              Official FreeSWITCH Sites
                              <a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a>
                              <a class="moz-txt-link-freetext" href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a>
                              <a class="moz-txt-link-freetext" href="http://www.cluecon.com">http://www.cluecon.com</a>
                              FreeSWITCH-users mailing list
                              <a class="moz-txt-link-abbreviated" href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a>
UNSUBSCRIBE:<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a>
                              <a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a></div>
                          </div>
                        </div>
                      </div>
                    </div>
                  </div>
                </div>
              </div>
            </div>
          </div>
        </div>
        <style><!--
.ExternalClass .ecxhmmessage P {
padding:0px;
}

.ExternalClass body.ecxhmmessage {
font-size:12pt;
font-family:Calibri;
}

--></style> </div>
      <br>
      <fieldset class="mimeAttachmentHeader"></fieldset>
      <br>
      <pre wrap="">_________________________________________________________________________
Professional FreeSWITCH Consulting Services: 
<a class="moz-txt-link-abbreviated" href="mailto:consulting@freeswitch.org">consulting@freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a>

Official FreeSWITCH Sites
<a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://confluence.freeswitch.org">http://confluence.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://www.cluecon.com">http://www.cluecon.com</a>

FreeSWITCH-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a>
UNSUBSCRIBE:<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a>
<a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a></pre>
    </blockquote>
    <br>
  </body>
</html>