CODEWITHSUNDEEP
mysqlsql
Arockiaraj
Arockiaraj

Reputation: 353

Get max and min hour from mysql query between two different dates

Some sample data...

booking_date booking_time bookingstart_time     bookingend_time
2015-01-12                2015-01-12 18:25:00   2015-01-12 20:45:00
2015-01-12                2015-01-12 18:30:00   2015-01-12 20:30:00
2015-01-12                2015-01-12 20:30:00   2015-01-12 22:00:00
2015-01-12                2015-01-12 19:00:00   2015-01-12 20:20:00
2015-01-12                2015-01-12 20:30:00   2015-01-12 22:25:00
2015-01-12                2015-01-12 20:00:00   2015-01-12 21:40:00
2015-01-12                2015-01-12 19:00:00   2015-01-12 21:20:00
2015-01-13                2015-01-13 14:00:00   2015-01-13 16:10:00
2015-01-13                2015-01-13 13:30:00   2015-01-13 16:00:00
2015-01-13                2015-01-13 17:00:00   2015-01-13 19:05:00
2015-01-13                2015-01-13 18:05:00   2015-01-13 19:50:00
2015-01-16                2015-01-16 19:30:00   2015-01-16 21:20:00
2015-01-18                2015-01-18 12:30:00   2015-01-18 14:35:00
2015-01-18                2015-01-18 15:00:00   2015-01-18 16:50:00
2015-01-18                2015-01-18 12:30:00   2015-01-18 14:00:00
2015-01-18                2015-01-18 14:30:00   2015-01-18 15:35:00
2015-01-18                2015-01-18 12:30:00   2015-01-18 13:55:00
2015-01-18                2015-01-18 16:00:00   2015-01-18 17:40:00
2015-01-18                2015-01-18 14:30:00   2015-01-18 16:05:00

I want to get min and max hour from bookingstart_time between two different dates . ex: From 2015-01-06 to 2015-01-18,

My Query is ,

SELECT `booking_date`, MAX(HOUR(bookingstart_time)) 
AS highdemand,MIN(HOUR(bookingstart_time)) 
AS lowdemand FROM (`rest_restaurantbooking`) 
WHERE `res_id` = '17' AND `booking_date` >= '2015-01-06' 
AND `booking_date` <= '2015-01-18'.

But i am getting output MAX and MIN on same date. like

enter image description here

It's false.

I need output like somewhat , enter image description here Here both values are in same date. it should be in different dates. What mistake i have made here .

Upvotes: 0

Views: 1932

Answers (2)

TommCatt
TommCatt

Reputation: 5636

You have the booking_date field which makes things easy. Just group by that field and get the earliest and latest entry for each date:

select  booking_date, Min( bookingstart_time ) Earliest, Max( bookingend_time ) Latest
from    rest_restaurantbooking;

Now you have a list of days with the first and last booking time. Feed that into a query which isolates the time portion and selects the first and last booking of all the days for a single row result set. Then join the outputs together to select the specific dates.

with
Q1 as(
    select  booking_date,
            Min( bookingstart_time ) as Earliest,
            Max( bookingend_time ) as Latest
    from    @Bookings
    group by booking_date
),
Q2 as(
    select  Min( Cast( Earliest as time )) as Earliest, Max( cast( Latest as time )) as Latest
    from    Q1
)
select  case when Q2.Latest = Cast( Q1.Latest as time )
            then 'Latest Demand'
            else 'Earliest Demand' end as Demand,
        Q1.booking_date as BookingDate,
        case when Q2.Latest = Cast( Q1.Latest as time ) then Q1.Latest
            else Q1.Earliest end as BookingTime
from    Q1
join    Q2
    on  Q2.Earliest = Cast( Q1.Earliest as time )
    or  Q2.Latest   = Cast( Q1.Latest as time );

Which returns this:

Demand          BookingDate  BookingTime
--------------- ------------ -----------------------
Latest Demand   2015-01-12   2015-01-12 22:25:00.000
Earliest Demand 2015-01-18   2015-01-18 12:30:00.000

Yes, I know, MySQL doesn't have CTEs. So here is a fiddle using tables. You can create and populate the temp tables in your code.

Upvotes: 1

gvee
gvee

Reputation: 17171

Update the 3rd

Try this...

SELECT 'Highest Demand' As type
     , booking_date
     , Max(Hour(bookingstart_time)) As demand_time
FROM   rest_restaurantbooking
WHERE  res_id = '17'
AND    booking_date >= '2015-01-06'
AND    booking_date <= '2015-01-18'
GROUP
    BY booking_date

UNION ALL

SELECT 'Lowest Demand' As type
     , booking_date
     , Min(Hour(bookingstart_time)) As demand_time
FROM   rest_restaurantbooking
WHERE  res_id = '17'
AND    booking_date >= '2015-01-06'
AND    booking_date <= '2015-01-18'
GROUP
    BY booking_date

Upvotes: 0

Related Questions