SELECT user.landlordppid AS LandLordID, user.email AS HVEmail, a.name, ro.id AS Objektsid, ro.publishedts AS Pub, ro.firstpublishedts AS FörstaPub, user.creationdate AS KontoSkapat, aa.finishedts AS SigneringsDatum, adminarea1, locality, SUBSTRING_INDEX((SELECT area FROM rentcalculatordetails rc WHERE user.id = rc.userid ORDER BY updatedts DESC LIMIT 1), ‘,’, – 2) AS SenasteArea, (SELECT area FROM rentcalculatordetails rc WHERE user.id = rc.userid ORDER BY updatedts DESC LIMIT 1) AS SenasteArea_FullSträng, (SELECT calculatedvalue FROM rentcalculatordetails rc WHERE user.id = rc.userid ORDER BY updatedts DESC LIMIT 1) AS SenastePris_Hyreskalkylator, (SELECT leasingperiodstartfrom FROM rentcalculatordetails rc WHERE user.id = rc.userid ORDER BY updatedts DESC LIMIT 1) AS SenasteUthyrningsperiod_Hyreskalkylator, ro.landlordmonthlyprice AS PrisIAnnons, aa.landlordmonthlyprice AS PrisIKontrakt, IF(ro.endrentaldate IS NULL, 6, (DATEDIFF(ro.endrentaldate, ro.startrentaldate) / 30)) AS PeriodIAnnons, (ro.landlordmonthlyprice * 0.15) * (IF(ro.endrentaldate IS NULL, 6, (DATEDIFF(ro.endrentaldate, ro.startrentaldate) / 30))) AS UppskattadIntäkt, (ro.landlordmonthlyprice – aa.landlordmonthlyprice) / aa.landlordmonthlyprice AS DiffPris, (SELECT COUNT(1) FROM viewing WHERE ro.id = viewing.rentalobjectid) AS AntalIntresse, (SELECT COUNT(1) FROM viewing WHERE ro.id = viewing.rentalobjectid AND time IS NOT NULL) AS AntalVisning, (SELECT EXISTS( SELECT 1 FROM viewing WHERE ro.id = viewing.rentalobjectid) ) AS OmIntresse, (SELECT EXISTS( SELECT 1 FROM viewing WHERE ro.id = viewing.rentalobjectid AND viewing.latestavailabletimes = ‘[]’) ) AS OmIntresse_UtanVisningstid_UtanVisning, (SELECT EXISTS( SELECT 1 FROM viewing WHERE ro.id = viewing.rentalobjectid AND time IS NOT NULL) ) AS OmVisning, (SELECT EXISTS( SELECT 1 FROM agreement WHERE ro.id = agreement.rentalobjectid AND scrivestatus = ‘closed’) ) AS OmAvtal, DATEDIFF(ro.creationts, user.creationdate) AS KontoSkapasAnnonsSkapas, DATEDIFF(ro.firstpublishedts, user.creationdate), DATEDIFF(ro.firstpublishedts, ro.creationts) AS AnnonsSkapasAnnonsPub, DATEDIFF(v.time, ro.firstpublishedts) AS AnnonspublicerasFörstaVisning, DATEDIFF(aa.finishedts, v.time) AS FörstaVisningKontraktsigneras, DATEDIFF(aa.startdate, aa.finishedts) AS KontraktSignerasInlytt, DATEDIFF(aa.enddate, aa.startdate) AS InflyttUtflytt, LatestActiveDate, tp.NumberOfClicks, MinRooms, MaxRooms, MinSquareMeters, MaxSquareMeters, MaxPrice AS HGMaxprisBudget, tp.StartRentalDate AS HGStartDatum, tp.EndRentalDate AS HGSlutDatum, aa.payfeeuntil, aa.enddate, DATEDIFF(aa.payfeeuntil, aa.enddate) AS DagarFöreUppsägning, DATEDIFF(aa.enddate, aa.startdate) AS Kontraktslängd, DATEDIFF(aa.payfeeuntil, aa.enddate) / (DATEDIFF(aa.enddate, aa.startdate) + DATEDIFF(aa.payfeeuntil, aa.enddate)) AS AndelTidKvar, IF(aa.payfeeuntil IS NULL, 0, 1) AS OmFörtidsUppsagdUtanFörlängning, (SELECT totalincome FROM creditinformation c WHERE c.tennantppid = t.tennantppid ORDER BY creditinformationdate ASC LIMIT 1) AS FörstTotalInkomst, (SELECT debtsum FROM creditinformation c WHERE c.tennantppid = t.tennantppid ORDER BY creditinformationdate ASC LIMIT 1) AS FörstaSkuldsaldo, (SELECT creditscoring FROM creditinformation c WHERE c.tennantppid = t.tennantppid ORDER BY creditinformationdate ASC LIMIT 1) AS FörstCreditScore, (SELECT debtsum FROM creditinformation c WHERE c.tennantppid = v.tennantppid ORDER BY creditinformationdate ASC LIMIT 1) AS SkuldSaldoVisning, (SELECT creditscoring FROM creditinformation c WHERE c.tennantppid = v.tennantppid ORDER BY creditinformationdate ASC LIMIT 1) AS CreditScoreVisning FROM user LEFT JOIN landlord ON user.landlordppid = landlord.landlordppid LEFT JOIN landlordpp ON user.landlordppid = landlordpp.id LEFT JOIN rentalobject ro ON landlord.id = ro.landlordid LEFT JOIN viewing v ON v.rentalobjectid = ro.id LEFT JOIN emailcserviceagent e ON e.email = user.email LEFT JOIN agent a ON a.id = e.agentid LEFT JOIN agreement aa ON aa.rentalobjectid = ro.id LEFT JOIN tennantprofile tp ON tp.id = aa.tennantprofileid LEFT JOIN tennant t ON tp.tennantid = t.id WHERE user.landlordppid IS NOT NULL GROUP BY user.landlordppid