Skip to content

Instantly share code, notes, and snippets.

@natali

natali/query_for_getting_course_time.sql

Created August 3, 2010 10:35
Show Gist options
  • Save natali/506167 to your computer and use it in GitHub Desktop.
Save natali/506167 to your computer and use it in GitHub Desktop.
Download ZIP
getting the available schedules, with interval 2:20 and grace period 20 minutes.
Raw
query_for_getting_course_time.sql
SELECT a.plantime_id AS plantime_id1, MIN(a.plan_time) AS plan_time1,
b.plantime_id AS plantime_id2, MIN(b.plan_time) plan_time2,
c.plantime_id AS plantime_id3, MIN(c.plan_time) plan_time3,
d.plantime_id AS plantime_id4, MIN(d.plan_time) plan_time4
FROM golf__timeslots a
JOIN golf__timeslots b
ON a.business_id = b.business_id
AND a.plan_date = b.plan_date
AND b.availability = 'available'
AND b.course_id = '1'
AND b.plan_time
BETWEEN ADDTIME(a.plan_time, '02:20:00')
AND ADDTIME(a.plan_time, '02:40:00')
JOIN golf__timeslots c
ON b.business_id = c.business_id
AND b.plan_date = c.plan_date
AND c.availability = 'available'
AND c.course_id = '2'
BETWEEN ADDTIME(b.plan_time, '02:20:00')
AND ADDTIME(b.plan_time, '02:40:00')
JOIN golf__timeslots d
ON c.business_id = d.business_id
AND c.plan_date = d.plan_date
AND d.availability = 'available'
AND d.course_id = '2'
AND d.plan_time
BETWEEN ADDTIME(c.plan_time, '02:20:00')
AND ADDTIME(c.plan_time, '02:40:00')
WHERE 1
AND a.availability = 'available'
AND a.business_id = '1'
AND a.course_id = '1'
AND a.plan_date = '2010-08-02'
GROUP BY a.plantime_id
ORDER BY a.plan_time ASC, b.plan_time ASC, c.plan_time ASC, d.plan_time ASC
@natali
Copy link
Author

natali commented Aug 3, 2010

Result query

array
0 =>
object(stdClass)[56]
public 'plantime_id1' => string '1711' (length=4)
public 'plan_time1' => string '04:00:00' (length=8)
public 'plantime_id2' => string '1616' (length=4)
public 'plan_time2' => string '06:35:00' (length=8)
public 'plantime_id3' => string '1549' (length=4)
public 'plan_time3' => string '09:13:00' (length=8)
public 'plantime_id4' => string '1557' (length=4)
public 'plan_time4' => string '11:45:00' (length=8)
1 =>
object(stdClass)[57]
public 'plantime_id1' => string '1712' (length=4)
public 'plan_time1' => string '04:11:00' (length=8)
public 'plantime_id2' => string '1616' (length=4)
public 'plan_time2' => string '06:35:00' (length=8)
public 'plantime_id3' => string '1549' (length=4)
public 'plan_time3' => string '09:13:00' (length=8)
public 'plantime_id4' => string '1557' (length=4)
public 'plan_time4' => string '11:45:00' (length=8)
2 =>
object(stdClass)[58]
public 'plantime_id1' => string '1713' (length=4)
public 'plan_time1' => string '04:22:00' (length=8)
public 'plantime_id2' => string '1519' (length=4)
public 'plan_time2' => string '06:54:00' (length=8)
public 'plantime_id3' => string '1550' (length=4)
public 'plan_time3' => string '09:32:00' (length=8)
public 'plantime_id4' => string '1558' (length=4)
public 'plan_time4' => string '12:04:00' (length=8)
3 =>
object(stdClass)[59]
public 'plantime_id1' => string '1714' (length=4)
public 'plan_time1' => string '04:33:00' (length=8)
public 'plantime_id2' => string '1519' (length=4)
public 'plan_time2' => string '06:54:00' (length=8)
public 'plantime_id3' => string '1550' (length=4)
public 'plan_time3' => string '09:32:00' (length=8)
public 'plantime_id4' => string '1558' (length=4)
public 'plan_time4' => string '12:04:00' (length=8)
4 =>
object(stdClass)[60]
public 'plantime_id1' => string '1715' (length=4)
public 'plan_time1' => string '04:44:00' (length=8)
public 'plantime_id2' => string '1520' (length=4)
public 'plan_time2' => string '07:19:00' (length=8)
public 'plantime_id3' => string '1551' (length=4)
public 'plan_time3' => string '09:51:00' (length=8)
public 'plantime_id4' => string '1559' (length=4)
public 'plan_time4' => string '12:23:00' (length=8)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment