47 lines
1.4 KiB
SQL
47 lines
1.4 KiB
SQL
create or replace view abrechnung1 as
|
|
select b.jahr as jahr,
|
|
(b.betrag / o.flaeche * w.flaeche) as anteil,
|
|
o.shortname as objekt,
|
|
w.shortname as wohnung,
|
|
concat(m.vorname, ' ', m.nachname) as mieter,
|
|
m.id as mieter_id,
|
|
(timestampdiff(month,
|
|
if (year(m.einzug) = b.jahr, m.einzug, makedate(b.jahr, 1)),
|
|
if (m.auszug is not null, m.auszug, last_day(makedate(b.jahr, 365)))
|
|
) + 1) as nutzungszeit
|
|
from betriebskosten_abrechnung b,
|
|
objekt o,
|
|
wohnung w,
|
|
mieter m
|
|
where b.objekt = o.id and
|
|
w.objekt = o.id and
|
|
m.wohnung = w.id and
|
|
(year(m.einzug) = b.jahr or year(m.auszug) = b.jahr);
|
|
|
|
create or replace view abrechnung2 as
|
|
select jahr,
|
|
anteil / 12 * nutzungszeit as anteil,
|
|
objekt,
|
|
wohnung,
|
|
mieter,
|
|
mieter_id
|
|
from abrechnung1;
|
|
|
|
create or replace view abrechnung3 as
|
|
select a.jahr as jahr,
|
|
a.anteil as anteil,
|
|
a.objekt as objekt,
|
|
a.wohnung as wohnung,
|
|
m.vorname as vorname,
|
|
m.nachname as nachname,
|
|
m.anrede as anrede,
|
|
m.strasse as strasse,
|
|
m.plz as plz,
|
|
m.ort as ort
|
|
from abrechnung2 a,
|
|
mieter m
|
|
where m.id = a.mieter_id;
|
|
|
|
select * from abrechnung3 where jahr = '2020';
|
|
|