Geek asks and answers

Find intervals with mysql

I would like to extract the entries with an empty reservation and get the maximum of unregistered days ago (from the first free day). Expected Result:

id = 1, 2013-08-03, 7 days free
id = 1, 2013-08-24, 7 days free
id = 2, 2013-08-07, 10 days free
id = 2, 2013-08-24, 7 days free

It would be best if I can also request a free time interval, for example. request for 1,2,3,4,5,6,7, .14 .. free days. This is an example of my source data:

id      bookingDate    bookingId
--------------------------------
1        2013-08-03        0
1        2013-08-04        0
1        2013-08-05        0
1        2013-08-06        0
1        2013-08-07        0
1        2013-08-08        0
1        2013-08-09        0
1        2013-08-10        112
1        2013-08-11        112
1        2013-08-12        112
1        2013-08-13        112
1        2013-08-14        112
1        2013-08-15        112
1        2013-08-16        112
1        2013-08-17        112
1        2013-08-18        112
1        2013-08-19        112
1        2013-08-20        112
1        2013-08-21        112
1        2013-08-22        112
1        2013-08-23        112
1        2013-08-24        0
1        2013-08-25        0
1        2013-08-26        0
1        2013-08-27        0
1        2013-08-28        0
1        2013-08-29        0
1        2013-08-30        0
1        2013-08-31        0
2        2013-08-03        78
2        2013-08-04        78
2        2013-08-05        78
2        2013-08-06        78
2        2013-08-07        0
2        2013-08-08        0
2        2013-08-09        0
2        2013-08-10        0
2        2013-08-11        0
2        2013-08-12        0
2        2013-08-13        0
2        2013-08-14        0
2        2013-08-15        0
2        2013-08-16        0
2        2013-08-17        39
2        2013-08-18        39
2        2013-08-19        39
2        2013-08-20        39
2        2013-08-21        39
2        2013-08-22        39
2        2013-08-23        39
2        2013-08-24        0
2        2013-08-25        0
2        2013-08-26        0
2        2013-08-27        0
2        2013-08-28        0
2        2013-08-29        0
2        2013-08-30        0
2        2013-08-31        0

If anyone has a good idea for a better data structure, I can try to implement. The database is still under construction :-)

Edit:

CREATE TABLE IF NOT EXISTS `pricesBookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `baseId` int(11) NOT NULL,
  `bookingDate` date NOT NULL,
  `bookingId` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `baseId` (`baseId`,`bookingDate`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
+5
source share
3 answers

This should give the correct result:

select
  id,
  min(startDate) as startFreeDate,
  count(*) - (endDate is null) numFreeDays
from (
  select
    pb1.id,
    pb1.bookingDate startDate,
    min(pb2.bookingDate) endDate
  from
    pricesBookings pb1 left join pricesBookings pb2
    on pb1.id=pb2.id
       and pb2.price>0
       and pb2.bookingDate>pb1.bookingDate
  where
    pb1.price=0
  group by
    pb1.id,
    pb1.bookingDate
) s
group by id, endDate
order by id, startDate

see here .

If you need to search for all free slots, for example, 14 days, you can add HAVING:

group by id, endDate
having count(*) - (endDate is null) >= 14
order by id, startDate
+4
source

. - , .

.

SELECT z.baseid, z.bookingdate, 
CASE 
    WHEN j.id IS NOT NULL THEN '11+ days free'
    WHEN i.id IS NOT NULL THEN '10 days free'
    WHEN h.id IS NOT NULL THEN '9 days free'
    WHEN g.id IS NOT NULL THEN '8 days free'
    WHEN f.id IS NOT NULL THEN '7 days free'
    WHEN e.id IS NOT NULL THEN '6 days free'
    WHEN d.id IS NOT NULL THEN '5 days free'
    WHEN c.id IS NOT NULL THEN '4 days free'
    WHEN b.id IS NOT NULL THEN '3 days free'
    WHEN a.id IS NOT NULL THEN '2 days free'
    ELSE '1 day free'
END AS DaysFree
FROM pricesbookings z
INNER JOIN pricesbookings y
ON z.baseid = y.baseid AND z.bookingid = 0 AND y.bookingid != 0 AND DATE_ADD(y.bookingdate, INTERVAL 1 DAY) = z.bookingdate
LEFT JOIN pricesbookings a ON z.baseid = a.baseid AND z.bookingid = 0 AND a.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 1 DAY) = a.bookingdate
LEFT OUTER JOIN pricesbookings b ON a.baseid = b.baseid AND b.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 2 DAY) = b.bookingdate
LEFT OUTER JOIN pricesbookings c ON b.baseid = c.baseid AND c.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 3 DAY) = c.bookingdate
LEFT OUTER JOIN pricesbookings d ON c.baseid = d.baseid AND d.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 4 DAY) = d.bookingdate
LEFT OUTER JOIN pricesbookings e ON d.baseid = e.baseid AND e.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 5 DAY) = e.bookingdate
LEFT OUTER JOIN pricesbookings f ON e.baseid = f.baseid AND f.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 6 DAY) = f.bookingdate
LEFT OUTER JOIN pricesbookings g ON f.baseid = g.baseid AND g.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 7 DAY) = g.bookingdate
LEFT OUTER JOIN pricesbookings h ON g.baseid = h.baseid AND h.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 8 DAY) = h.bookingdate
LEFT OUTER JOIN pricesbookings i ON h.baseid = i.baseid AND i.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 9 DAY) = i.bookingdate
LEFT OUTER JOIN pricesbookings j ON i.baseid = j.baseid AND j.bookingid = 0 AND DATE_ADD(z.bookingdate, INTERVAL 10 DAY) = j.bookingdate
ORDER BY z.baseid, z.bookingdate

11 ( , , , ) , , .

z , y, , . LEFT JOINs , . CASE, , , .

, !

0

...

select
  concat_ws(',',(concat("ID=",id)),
  min(startDate),
 (concat((count(*) - (endDate is null))," Days Free"))) as result
from (
  select
    pb1.id,
    pb1.bookingDate startDate,
    min(pb2.bookingDate) endDate
  from
    pricesBookings pb1 left join pricesBookings pb2
    on pb1.id=pb2.id
       and pb2.price>0
       and pb2.bookingDate>pb1.bookingDate
  where
    pb1.price=0
  group by
    pb1.id,
    pb1.bookingDate
) s
group by id, endDate
  order by id, startDateselect
  concat_ws(',',(concat("ID=",id)),
  min(startDate),
 (concat((count(*) - (endDate is null))," Days Free"))) as result
from (
  select
    pb1.id,
    pb1.bookingDate startDate,
    min(pb2.bookingDate) endDate
  from
    pricesBookings pb1 left join pricesBookings pb2
    on pb1.id=pb2.id
       and pb2.price>0
       and pb2.bookingDate>pb1.bookingDate
  where
    pb1.price=0
  group by
    pb1.id,
    pb1.bookingDate
) s
group by id, endDate
  order by id, startDate
0

More articles:


All Articles