Tony Tony - 6 months ago 10
MySQL Question

MySql Query (Brain Challenge)

List all aircraft that have three or more servicing jobs done within the last three months (90 Days). Also, List all aircraft registration number, plane_type, and manufacturer.

Pilot Table:

reg_nbr, plane_type, manufacturer, resident, date_service
'101', 'SE Prop', 'Lear', '1', '1998-12-01'
'202', 'DE Prop', 'Cessna', '1', '1999-11-22'
'303', 'SE Jet', 'Bombardier', '1', '2000-07-06'
'404', 'ME Jet', 'Lear', '1', '2001-02-24'
'505', 'SE Prop', 'Cessna', '0', '2003-12-01'
'606', 'DE Prop', 'Bombardier', '1', '2004-11-22'
'707', 'SE Jet', 'Lear', '1', '2005-07-06'
'808', 'ME Jet', 'Cessna', '0', '2005-02-24'
'909', 'SE Prop', 'Bombardier', '1', '2002-02-12'


Services Table:

workorder_nbr, file_nbr, reg_nbr, date_start, date_end, work_description, hours_worked, person_nbr
'2061', '3101', '101', '2014-03-16', '2014-03-16', 'Maint', '2', '901'
'2062', '3101', '101', '2014-07-20', '2014-07-21', 'Maint', '3', '901'
'2063', '3102', '202', '2015-12-22', '2015-12-22', 'Replace', '5', '901'
'2064', '3102', '202', '2015-07-12', '2015-07-13', 'Clean', '2', '901'
'2065', '3103', '303', '2015-05-01', '2015-05-01', 'Maint', '2', '901'
'2066', '3104', '404', '2015-07-09', '2015-07-09', 'Clean', '5', '901'
'2067', '3108', '505', '2015-07-09', '2015-07-09', 'Maint', '2', '901'
'2068', '3106', '606', '2015-08-21', '2015-08-21', 'Check', '1', '901'
'2069', '3101', '101', '2016-01-15', '2016-01-16', 'Maint', '3', '901'
'2070', '3101', '101', '2016-01-31', '2016-01-31', 'Repair', '15', '901'
'2071', '3101', '101', '2016-02-08', '2016-02-10', 'Repair', '8', '901'
'2072', '3108', '505', '2016-01-08', '2016-01-09', 'Upgrade', '20', '901'
'2073', '3108', '505', '2016-02-02', '2016-02-02', 'Repair', '1', '901'
'2074', '3108', '505', '2014-02-11', '2014-02-11', 'Maint', '1', '901'


My Query: (Its wrong anyways)

SELECT service.reg_nbr, plane_type, manufacturer
FROM service, aircraft
WHERE date_start BETWEEN (date(NOW() AND 90DAY)
GROUP BY service.reg_nbr
HAVING COUNT(service.reg_nbr) >=3


I am struggling in WHERE clase that how to make it show current date to 90 days back as required by the question)

Answer

One option would be to use INTERVAL:

WHERE date_start BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()
Comments