Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

07/03/26 13:17:13 (7 days ago)

Author:

223091

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Advanced Reports =
 
-This page documents Phase P6 of the Room Reservation System project. The goal of this phase is to define and implement more complex analytical database reports using SQL. The reports are designed to provide a higher-level picture of the long-term operation of the reservation system.
-
-The project has one team member, therefore two advanced analytical reports are implemented.
-
-The reports are based on the relational schema created in Phase P2 and confirmed through the normalization process in Phase P5.
-
-== Report 1: Quarterly room utilization and approval report ==
+This page documents two complex analytical database reports for the Room Reservation System. The reports are written as SQL queries over the implemented PostgreSQL schema in the ''project'' schema. Each report uses several joined relations, grouping, aggregate calculations, conditional aggregation, derived indicators, and ranking in order to provide useful analytical information about the long-term operation of the reservation system.
+
+The selected reports are:
+
+* Quarterly room utilization and reservation status report
+* Quarterly equipment demand and stock pressure report
+
+Both reports are intended to support administrative decision-making. They can be used by a resource administrator or reservation approver to understand how rooms and equipment are used over time, which rooms are most frequently requested, and which equipment types may require additional stock.
+
+== Report 1: Quarterly room utilization and reservation status report ==
+
+=== Data requirements idea / concept title ===
+
+Quarterly room utilization and reservation status report
 
 === Data requirements description ===
 
