Help with mysql datetime qry

@hastxDec 31.2009

The way I am doing this currently uses a silly amount of loops, there must be a cleaner way.

I have a table of items with their check-out and check-in dates stored as “datetime” fields.

what I am trying to do is query all items that were out at any time between two dates and calculate the number of days they were out within the given date range.

The problem: Finding items which have both the checkout and checkin dates within the range is easy …but there are 3 other conditions to look for:

~~[LIST]~~

~~[*]~~

items whose check-out date was prior to the selected date range

~~[*]~~

items whose check-in date is after the selected date range (or might not even have a checkin date because they are still out)

~~[*]~~

items whose check-out AND check-in are outside the range.

[/LIST]

Any ideas? I have the functions built to calculate the number of days each item was out within the range, but querying the records which have days within the range is the pain.

to post a comment

PHP

6 Comments(s) _↴

@like_phpDec 31.2009 — #this is the query for 1st condtion

[code=php]
 $query = '
 select datediff(chkIn,chkOut) as daysDiffrent
 from table_name
 where (chkIn between "2009-12-01" and "2009-12-31") and
 (chkout between "2009-12-01" and "2009-12-31" )';
 [/code]

items whose check-out date was prior to the selected date range

[code=php]
 $query = '
 select itemId
 from table_name
 where chkOut < "2009-12-01"
 ';
 [/code]

items whose check-in date is after the selected date range (or might not even have a checkin date because they are still out)

[code=php]
 $query = '
 select itemId
 from table_name
 where chkIn > "2009-12-31" or chkIn is not null
 ';
 [/code]

items whose check-out AND check-in are outside the range.

[code=php]
 $query = '
 select itemId
 from table_name
 where  chkOut < "2009-12-01" and chkIn > "2009-12-31"
 ';
 [/code]

@criterion9Dec 31.2009 — #Are you trying to combine all the queries into a single master query? Have you tried using OR in combination with the separate WHERE clauses?

@hastxauthorDec 31.2009 — #Thanks for the replies, I think I'm on track with versions of the queries from like_php. I'll post the code when I finish, it is definately cleaner ...geez i had like 300 lines just to create this report.

@criterion9: what I have decided to do is use the individual queries to create an array of the id's of records fitting the criteria...then create a function to loop through that array to calculate the number of days out for each item and reconcile a charge.

@hastxauthorJan 04.2010 — #This is what I came up with: I'm omitting many of the supporting functions and variables just to focus on the logic.

Any comments on improving efficiency or accuracy welcome.

[code=php]
 function get_ids($qry){
 global $ids;
 $result = mysql_query($qry) or die ('Could Not Run Qry: '.mysql_error());
 while($row=mysql_fetch_assoc($result)){
 if(!in_array($row['id'], $ids)){
 $ids[]=$row['id'];
 }
 }
 }
 
 /////////////////////////
 //Start DB Conn 
 /////////////////////////
 $conn = mysql_connect($host, $user, $pw) or die ('Could Not Connect: '.mysql_error());
 mysql_select_db($db) or die ('Could Not Select DB: '.mysql_error());
 
 /////////////////////////
 //qry's should retrieve only records which have been 'OUT' at some point between selected start and end dates
 /////////////////////////
 $qrys = array(
 'SELECT id FROM transacts WHERE asset_class="'.$srch_asset.'" AND (out_date BETWEEN "'.$s_date.'" AND "'.$e_date.'") AND (in_date BETWEEN "'.$s_date.'" AND "'.$e_date.'" )',
 'SELECT id FROM transacts WHERE asset_class="'.$srch_asset.'" AND (out_date BETWEEN "'.$s_date.'" AND "'.$e_date.'") AND trans_stat="Open"',
 'SELECT id FROM transacts WHERE asset_class="'.$srch_asset.'" AND (out_date < "'.$s_date.'" AND trans_stat="Open")',
 'SELECT id FROM transacts WHERE asset_class="'.$srch_asset.'" AND out_date < "'.$s_date.'" AND (in_date BETWEEN "'.$s_date.'" AND "'.$e_date.'" )',
 'SELECT id FROM transacts WHERE asset_class="'.$srch_asset.'" AND out_date < "'.$s_date.'" AND in_date > "'.$e_date.'" AND trans_stat="Complete"'
 );
 
 /////////////////////////
 //Run qrys through custom function to get array of ID's fitting criteria
 /////////////////////////
 foreach($qrys as $qry){
 get_ids($qry);
 }
 
 //sort($ids);//optional for debugging
 
 /////////////////////////
 //Start DB Conn
 /////////////////////////
 foreach($ids as $found){
 $result=mysql_query("SELECT * FROM transacts WHERE id='$found'");
 while($row = mysql_fetch_assoc($result)){
 extract ($row);
 /////////////////////////
 //if the checkout date is prior to start_date, charge begins at start date
 /////////////////////////
 $charge_start_date = (checkout_before_start($out_date)) ? $s_date : $out_date; 
 if($trans_stat == 'Open'){
 /////////////////////////
 //if the transaction status is 'Open', charge should stop at the end date
 /////////////////////////
 $charge_end_date = $e_date;
 }else{
 /////////////////////////
 //if the checkin date is after the selected end_date, charge should stop at the end date, else it stops at in_date
 /////////////////////////
 $charge_end_date = (checkin_after_end($in_date)) ? $e_date : $in_date;//checkin_after_end returns T or F
 }
 $days_out = get_num_days_sans_weekend('-', $charge_start_date, $charge_end_date);
 $unit_prefix = get_alpha_prefix($unit);
 ...
 ....
 ..... 
 $ttl_charge = $days_out * $rate;
 $tmp .= "DC,$cost_code,$trans_date,$acctg_date,$unit,$days_out,$rate,$ttl_charge,$debit,$creditrn";
 }
 }
 
 mysql_close($conn);
 
 
 ////////////////////
 //write the mess to a log file for download and display
 ////////////////////
 file_put_contents($f_out, $tmp);
 [/code]

@NogDogJan 04.2010 — #I think you could do it in one query. Not 100% guaranteed to be correct, but should give you the idea:

[code=php]
 $query = "
 SELECT id FROM transacts
 WHERE
 asset_class='$srch_asset' AND (
 (
 out_date BETWEEN '$s_date' AND '$e_date' AND
 (in_date BETWEEN '$s_date' AND '$e_date' OR trans_stat='Open')
 ) OR (
 out_date < '$s_date' AND (
 trans_stat='Open' OR
 in_date BETWEEN '$s_date' AND '$e_date' OR
 (in_date > '$e_date' AND trans_stat='Complete')
 )
 )
 )
 ";
 [/code]

@hastxauthorJan 04.2010 — #thanks NogDog. I ran a test report from 11-23 thru 12-20 and got the same lines returned. That reduces the code even more, and no need to sort it for debugging.

Also in #PHP _↴

php : html to pdf convertion on the fly PHP dates Using google's OAuth

Success!

Help @hastx spread the word by sharing this article on Twitter...

Tweet This

Help with mysql datetime qry

6 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Help with mysql datetime qry

6 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

6 Comments(s) _↴

Also in #PHP _↴