1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
|
--drop view v_quittance;
drop table versement;
drop table locataire;
drop table location;
drop table appartement;
drop table mois;
go
create table mois
(
debut date not null,
fin as dateadd(day, -1, dateadd(month, 1, debut))
);
create table appartement
(
id int not null identity primary key,
adresse varchar(200) not null,
charges decimal(7, 2) not null,
loyer decimal(7, 2) not null
);
create table location
(
id int not null identity primary key,
appartement_id int not null references appartement(id),
debut date not null,
fin date null,
);
create table locataire
(
id int not null identity primary key,
nom varchar(50) not null,
prenom varchar(50) not null,
email varchar(256) not null,
adresse varchar(200) not null,
location_id int not null references location(id)
);
create table versement
(
id int not null identity primary key,
locataire_id int not null references locataire(id),
jour date not null,
montant decimal(7, 2) not null
);
go
/*
create view v_quittance
as
select 1
go
*/
insert into mois (debut) values ('2016-01-01'), ('2016-02-01'), ('2016-03-01'), ('2016-04-01'), ('2016-05-01'), ('2016-06-01'), ('2016-07-01'), ('2016-08-01'), ('2016-09-01'), ('2016-10-01'), ('2016-11-01'), ('2016-12-01');
insert into appartement (adresse, charges, loyer) values ('Adresse appartement', 50, 700);
insert into location (appartement_id, debut) values (1, '2016-07-01');
insert into locataire (nom, prenom, email, adresse, location_id) values ('Locataire 1', 'Locataire 1', 'Adresse locataire 1', 'locataire1@email.com', 1);
insert into locataire (nom, prenom, email, adresse, location_id) values ('Locataire 2', 'Locataire 2', 'Adresse locataire 2', 'locataire2@email.com', 1);
insert into locataire (nom, prenom, email, adresse, location_id) values ('Locataire 3', 'Locataire 3', 'Adresse locataire 3', 'locataire3@email.com', 1);
insert into versement (locataire_id, jour, montant) values (1, '2016-07-05', 300), (2, '2016-08-06', 250), (3, '2016-08-10', 600), (1, '2016-08-02', 150), (2, '2016-09-06', 250), (1, '2016-09-15', 250), (3, '2016-09-08', 200); |
Partager