-This report analyzes how rooms are used over a longer period of time. It groups reservations by quarter, building, and room, and calculates the number of reservation requests, the number of approved, rejected, cancelled, and pending reservations, the total requested hours, and the approved reservation hours.
-
-The report is useful for identifying:
-
-* which rooms are used most often,
-* which rooms have the highest approved reservation time,
-* how much demand exists for each room,
-* whether some rooms are frequently requested but not approved,
-* which rooms are important resources in the organization.
-
-This report can be used on a quarterly, annual, or multi-year level. In the current query, the report is shown for the year 2026.
+This report analyzes how rooms are used during a selected year, grouped by quarter, building, and room. It calculates how many reservations were made for each room, how many of them were approved, rejected, cancelled, or still pending, and how many total hours were requested and approved.
+
+The report is useful because it shows which rooms are used most often and which rooms may be underused. It also helps the organization identify whether certain buildings or room types have higher reservation demand. Since the report is grouped by quarter, it can be used for long-term analysis of room usage trends.
+
+The report uses data from the following relations:
+
+* ''project.reservations''
+* ''project.rooms''
+* ''project.buildings''
+
+The report performs the following operations:
+
+* joins reservations with rooms and buildings;
+* filters room-based reservations only;
+* groups reservations by quarter, building, and room;
+* counts total reservations;
+* counts reservations by status using conditional aggregation;
+* calculates requested and approved reservation hours;
+* calculates each room's share of approved hours within the quarter;
+* ranks rooms by utilization within each quarter.
 
 === Solution SQL ===
 
 {{{
-WITH reservation_base AS (
-SELECT
-b.building_id,
+WITH room_reservation_base AS (
+SELECT
+date_trunc('quarter', res.reservation_date)::date AS quarter_start,
 b.name AS building_name,
 r.room_id,
@@ -36 +52 @@
 res.reservation_id,
 res.status,
-DATE_TRUNC('quarter', res.reservation_date)::date AS quarter_start,
-EXTRACT(EPOCH FROM (res.end_time - res.start_time)) / 3600.0 AS duration_hours
+EXTRACT(EPOCH FROM (res.end_time - res.start_time)) / 3600.0 AS reservation_hours
 FROM project.reservations res
 JOIN project.rooms r
@@ -43 +58 @@
 JOIN project.buildings b
 ON r.building_id = b.building_id
-WHERE res.reservation_date >= DATE '2026-01-01'
+WHERE res.room_id IS NOT NULL
+AND res.reservation_date >= DATE '2026-01-01'
 AND res.reservation_date < DATE '2027-01-01'
 ),
-room_report AS (
+room_quarter_summary AS (
 SELECT
 quarter_start,
 building_name,
+room_id,
 room_code,
 type,
 capacity,
-COUNT(*) AS total_room_reservations,
+COUNT(reservation_id) AS total_room_reservations,
 COUNT(*) FILTER (WHERE status = 'approved') AS approved_reservations,
 COUNT(*) FILTER (WHERE status = 'rejected') AS rejected_reservations,
 COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_reservations,
 COUNT(*) FILTER (WHERE status = 'pending') AS pending_reservations,
-ROUND(COALESCE(SUM(duration_hours), 0)::numeric, 2) AS requested_hours,
-ROUND(COALESCE(SUM(duration_hours) FILTER (WHERE status = 'approved'), 0)::numeric, 2) AS approved_hours,
-ROUND(
-(100.0 * COUNT(*) FILTER (WHERE status = 'approved') / NULLIF(COUNT(*), 0))::numeric,
-2
-) AS approval_rate_percent
-FROM reservation_base
+ROUND(SUM(reservation_hours), 2) AS requested_hours,
+ROUND(COALESCE(SUM(reservation_hours) FILTER (WHERE status = 'approved'), 0), 2) AS approved_hours
+FROM room_reservation_base
 GROUP BY
 quarter_start,
 building_name,
+room_id,
 room_code,
 type,
 capacity
-)
+),
+room_quarter_ranked AS (
 SELECT
 quarter_start,
@@ -85 +100 @@
 requested_hours,
 approved_hours,
-approval_rate_percent,
-RANK() OVER (
+ROUND(
+approved_hours / NULLIF(SUM(approved_hours) OVER (PARTITION BY quarter_start), 0) * 100,
+2
+) AS approved_hours_share_percent,
+DENSE_RANK() OVER (
 PARTITION BY quarter_start
-ORDER BY approved_hours DESC, total_room_reservations DESC, capacity DESC
-) AS utilization_rank_in_quarter
-FROM room_report
+ORDER BY approved_hours DESC, total_room_reservations DESC, room_code
+) AS utilization_rank,
+CASE
+WHEN approved_hours >= 4 THEN 'high_usage'
+WHEN approved_hours >= 2 THEN 'medium_usage'
+WHEN approved_hours > 0 THEN 'low_usage'
+ELSE 'no_approved_usage'
+END AS utilization_level
+FROM room_quarter_summary
+)
+SELECT
+quarter_start,
+building_name,
+room_code,
+type,
+capacity,
+total_room_reservations,
+approved_reservations,
+rejected_reservations,
+cancelled_reservations,
+pending_reservations,
+requested_hours,
+approved_hours,
+approved_hours_share_percent,
+utilization_rank,
+utilization_level
+FROM room_quarter_ranked
 ORDER BY
 quarter_start,
-utilization_rank_in_quarter,
+utilization_rank,
 building_name,
 room_code;
 }}}
 
-=== Explanation of the SQL solution ===
-
-The query first creates a temporary result named ''reservation_base''. This part joins reservations, rooms, and buildings, and calculates the duration of each room reservation in hours.
-
-The second temporary result, ''room_report'', groups the data by quarter, building, room, room type, and capacity. It calculates aggregate values such as the total number of room reservations, the number of approved reservations, the number of rejected reservations, the number of cancelled reservations, the number of pending reservations, the total requested hours, and the approved hours.
-
-The final SELECT adds a ranking value using the SQL window function RANK(). Rooms are ranked inside each quarter according to approved reservation hours, number of reservations, and capacity.
-
-This makes the report analytical because it combines joins, filtering, grouping, conditional aggregation, duration calculation, and window ranking.
-
 === Solution Relational Algebra ===
 
-The following relational algebra expression uses extended relational algebra because the report requires grouping and aggregate functions.
+The report can be represented using extended relational algebra because it uses grouping, aggregation, conditional aggregation, and ranking.
 
 Let:
 
-* Reservations = Res
-* Rooms = R
-* Buildings = B
-
-First, create the joined reservation-room-building relation:
-
-{{{
-RB =
-σ_{reservation_date >= '2026-01-01' ∧ reservation_date < '2027-01-01'}
-(
-Res ⋈*{Res.room_id = R.room_id} R
-⋈*{R.building_id = B.building_id} B
-)
-}}}
-
-Then calculate grouped room utilization statistics:
-
-{{{
-RoomReport =
-γ_{quarter(reservation_date), B.name, R.room_code, R.type, R.capacity;
+* ''RES'' = Reservations
+* ''R'' = Rooms
+* ''B'' = Buildings
+
+First, select only room-based reservations in the selected period:
+
+{{{
+A =
+σ room_id IS NOT NULL AND reservation_date >= '2026-01-01' AND reservation_date < '2027-01-01'
+(RES)
+}}}
+
+Then join reservations with rooms and buildings:
+
+{{{
+B1 =
+A ⨝ A.room_id = R.room_id R
+}}}
+
+{{{
+B2 =
+B1 ⨝ R.building_id = B.building_id B
+}}}
+
+Then group the result by quarter, building, room, room type, and capacity:
+
+{{{
+C =
+γ quarter_start, building_name, room_id, room_code, type, capacity;
 COUNT(reservation_id) → total_room_reservations,
-COUNT_{status = 'approved'}(reservation_id) → approved_reservations,
-COUNT_{status = 'rejected'}(reservation_id) → rejected_reservations,
-COUNT_{status = 'cancelled'}(reservation_id) → cancelled_reservations,
-COUNT_{status = 'pending'}(reservation_id) → pending_reservations,
+COUNT(status = 'approved') → approved_reservations,
+COUNT(status = 'rejected') → rejected_reservations,
+COUNT(status = 'cancelled') → cancelled_reservations,
+COUNT(status = 'pending') → pending_reservations,
 SUM(end_time - start_time) → requested_hours,
-SUM_{status = 'approved'}(end_time - start_time) → approved_hours
-}
-(RB)
-}}}
-
-Finally, extend the grouped relation with an approval rate and a quarterly utilization ranking:
+SUM(end_time - start_time WHERE status = 'approved') → approved_hours
+(B2)
+}}}
+
+Finally, extended relational algebra is used to calculate the percentage share of approved hours and the utilization rank for each room inside the same quarter:
 
 {{{
 Result =
-ρ_{approval_rate_percent, utilization_rank_in_quarter}
-(RoomReport)
-}}}
-
-The report preserves the meaning of the original relations because every joined tuple represents a reservation connected to exactly one room and one building.
-
-== Report 2: Quarterly equipment demand and replenishment report ==
+ρ approved_hours_share_percent, utilization_rank, utilization_level
+(C)
+}}}
+
+This corresponds to the SQL query where window functions and CASE expressions are used for ranking and classification.
+
+=== Report result screenshot ===
+
+The following screenshot shows the result of executing the first report in DBeaver.
+
+[[Image(report1_room_utilization.png, width=100%)]]
+
+''Figure 1. Quarterly room utilization report showing room reservations grouped by quarter, building, room, room type, capacity, reservation status, requested hours, and utilization ranking.''
+
+=== Discussion ===
+
+The first report shows how each room is used during each quarter. The result includes the number of reservations, the number of approved, rejected, cancelled, and pending reservations, and the number of requested and approved reservation hours.
+
+This report is useful for identifying rooms with high demand and rooms that are rarely used. If a room has a high utilization rank and many approved hours, the organization may consider increasing availability of similar rooms or assigning more equipment to that room type. If a room has very low usage, the organization may investigate whether its capacity, type, location, or equipment availability is not suitable for users.
+
+== Report 2: Quarterly equipment demand and stock pressure report ==
+
+=== Data requirements idea / concept title ===
+
+Quarterly equipment demand and stock pressure report
 
 === Data requirements description ===
 
