Aggrégation par pallier

**StringBuilder** · 06/09/2016, 22h26

Bonsoir,

Je planche actuellement sur un problème qui me tient en échec : un cumul par pallier afin d'émettre des quittances de loyer en fonction de versements perçus.

Voici les règles :

- Un appartement peut héberger plusieurs locataires
- Un appartement a un loyer mensuel ainsi qu'un montant de charges mensuel
- Lorsqu'il y a plusieurs locataires, ils arrivent tous à la même date, et partent tous à la même date (un nouveau bail est établi à chaque changement de colocataire)
- Chaque colocataire d'un même appartement se réparti le loyer dans les mêmes proportions (si N colocataires, chacun paie 1/N loyer + charge)
- Un locataire peut effectuer des versements de façon anarchique, et avoir un solde débiteur ou créditeur
- Pour simplifier, on va dire qu'une location débute un premier du mois et se termine un dernier jour du mois, afin de n'avoir à gérer que des mois complets)

J'ai donc modélisé la chose suivante :

appartement (id, adresse)
location (id, id_appartement, debut, fin)
locataire (id, id_location, nom, prenom)
versement (id, id_locataire, date, montant)

Mettons donc un appartement avec un loyer de 700 euros et des charges de 50 euros.
3 locataires arrivent dedans au 1 juillet 2016.
=> Chacun doit donc payer (700 + 50) / 3 = 250

Je souhaite écrire une requête permettant, mois par mois, entre le début de la location et maintenant, de savoir où en sont les versements des loyers.

Par exemple, avec les paiements suivants :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
Locataire Date       Montant
1         05/07/2016     300
2         06/08/2016     250
3         10/08/2016     600
1         02/08/2016     150
2         06/09/2016     250
1         15/09/2016     250
3         08/09/2016     200

Je souhaite ce résultat :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
Locataire Mois       Date
1         01/07/2016 05/07/2016
2         01/07/2016 06/08/2016
3         01/07/2016 10/08/2016
3         01/08/2016 10/08/2016
2         01/08/2016 06/09/2016
2         01/09/2016 06/09/2016
3         01/09/2016 08/09/2016 
1         01/08/2016 15/09/2016

C'est à dire, pour chaque moi depuis le début de la location de chaque locataire, la date à laquelle le cumul des versements à égalé ou dépassé le montant des loyers dûs.
On voit par exemple que le locataire 1, malgré ses trois versements, ne s'est pas acquitté de la totalité de son loyer pour le mois de septembre.

On voit aussi que le premier versement du locataire 2 lui a permis de s'acquitter des deux premiers mois de loyer.

Seul hic, j'ai aucune idée de comment traduire ça en SQL...

Il me manque certainement une entité "mois", mais c'est pas ça qui me bloque le plus... C'est surtout le cumul progressif d'un côté, couplé à l'éclatement des lignes par seuil atteint par ce cumul et non pas par une donnée stockée en base...
Est-ce possible à réaliser ?

**StringBuilder** · 07/09/2016, 00h06

Actuellement j'en suis là :

Jeu de données :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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);

