[PHP-DB] Re: Calculating time unused

2003-10-03 Thread Shaun 

"Shaun" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> "David Robley" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED]
> > says...
> > > Hi,
> > >
> > > I have a table called Bookings which has two important columns;
> > > Booking_Start_Time and Booking_End_Time. These columns are both of
type
> > > DATETIME. Given any day how can I calculate how many hours are
available
> > > between the hours of 09.00 and 17.30 so a user can see at a glance how
> many
> > > hours they have unbooked on a particular day, can this be done with a
> query
> > > or do I have to work it out with PHP?
> > >
> > > Thanks for your help
> >
> > Seems easy enough - just sum the time booked per day and subtract it
from
> > the constant which is the total of available hours, and group by day.
> > You'll need to calculate the booked time based on existing start/end
> > values but you should be able to do that in SQL.
> >
> > Cheers
> > -- 
> > Quod subigo farinam
> >
> > A: Because it messes up the order in which people normally read text.
> > Q: Why is top-posting such a bad thing?
> > A: Top-posting.
> > Q: What is the most annoying thing on usenet?
>
> Thanks for your reply,
>
> I am having some odd results with this, just wondering if you could check
> this query for me:
>
> "SELECT COUNT(B.Booking_ID) AS Booking_ID,
>SUM(8.5 - (DATE_FORMAT(Booking_Start_Date, '%h:%i:%s')) +
> (DATE_FORMAT(B.Booking_End_Date, '%h:%i:%s'))) AS Available_Hours
>FROM WMS_Bookings B
>WHERE B.User_ID = '".$user_id."'
>AND B.Booking_Status <> '1'
>AND '".date("Y-m-d",
> strtotime("$loop3_this_year-$loop3_this_month-$loop3_this_day"))."' =
> DATE_FORMAT(Booking_Start_Date, '%Y-%m-%d')";
>
> Thanks for your help

Fixed it :)

$query = "SELECT
  8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
DATE_FORMAT(B.Booking_End_Date, '%i')) -
 ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS
Available_Hours
 FROM Bookings B
  WHERE B.User_ID = '".$user_id."'
  AND DATE_FORMAT(Booking_Start_Date, '%Y-%m-%d') = '".date("Y-m-d",
strtotime("$year-$month-$day"))."'";

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: Calculating time unused

2003-10-03 Thread Shaun 

"David Robley" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
> says...
> > Hi,
> >
> > I have a table called Bookings which has two important columns;
> > Booking_Start_Time and Booking_End_Time. These columns are both of type
> > DATETIME. Given any day how can I calculate how many hours are available
> > between the hours of 09.00 and 17.30 so a user can see at a glance how
many
> > hours they have unbooked on a particular day, can this be done with a
query
> > or do I have to work it out with PHP?
> >
> > Thanks for your help
>
> Seems easy enough - just sum the time booked per day and subtract it from
> the constant which is the total of available hours, and group by day.
> You'll need to calculate the booked time based on existing start/end
> values but you should be able to do that in SQL.
>
> Cheers
> -- 
> Quod subigo farinam
>
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet?

Thanks for your reply,

I am having some odd results with this, just wondering if you could check
this query for me:

"SELECT COUNT(B.Booking_ID) AS Booking_ID,
   SUM(8.5 - (DATE_FORMAT(Booking_Start_Date, '%h:%i:%s')) +
(DATE_FORMAT(B.Booking_End_Date, '%h:%i:%s'))) AS Available_Hours
   FROM WMS_Bookings B
   WHERE B.User_ID = '".$user_id."'
   AND B.Booking_Status <> '1'
   AND '".date("Y-m-d",
strtotime("$loop3_this_year-$loop3_this_month-$loop3_this_day"))."' =
DATE_FORMAT(Booking_Start_Date, '%Y-%m-%d')";

Thanks for your help

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: Calculating time unused

2003-09-30 Thread David Robley 
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] 
says...
> Hi,
> 
> I have a table called Bookings which has two important columns;
> Booking_Start_Time and Booking_End_Time. These columns are both of type
> DATETIME. Given any day how can I calculate how many hours are available
> between the hours of 09.00 and 17.30 so a user can see at a glance how many
> hours they have unbooked on a particular day, can this be done with a query
> or do I have to work it out with PHP?
> 
> Thanks for your help

Seems easy enough - just sum the time booked per day and subtract it from 
the constant which is the total of available hours, and group by day. 
You'll need to calculate the booked time based on existing start/end 
values but you should be able to do that in SQL. 

Cheers
-- 
Quod subigo farinam

A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet?

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php