-This report analyzes how often equipment is requested as part of reservations and compares that demand with the available equipment quantity in the system. It combines equipment from general stock and equipment assigned to specific rooms.
-
-The report is useful for identifying:
-
-* which equipment is requested most often,
-* which equipment has high demand compared to available quantity,
-* whether equipment demand increases or decreases between quarters,
-* which equipment types should be considered for replenishment,
-* whether the organization has enough available equipment for future reservations.
-
-This report can support planning and decision-making for equipment purchasing and resource management.
+This report analyzes equipment demand during a selected year, grouped by quarter and equipment type. It compares the number of requested equipment items with the registered stock quantity and the equipment assigned to rooms. It also calculates how much demand was approved, pending, or rejected.
+
+The report is useful because it helps the organization understand which equipment types are requested most often and whether the existing stock is sufficient. It can support decisions about purchasing new equipment, moving equipment between rooms, or increasing the stock of frequently requested equipment.
+
+The report uses data from the following relations:
+
+* ''project.equipment''
+* ''project.room_equipment''
+* ''project.reservation_equipment''
+* ''project.reservations''
+
+The report performs the following operations:
+
+* calculates how much equipment is assigned to rooms;
+* joins requested equipment with reservations and equipment data;
+* groups equipment demand by quarter and equipment type;
+* calculates total requested quantity;
+* calculates approved, pending, rejected, and cancelled requested quantities;
+* compares demand with total registered quantity;
+* calculates demand ratio;
+* compares demand with the previous quarter;
+* ranks equipment by demand in each quarter;
+* classifies demand level as critical, high, medium, or low.
 
 === Solution SQL ===
