Range of date function

[binhpham]

I know we can check 1 date to see if it is within a range of date.(Use between() function)<br>Are there any way we can check a range of date to see if it is within a range of date ?(without use for/next loop checking every single date)<br>

 

[foxdev]

Perhaps I'm misunderstanding, but this seems pretty simple:<br><br>The logic would be:<br><FONT FACE=monospace>dStart &gt;= dValidStart and dEnd &lt;= dValidEnd</font><br><br><b>Examples:</b><br>Valid Range:<br>dValidStart = {01/01/2000}<br>dValidEnd&nbsp;&nbsp;&nbsp;= {06/1/2000}<br><br>Range to be tested:<br>dStart = {02/02/2000}<br>dEnd&nbsp;&nbsp;&nbsp;= {03/15/2000}<br><br><font color=red>Pass (valid)</font><br><br>Second Range to be tested:<br>dStart = {02/02/2000}<br>dEnd&nbsp;&nbsp;&nbsp;= {07/15/2000}<br><br><font color=red>Fail (invalid) </font><br><br> <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[binhpham]

Dear Robert<br>No, You are not misunderstanding me.That is it.<br>Your pretty simple help works beautifully.<br>Thanks a lot.<br><br>Now, this is another problem :<br><br>My boss throws me a big table (10,000 records) contains records of equipment that was scheduled to rent out.<br>RENTSCHEDULE.DBF fields : EQUIPID,DATEBEOUT,DATEBEIN,QUANTITY<br>and<br>EQUIPMENT.DBF fields :<br>EQIPID,INVENTORYQUANT<br><br>He asked me to write a function or procedure to validate the availability of equipment in rental order.<br><br>Function or procedure will take parameters as Equipidorder,dateoutorder,dateinorder,quantityorder and return a list of equipment from EQUIPMENT.DBF if dateoutorder and dateinorder is in the range of DATEBEOUT and DATEBEIN and quantityorder&gt;INVENTORYQUANT-SUM(QUANTIY)<br><br>I used SELECT SQL with no problem to display the list, but I don’t feel comfortable with that way because it only takes a couple records among 10,000 records.<br><br>Should I use SEEK command? If so, what a combination of index key should I use to seek for a range of date ?<br>Index key EQUIPID+DTOS(DATEBEOUT)+DTOS(DATEBEIN) seems doesn't work.<br>Any suggestions will be appreciated?<br>Binh Pham<br><br>

 

[foxdev]

Binh wrote:<br><i>I used SELECT SQL with no problem to display the list, but I don’t feel comfortable with that way because it only takes a couple records among 10,000 records.</i><br><br>I have two questions:<br><br>1) why don't you like the SELECT technique (I don't understand what you mean by &quot;it only takes a couple records&quot?&nbsp;&nbsp;Can you post the SQL statement you are using?<br><br>2) Can you be clearer about the purpose of this routine, specically &quot;validate the availability of equipment in rental order.&quot; - validate it in what way?&nbsp;&nbsp;When you say &quot;rental order&quot;, do you mean ordered by the DATEBEOUT column? <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[binhpham]

Hi Robert<br>Sorry for some mistakes I made on my post leading to the unclear problem.<br>I will be more carefully and clearer this time.<br><br>RENTSCHEDULE.DBF<br>Fields EQUIPID DATEBEOUT DATEBEIN QUANTITY<br>Rec. 00001 HVA001 09/01/2000 09/28/2000 03<br>Rec. 00002 WSC003 09/04/2000 09/10/2000 02<br>Rec. 00003 HVA001 11/01/2000 11/20/2000 05<br>...<br>Rec. 10000 HZA001 09/01/2000 01/28/2001 01<br><br>EQUIPMENT.DBF<br>Fields EQUIPID INVENT_QUANT<br>Rec. 00001 HVA001 09<br>Rec. 00002 WSC003 10<br>...<br>Rec. 01000 HZA001 01<br><br>When a customer wants to rent equipment, he or she will tell operator:<br>1. equipment ID&nbsp;&nbsp;(variable equip_ID_want)<br>2. date will be pickup (date_out_want) <br>3. date will be returned (date_in_want)<br>4. quantity (variable quant_want)<br>My job is writing a procedure taking those input and checking if that equipment at that range of date is available for rent. If it is available then display message “OK, You can rent it” if not then display a list of equipment was scheduled for rent within the range of date, base on following condition:<br>RENTSCHEDULE.equipid=equip_ID_want and date_out_want&gt;=RENSCHEDULE.datebeout and date_in_want&lt;=RENTSCHEDULE.datebein and quant_want&gt;EQUIPMENT.invent_quant – sum of quantity of the equipment was rent out within that range of date <br>Example1:<br><br>equip_ID_want=”HVA001”<br>date_out_want={09/26/2000}<br>date_in_want={11/12/2000}<br>quant_want=01<br>Will display “OK, you can rent it” (in that range of date, sum of quantity rent out is 8, total inventory is 9, customer want 01)<br><br>Example 2:<br><br>equip_ID_want=”HVA001”<br>date_out_want={08/23/2000}<br>date_in_want={11/02/2000}<br>quant_want=03<br>Will display a list:<br>&nbsp;Rec. 00001 HVA001 09/01/2000 09/28/2000 03<br>Rec. 00003 HVA001 11/01/2000 11/20/2000 05<br> 08<br>(In that range of date, sum of quantity rent out is 8, total inventory is 9, available is 1, and customer wants 03)<br><br>Example 3:<br><br>equip_ID_want=” WSC003”<br>date_out_want={09/01/2000}<br>date_in_want={09/03/2000}<br>quant_want=03<br>Will display “OK, you can rent it”<br>&nbsp;(In that range of date, nobody rent WSC003, sum of quantity rent out is 0, total inventory is 10, available is 10, customer wants 03)<br><br>Hope it is clear<br>Thanks for your time<br>Binh Pham <br><br><br>