Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

01/11/23 02:34:07 (2 years ago)

Author:

201087

Comment:

--

AdvancedReports

@@ -16 +16 @@
 order by stud.studentid
 }}}
+
+* objavite na gazdite i koja e razlikata megju cenata objavena i cenata potpisana za stanot
+{{{
+select au.ime,au.prezime,p.cityname as city_posted_for,p.price as posted_price, aa.apartment_rent as signed_price, abs(p.price-aa.apartment_rent) as difference from apartment_agreement as aa
+join apartment_owner ao on ao.ownerid=aa.ownerid 
+join appuser au on au.userid = ao.ownerid 
+join apartment apt on apt.apartmentid = aa.apartmentid 
+join owner_post op on op.ownerid  = ao.ownerid  
+join post p on p.postid = op.owner_postid 
+order by au.ime,difference
+}}}
+
+* stanovi vo sistemot koi momentalno ne se izdadeni
+{{{
+select apt.apartmentid,concat(b.cityname,', ',b.building_adress,' ',apt.apartment_adress) as adresa, a.ime, a.prezime, a.phone_number from apartment as apt
+left join apartment_agreement aa on aa.apartmentid = apt.apartmentid 
+join apartment_owner ao on ao.ownerid = apt.ownerid
+join appuser as a on ao.ownerid = a.userid
+join building b on apt.buildingid = b.buildingid 
+where now()>aa.date_expires  or aa.apartment_agreementid isnull
+}}}
+
+* preferenca za roomates na studentot spored objavite
+{{{
+select  au.userid,au.ime, au.prezime,p.number_of_roommates,count(p.number_of_roommates) as kolku_pati from post as p
+join student_post sp on sp.student_postid = p.postid 
+join student stud on stud.studentid = sp.studentid 
+join appuser au on stud.studentid = au.userid 
+group by 1,2,3,4
+order by au.userid, kolku_pati DESC
+}}}
+
+* broj na postovi od owner vo sekoj grad vo minatata godina vo septemvri
+{{{
+select a.ime, a.prezime, p.cityname, count(op.ownerid) from apartment_owner ao
+join appuser as a on ao.ownerid = a.userid
+join owner_post op on ao.ownerid = op.ownerid
+join post as p on op.owner_postid = p.postid
+where extract(year from p.date_posted)=extract(year from now())-1
+and extract(month from p.date_posted)=9
+group by 1,2,3
+}}}