@@ -176 +246 @@
 WITH room_stock AS (
 SELECT
-equipment_id,
-SUM(quantity) AS assigned_room_quantity
-FROM project.room_equipment
-GROUP BY equipment_id
+re.equipment_id,
+SUM(re.quantity) AS assigned_room_quantity
+FROM project.room_equipment re
+GROUP BY re.equipment_id
 ),
 equipment_demand AS (
 SELECT
+date_trunc('quarter', res.reservation_date)::date AS quarter_start,
 e.equipment_id,
 e.name AS equipment_name,
@@ -188 +259 @@
 COALESCE(rs.assigned_room_quantity, 0) AS assigned_room_quantity,
 e.stock_quantity + COALESCE(rs.assigned_room_quantity, 0) AS total_registered_quantity,
-DATE_TRUNC('quarter', res.reservation_date)::date AS quarter_start,
-COUNT(DISTINCT re.reservation_id) AS reservations_with_equipment,
-COALESCE(SUM(re.requested_quantity), 0) AS total_requested_quantity,
-COALESCE(SUM(re.requested_quantity) FILTER (WHERE res.status = 'approved'), 0) AS approved_requested_quantity,
-COALESCE(SUM(re.requested_quantity) FILTER (WHERE res.status = 'pending'), 0) AS pending_requested_quantity,
-COALESCE(SUM(re.requested_quantity) FILTER (WHERE res.status = 'rejected'), 0) AS rejected_requested_quantity
-FROM project.equipment e
+COUNT(DISTINCT res.reservation_id) AS reservations_with_equipment,
+SUM(req.requested_quantity) AS total_requested_quantity,
+COALESCE(SUM(req.requested_quantity) FILTER (WHERE res.status = 'approved'), 0) AS approved_requested_quantity,
+COALESCE(SUM(req.requested_quantity) FILTER (WHERE res.status = 'pending'), 0) AS pending_requested_quantity,
+COALESCE(SUM(req.requested_quantity) FILTER (WHERE res.status = 'rejected'), 0) AS rejected_requested_quantity,
+COALESCE(SUM(req.requested_quantity) FILTER (WHERE res.status = 'cancelled'), 0) AS cancelled_requested_quantity
+FROM project.reservation_equipment req
+JOIN project.reservations res
+ON req.reservation_id = res.reservation_id
+JOIN project.equipment e
+ON req.equipment_id = e.equipment_id
 LEFT JOIN room_stock rs
-ON rs.equipment_id = e.equipment_id
-LEFT JOIN project.reservation_equipment re
-ON re.equipment_id = e.equipment_id
-LEFT JOIN project.reservations res
-ON res.reservation_id = re.reservation_id
-WHERE res.reservation_date IS NULL
-OR (
-res.reservation_date >= DATE '2026-01-01'
+ON e.equipment_id = rs.equipment_id
+WHERE res.reservation_date >= DATE '2026-01-01'
 AND res.reservation_date < DATE '2027-01-01'
-)
 GROUP BY
+date_trunc('quarter', res.reservation_date)::date,
 e.equipment_id,
 e.name,
 e.stock_quantity,
-rs.assigned_room_quantity,
-DATE_TRUNC('quarter', res.reservation_date)::date
+rs.assigned_room_quantity
 ),
