Hello!
I posted this question as a follow up to a previously answered question, and decided that this is completely different and should be treated as a new post (doh!).
Each peice of equipment can be tested many times and a certificate is given everytime it passes of fails. If the equipment passes then its date for recall is 1 year from the pass date.
Here's my SQL at the moment:
select
e.CATRef_num as "EQUI NUM",
MAX(tc.id_test_certificate) as "LAST TEST CERT NUM",
e.make as "MAKE",
c.id_customer as "CUSTOMER ID",
c.name as "CUSTOMER NAME",
el.date as "Last Test Date",
el.date + INTERVAL 1 YEAR as "RE-TEST DUE DATE"
from customer c, equipment e, equipment_location el, test_certificate tc
where e.CATRef_num = el.CATRef_num
and e.CATRef_num = tc.CATRef_num
and el.id_location = 6
and e.id_customer = c.id_customer
group by e.CATRef_num;
The id_location is 6 because this is the id reflecting the equipment passing the tests so requiring a recall.
This SQL is flawed because the equipment may have failed after having passed, so this SQL will pull out the passed certificate and ignore the fact it has been retested and failed.
Basically I need to report all of the equipment thats last test was a pass in april 2003 and will need to be retested in april 2004.
Here are the relevant creates:
create table CUSTOMER(
id_customer int(4) auto_increment primary key,
name varchar(40) not null,
address varchar(50) not null
);
create table EQUIPMENT(
CATRef_num int(4) auto_increment primary key,
id_customer int(4) not null,
id_category int(4) not null,
make vArchar(15) not null,
model varchar(40) not null,
serial_num int(30),
DOB date
);
create table EQUIPMENT_LOCATION(
id_equipment_location int(4) auto_increment primary key,
CATRef_num int(4) not null,
id_location int(4) not null,
date datetime
);
create table TEST_CERTIFICATE(
id_test_certificate int(4) auto_increment primary key,
CATRef_num int(4) not null,
id_engineer int(4) not null,
date date
);
Multiplicity:
1 customer has many equipment
1 equipment has many test_certificate
1 equipment has many equipment_location
I posted this question as a follow up to a previously answered question, and decided that this is completely different and should be treated as a new post (doh!).
Each peice of equipment can be tested many times and a certificate is given everytime it passes of fails. If the equipment passes then its date for recall is 1 year from the pass date.
Here's my SQL at the moment:
select
e.CATRef_num as "EQUI NUM",
MAX(tc.id_test_certificate) as "LAST TEST CERT NUM",
e.make as "MAKE",
c.id_customer as "CUSTOMER ID",
c.name as "CUSTOMER NAME",
el.date as "Last Test Date",
el.date + INTERVAL 1 YEAR as "RE-TEST DUE DATE"
from customer c, equipment e, equipment_location el, test_certificate tc
where e.CATRef_num = el.CATRef_num
and e.CATRef_num = tc.CATRef_num
and el.id_location = 6
and e.id_customer = c.id_customer
group by e.CATRef_num;
The id_location is 6 because this is the id reflecting the equipment passing the tests so requiring a recall.
This SQL is flawed because the equipment may have failed after having passed, so this SQL will pull out the passed certificate and ignore the fact it has been retested and failed.
Basically I need to report all of the equipment thats last test was a pass in april 2003 and will need to be retested in april 2004.
Here are the relevant creates:
create table CUSTOMER(
id_customer int(4) auto_increment primary key,
name varchar(40) not null,
address varchar(50) not null
);
create table EQUIPMENT(
CATRef_num int(4) auto_increment primary key,
id_customer int(4) not null,
id_category int(4) not null,
make vArchar(15) not null,
model varchar(40) not null,
serial_num int(30),
DOB date
);
create table EQUIPMENT_LOCATION(
id_equipment_location int(4) auto_increment primary key,
CATRef_num int(4) not null,
id_location int(4) not null,
date datetime
);
create table TEST_CERTIFICATE(
id_test_certificate int(4) auto_increment primary key,
CATRef_num int(4) not null,
id_engineer int(4) not null,
date date
);
Multiplicity:
1 customer has many equipment
1 equipment has many test_certificate
1 equipment has many equipment_location