Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

12/12/24 00:06:46 (5 weeks ago)

Author:

211228

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL) =
+
+
+=== Извештај за проверка колку студенти кои побарале специфична соба во првиот ден од рокот за вселување истата ја добиле како и информација за најмногу исполнет блок и број на зафатени соби 
+
+SELECT 
+    COUNT(DISTINCT str.student_id) AS br_studenti_zemale_soba_prv_den,
+    (SELECT 
+         str.block_id 
+     FROM StudentTookRoom str
+     GROUP BY str.block_id) AS blok_so_najvekje_zafateni_sobi,
+     b.num_available_rooms AS br_zafateni_sobi
+FROM student s
+JOIN roomrequest rr 
+    ON s.u_id = rr.student_id
+JOIN studenttookroom str 
+    ON str.student_id = s.u_id
+JOIN room r 
+    ON r.room_number = str.room_num
+JOIN block b 
+    ON b.block_id = r.block_id
+WHERE rr.status = 'Approved' 
+  AND str.start_date = (SELECT MIN(start_date) FROM StudentTookRoom)
+  AND rr.room_number = str.room_num
+  group by b.num_available_rooms
+  
+
+=== Извештај за бројка на доделени соба и блок на студенти кои побарале соодветна соба но истата не ја добиле
+
+ SELECT 
+    du.u_id AS student_id,
+    CONCAT(du.first_name, ' ', du.last_name) AS student_name,
+    rr.block_id AS requested_block,
+    rr.room_number AS requested_room,
+    str.block_id AS assigned_block,
+    str.room_num AS assigned_room,
+    COUNT(*) OVER (PARTITION BY rr.block_id, rr.room_number) AS mismatches_per_requested_room
+FROM dorm_user du 
+JOIN roomrequest rr 
+    ON du.u_id = rr.student_id
+JOIN studenttookroom str 
+    ON str.student_id = du.u_id
+WHERE rr.block_id != str.block_id OR rr.room_number != str.room_num;