-equipment_analysis AS (
-SELECT
-*,
-ROUND(
-(total_requested_quantity::numeric / NULLIF(total_registered_quantity, 0)),
-2
-) AS demand_to_registered_quantity_ratio,
-LAG(total_requested_quantity) OVER (
-PARTITION BY equipment_id
-ORDER BY quarter_start
-) AS previous_quarter_requested
-FROM equipment_demand
-WHERE quarter_start IS NOT NULL
-)
-SELECT
-quarter_start,
+equipment_with_previous_quarter AS (
+SELECT
+quarter_start,
+equipment_id,
 equipment_name,
 stock_quantity,
@@ -238 +294 @@
 pending_requested_quantity,
 rejected_requested_quantity,
-COALESCE(previous_quarter_requested, 0) AS previous_quarter_requested,
-total_requested_quantity - COALESCE(previous_quarter_requested, 0) AS requested_quantity_change,
-demand_to_registered_quantity_ratio,
+cancelled_requested_quantity,
+LAG(total_requested_quantity) OVER (
+PARTITION BY equipment_id
+ORDER BY quarter_start
+) AS previous_quarter_requested_quantity
+FROM equipment_demand
+),
+equipment_ranked AS (
+SELECT
+quarter_start,
+equipment_name,
+stock_quantity,
+assigned_room_quantity,
+total_registered_quantity,
+reservations_with_equipment,
+total_requested_quantity,
+approved_requested_quantity,
+pending_requested_quantity,
+rejected_requested_quantity,
+cancelled_requested_quantity,
+COALESCE(previous_quarter_requested_quantity, 0) AS previous_quarter_requested_quantity,
+total_requested_quantity - COALESCE(previous_quarter_requested_quantity, 0) AS request_change_from_previous_quarter,
+ROUND(
+total_requested_quantity::numeric / NULLIF(total_registered_quantity, 0),
+2
+) AS demand_ratio,
+DENSE_RANK() OVER (
+PARTITION BY quarter_start
+ORDER BY total_requested_quantity DESC, equipment_name
+) AS demand_rank,
 CASE
-WHEN demand_to_registered_quantity_ratio >= 1 THEN 'high demand - consider replenishment'
-WHEN demand_to_registered_quantity_ratio >= 0.5 THEN 'medium demand'
-ELSE 'low demand'
-END AS demand_assessment
-FROM equipment_analysis
+WHEN total_requested_quantity > total_registered_quantity THEN 'critical_demand'
+WHEN total_requested_quantity >= total_registered_quantity * 0.75 THEN 'high_demand'
+WHEN total_requested_quantity >= total_registered_quantity * 0.40 THEN 'medium_demand'
+ELSE 'low_demand'
+END AS demand_level
+FROM equipment_with_previous_quarter
+)
+SELECT
+quarter_start,
+equipment_name,
+stock_quantity,
+assigned_room_quantity,
+total_registered_quantity,
+reservations_with_equipment,
+total_requested_quantity,
+approved_requested_quantity,
+pending_requested_quantity,
+rejected_requested_quantity,
+cancelled_requested_quantity,
+previous_quarter_requested_quantity,
+request_change_from_previous_quarter,
+demand_ratio,
+demand_rank,
+demand_level
+FROM equipment_ranked
 ORDER BY
 quarter_start,
