Changes between Version 3 and Version 4 of Internal/cProcedures/aGeneratingReports

Timestamp:

Apr 7, 2016, 12:43:10 PM (8 years ago)

Author:

Olivera Tosic

Comment:

—

Internal/cProcedures/aGeneratingReports

@@ -12 +12 @@
 
 }}}
+
+To get total user reservations in minutes for certain period per machine:
+
+{{{
+SELECT machines.name resource_name,
+sum((reservations.end_date   + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration
+FROM reservations
+LEFT JOIN reservation_users ON reservation_users.resid = reservations.resid
+LEFT JOIN machines         ON reservations.machid = machines.machid
+WHERE unix_timestamp('2000-01-01') <=(reservations.start_date + (reservations.startTime * 60))  AND
+unix_timestamp('2016-03-31') >= (reservations.end_date   + (reservations.endTime * 60))
+AND is_pending = 0
+GROUP BY resource_name
+ORDER BY duration desc
+}}}
+
+To get total resource reservations in minutes for certain period per machine.
+This is slightly different than above because user can invite other user(s) to his reservation.
+{{{
+SELECT machines.name resource_name,
+sum((reservations.end_date   + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration
+FROM reservations
+LEFT JOIN machines         ON reservations.machid = machines.machid
+WHERE unix_timestamp('2016-03-01') <=(reservations.start_date + (reservations.startTime * 60))  AND
+unix_timestamp('2016-03-31') >= (reservations.end_date   + (reservations.endTime * 60))
+AND is_pending = 0
+GROUP BY resource_name
+ORDER BY resource_name;
+}}}
+