Ma requête actuelle (un peu usine à gaz, fallait pas me montrer les CTE maintenant j'en colle partout...) :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
with loyer (id_appartement, adresse, debut, fin, id_locataire, nom, prenom, montant_loyer)
as
(
	select a.id, a.adresse, l.debut, l.fin, lo.id, lo.nom, lo.prenom, (a.charges + a.loyer) / count(lo.id) over (partition by l.id)
	from appartement a
	inner join location l on l.appartement_id = a.id
	inner join locataire lo on lo.location_id = l.id
),
temps (id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, n_mois, debut_mois)
as
(
	select l.id_appartement, l.adresse, l.id_locataire, l.nom, l.prenom, sum(l.montant_loyer) over (partition by l.id_locataire order by m1.debut), dense_rank() over (partition by l.id_appartement order by m1.debut), m1.debut
	from loyer l
	inner join mois m1 on m1.debut >= l.debut and m1.fin <= coalesce(l.fin, '2999-12-31') and m1.debut <= getdate()
),
versements (id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, jour_versement, cumul_versement, n_mois, debut_mois)
as
(
	select t.id_appartement, t.adresse, t.id_locataire, t.nom, t.prenom, t.montant_loyer, v.jour, sum(v.montant) over (partition by t.id_locataire, n_mois order by v.jour), t.n_mois, t.debut_mois
	from temps t
	inner join versement v on v.locataire_id = t.id_locataire
)
select id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, min(jour_versement), n_mois, debut_mois
from versements
where cumul_versement - montant_loyer >= 0
group by id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, n_mois, debut_mois

Maintenant, il faudrait que j'arrive à ramener les lignes des mois pour lesquels je montant n'est pas atteint, mais avec par exemple "null" dans la colonne "jour_versement".

Mais là je sèche... Mise à part des usines à gaz monstrueuses, je trouve pas d'idée...
Pourtant, sans mon min(), j'ai bien les informations déjà...

**StringBuilder** · 07/09/2016, 00h09

Je suis bête en fait...

NULL n'est pas plus petit que n'importe quelle date... (il n'est pas plus grand non plus d'ailleurs ^^)

Du coup... Mon select final est tout simple :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, min(case when cumul_versement - montant_loyer >= 0 then jour_versement else null end), n_mois, debut_mois
from versements
group by id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, n_mois, debut_mois

Et donc ma belle vue

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
create view v_quittance (id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, jour_versement, n_mois, debut_mois)
with schemabinding
as
with loyer (id_appartement, adresse, debut, fin, id_locataire, nom, prenom, montant_loyer)
as
(
	select a.id, a.adresse, l.debut, l.fin, lo.id, lo.nom, lo.prenom, (a.charges + a.loyer) / count(lo.id) over (partition by l.id)
	from dbo.appartement a
	inner join dbo.location l on l.appartement_id = a.id
	inner join dbo.locataire lo on lo.location_id = l.id
),
temps (id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, n_mois, debut_mois)
as
(
	select l.id_appartement, l.adresse, l.id_locataire, l.nom, l.prenom, sum(l.montant_loyer) over (partition by l.id_locataire order by m1.debut), dense_rank() over (partition by l.id_appartement order by m1.debut), m1.debut
	from loyer l
	inner join dbo.mois m1 on m1.debut >= l.debut and m1.fin <= coalesce(l.fin, '2999-12-31') and m1.debut <= getdate()
),
versements (id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, jour_versement, cumul_versement, n_mois, debut_mois)
as
(
	select t.id_appartement, t.adresse, t.id_locataire, t.nom, t.prenom, t.montant_loyer, v.jour, sum(v.montant) over (partition by t.id_locataire, n_mois order by v.jour), t.n_mois, t.debut_mois
	from temps t
	inner join dbo.versement v on v.locataire_id = t.id_locataire
)
select id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, min(case when cumul_versement - montant_loyer >= 0 then jour_versement else null end), n_mois, debut_mois
from versements
group by id_appartement, adresse, id_locataire, nom, prenom, montant_loyer, n_mois, debut_mois
go

**escartefigue** · 07/09/2016, 12h14

Bonjour,

Ce qui me surprend dans votre modèle c'est que la location possède son propre identifiant, alors qu'il me semble que c'est une table issue de la relation entre un appartement et un ou plusieurs locataire(s)

De plus, votre modèle ne permet qu'une seule location pour un locataire, c'est sans doute le cas le plus courant, mais est-ce vraiment la règle ? vous ne l'avez pas précisé.

Enfin, j'ai l'impression, mais je ne l'ai pas vérifié n'ayant pas créé les tables ni les jeux d'essais, que votre requete ne prend pas en compte les règlements par les co-locataires

Un modèle comme suit me semble plus adapté :

Nom : MCD_location_01.png
Affichages : 181
Taille : 58,0 Ko

Nom : MCD_location_01.png
Affichages : 181
Taille : 58,0 Ko

**StringBuilder** · 07/09/2016, 12h38

Oui, il y a bien une entité "location" qui correspond au bail.
=> A chaque changement de colocataire, un nouveau bail est signé.

Donc les locataires sont liés à un seul et un seul bail. J'ai donc une entité location qui contient le début et la fin du bail.
En réfléchissant, le montant du loyer et des charges devrait d'ailleurs y être placé, plutôt que directement sur l'appartement.

En revanche, vous avez raison, dans l'absolu, l'entité "locataire" devrait faire référence à une entité "personne" qui contient les informations de la personne (nom, prénom, mail, adresse).

Dans mon cas, vu qu'il s'agit d'émettre des quittances de loyer, les "locataires" sont d'ailleurs les payeurs.

Sinon, dans mon cas, chaque colocataire à son propre encours et sa propre quittance. Donc j'ai bien N versements par locataires, mais ces versements n'impactent en rien le paiement global du loyer (chaque locataire ne s'est engagé que pour sa part à lui).

Mais bon, mon souci initial est résolu

**SQLpro** · 07/09/2016, 13h25

Quelques remarques sur ton modèle :

La location est en effet un bail et donc entité à part (contrat donc objet )
Le locataire est une entité à part et peut être une personne physique ou moral dotée de caractéristiques complémentaires (solvabilité par exemple).
-- En gros on a la chaine de dépendance LOCATAIRE => PERSONNE => PERSONNE PHYSIQUE ou PERSONNE MORALE (une entreprise, une association, une collectivité peut louer)
Vous n'avez pas modélisé l'occupant qui est souvent le locataire, mais pas toujours... Cela peut être un employé logé par l'entreprise, ou un étudiant logé par les parents !
De la même manière le payeur n'est pas toujours, ni le titulaire du bail, ni la personne logée !!!!
Enfin, juridiquement vous commettez une erreur, en cas de pluralité de locataire nommés au même bail, il y a solidarité, ce qui signifie que n'importe laquelle des personnes au bail doit payer pour toutes les autres en cas de défaillance...

A +

**escartefigue** · 07/09/2016, 14h14

Envoyé par StringBuilder

Oui, il y a bien une entité "location" qui correspond au bail.
=> A chaque changement de colocataire, un nouveau bail est signé.

D'accord je comprends mieux ainsi

Envoyé par StringBuilder

Donc les locataires sont liés à un seul et un seul bail. J'ai donc une entité location qui contient le début et la fin du bail.
En réfléchissant, le montant du loyer et des charges devrait d'ailleurs y être placé, plutôt que directement sur l'appartement.

Oui

Envoyé par SQLpro

Quelques remarques sur ton modèle :

La location est en effet un bail et donc entité à part (contrat donc objet )
Le locataire est une entité à part et peut être une personne physique ou moral dotée de caractéristiques complémentaires (solvabilité par exemple).
-- En gros on a la chaine de dépendance LOCATAIRE => PERSONNE => PERSONNE PHYSIQUE ou PERSONNE MORALE (une entreprise, une association, une collectivité peut louer)
Vous n'avez pas modélisé l'occupant qui est souvent le locataire, mais pas toujours... Cela peut être l'entreprise pour loger un employé ou les parents pour un étudiant !
De la même manière le payeur n'est pas toujours, ni le titulaire du bail, ni la personne logée !!!!

Remarques judicieuses, mais si j'en suis le destinataire, je suis parti des règles énoncées qui n'étaient pas si riches

Votre deuxième remarque est d'ailleurs en rapport avec ma question sur la possibilité pour un locataire d'avoir ou non plusieurs locations (dans votre cas, une personne morale peut louer plusieurs appartements pour plusieurs de ses employés). Mais ce besoin est écarté par la réponse de Stringbuilder : "Donc les locataires sont liés à un et un seul bail"

**StringBuilder** · 07/09/2016, 14h18

Merci pour les remarques

En fait, ici on est sur un cas personnel : j'ai un appartement en location, et dès le premier mois j'ai eu droit à tout entre les APL qui paient 3 mois d'un coup et les locataires qui n'ont pas compris qu'ils devaient payer les loyer ET les charges même quand ils étaient en vacances loin de l'appartement

Du coup j'ai voulu faire rapidement un petit programme pour suivre les versements divers et très variés, afin de savoir, sans passer par Excel, qui était à jour ou non et à qui je pouvais envoyer quelles quittances de loyer.
Par conséquent, c'est simplifié à l'extrême.
Mais effectivement, je vais tâcher d'enrichir le modèle afin, éventuellement, de mettre à disposition mon outil pour d'autres proprios ayant quelques légers soucis comme les miens. On sait jamais, ça peut intéresser des gens ^^

Aggrégation par pallier

Langage SQL

Discussions similaires

Partager

Partager