-total_requested_quantity DESC,
+demand_rank,
 equipment_name;
 }}}
 
-=== Explanation of the SQL solution ===
-
-The query first calculates the total quantity of each equipment type that is assigned to rooms. This is done in the temporary result ''room_stock'' by grouping records from ''project.room_equipment''.
-
-The second temporary result, ''equipment_demand'', combines equipment, room stock, requested equipment, and reservations. It calculates the number of reservations that requested each equipment type, the total requested quantity, and the requested quantity by reservation status.
-
-The third temporary result, ''equipment_analysis'', calculates the demand-to-available-quantity ratio and uses the LAG() window function to compare current quarter demand with previous quarter demand.
-
-The final SELECT returns the equipment demand analysis and adds a textual demand assessment. If the requested quantity is greater than or equal to the total registered quantity, the report marks the equipment as high demand and suggests replenishment.
-
-This makes the report analytical because it combines multiple relations, grouping, conditional aggregation, stock comparison, trend comparison, and decision-oriented classification.
-
 === Solution Relational Algebra ===
 
-The following relational algebra expression uses extended relational algebra because the report requires grouping, aggregate functions, and trend comparison.
+The second report also uses extended relational algebra because it includes grouping, aggregation, left join, derived values, ranking, and comparison with previous quarter demand.
 
 Let:
 
