"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

Reply via email to