-* Equipment = E
-* !RoomEquipment = REqRoom
-* !ReservationEquipment = REqRes
-* Reservations = Res
-
-First, calculate how much equipment is assigned to rooms:
-
-{{{
-RoomStock =
-γ_{equipment_id;
+* ''E'' = Equipment
+* ''RE'' = !RoomEquipment
+* ''REQ'' = !ReservationEquipment
+* ''RES'' = Reservations
+
+First, calculate the quantity of equipment assigned to rooms:
+
+{{{
+RS =
+γ equipment_id;
 SUM(quantity) → assigned_room_quantity
-}
-(REqRoom)
-}}}
-
-Then join equipment with room stock, requested equipment, and reservations:
-
-{{{
-EquipmentBase =
-E ⟕*{E.equipment_id = RoomStock.equipment_id} RoomStock
-⟕*{E.equipment_id = REqRes.equipment_id} REqRes
-⟕_{REqRes.reservation_id = Res.reservation_id} Res
-}}}
-
-Then group the data by quarter and equipment type:
-
-{{{
-EquipmentDemand =
-γ_{quarter(reservation_date), E.equipment_id, E.name, E.stock_quantity, assigned_room_quantity;
-COUNT(REqRes.reservation_id) → reservations_with_equipment,
+(RE)
+}}}
+
+Then join reservation equipment with reservations and equipment:
+
+{{{
+A =
+REQ ⨝ REQ.reservation_id = RES.reservation_id RES
+}}}
+
+{{{
+B =
+A ⨝ REQ.equipment_id = E.equipment_id E
+}}}
+
+Then add the equipment quantity assigned to rooms using a left outer join:
+
+{{{
+C =
+B ⟕ E.equipment_id = RS.equipment_id RS
+}}}
+
+Then select only reservations in the selected year:
+
+{{{
+D =
+σ reservation_date >= '2026-01-01' AND reservation_date < '2027-01-01'
+(C)
+}}}
+
+Then group the result by quarter and equipment type:
+
+{{{
+E1 =
+γ quarter_start, equipment_id, equipment_name, stock_quantity, assigned_room_quantity;
+COUNT(DISTINCT reservation_id) → reservations_with_equipment,
 SUM(requested_quantity) → total_requested_quantity,
-SUM_{status = 'approved'}(requested_quantity) → approved_requested_quantity,
-SUM_{status = 'pending'}(requested_quantity) → pending_requested_quantity,
-SUM_{status = 'rejected'}(requested_quantity) → rejected_requested_quantity
-}
-(EquipmentBase)
-}}}
-
-Finally, extend the grouped relation with the demand ratio, previous quarter demand, and demand assessment:
+SUM(requested_quantity WHERE status = 'approved') → approved_requested_quantity,
+SUM(requested_quantity WHERE status = 'pending') → pending_requested_quantity,
+SUM(requested_quantity WHERE status = 'rejected') → rejected_requested_quantity,
+SUM(requested_quantity WHERE status = 'cancelled') → cancelled_requested_quantity
+(D)
+}}}
+
+Finally, extended relational algebra is used to calculate:
+
+* total registered quantity;
+* demand ratio;
+* previous quarter demand;
+* demand change;
+* demand rank;
+* demand level classification.
 
 {{{
 Result =
-ρ_{total_registered_quantity,
-demand_to_registered_quantity_ratio,
-previous_quarter_requested,
-requested_quantity_change,
-demand_assessment}
-(EquipmentDemand)
-}}}
-
-The report preserves the meaning of the database model because equipment demand is calculated from !ReservationEquipment, while available equipment is calculated from Equipment and !RoomEquipment.
+ρ total_registered_quantity, demand_ratio, previous_quarter_requested_quantity,
+request_change_from_previous_quarter, demand_rank, demand_level
+(E1)
+}}}
+
+This corresponds to the SQL query where window functions, arithmetic expressions, and CASE expressions are used.
+
+=== Report result screenshot ===
+
+The following screenshot shows the result of executing the second report in DBeaver.
+
+[[Image(report2_equipment_demand.png, width=100%)]]
+
+''Figure 2. Quarterly equipment demand report showing requested equipment quantities, registered stock, approved, pending, rejected demand, demand ratio, previous quarter comparison, and demand level assessment.''
+
+=== Discussion ===
+
+The second report shows how often each equipment type is requested and compares the requested quantity with the equipment registered in the system. This is useful for identifying equipment that is requested more often than expected.
+
+For example, if an equipment item has a high demand ratio or a critical demand level, the organization may need to purchase more units, assign more units to rooms, or manage equipment availability more carefully. If an equipment item has low demand, the organization may decide that the existing stock is sufficient.
+
+The report also includes a comparison with the previous quarter. This allows administrators to identify whether equipment demand is increasing or decreasing over time.
 
 == Final discussion ==
 
-The two implemented reports provide analytical information that is not directly visible from simple table contents.
-
-The first report focuses on room utilization. It helps identify which rooms are most frequently used and which rooms have the highest number of approved reservation hours. This can help the organization understand room demand and make better decisions about room scheduling and future room allocation.
-
-The second report focuses on equipment demand. It helps identify which equipment types are frequently requested and whether the current registered quantity is sufficient. This can help the organization decide whether certain equipment should be replenished or redistributed.
-
-Both reports are implemented with SQL queries over the normalized relational model from Phase P2 and Phase P5. No additional database tables are required because the existing schema already stores the necessary information about rooms, buildings, reservations, users, equipment, equipment assigned to rooms, and equipment requested in reservations.
-
-The reports can be reused in later phases as part of the application or as administrative database reports.
+The two reports provide analytical information that is not directly visible from individual reservation records. The first report focuses on room utilization and reservation status, while the second report focuses on equipment demand and stock pressure.
+
+The reports are more complex than the basic SQL queries used in previous phases because they use:
+
+* multiple joined tables;
+* common table expressions;
+* aggregate functions;
+* conditional aggregation with FILTER;
+* date-based grouping by quarter;
+* calculated indicators;
+* window functions;
+* ranking;
+* CASE-based classification.
+
+These reports can later be integrated into the prototype or into a future application interface as administrative reports. They support better planning of room capacity, equipment availability, and long-term resource management.