GuestPass - How-To - Report Queries

Queries you may be asked to run for GuestPass reporting.

Transactions per Hour

SELECT the_day, the_hour, the_count
FROM (
SELECT 
date(date_created) as the_day,
hour(date_created) as the_hour,
count(*) as the_count
FROM transactions
where date_created >= '2014-10-31'
and date_created < '2014-11-01'
group by the_day, the_hour
) s
group by the_hour

GuestPass Per Hour

SELECT avg(the_count)
FROM (
SELECT 
date(date_created) as the_day,
hour(date_created) as the_hour,
count(*) as the_count
FROM accounts
where date_created > '2013-01-01'
and date_created <= NOW()
group by the_day, the_hour
) s
group by the_hour

Active Users

SELECT gps.username, gpa.first_name, gpa.middle_name, gpa.last_name FROM guestpass.staff AS gps
LEFT JOIN guestpass.accounts AS gpa ON (gpa.account_id = gps.account_id)

Transactions Per Day in Range

SELECT date(date_created) AS the_day, count(*) AS the_count FROM transactions
WHERE date_created >= '2014-01-02' AND date_created <= '2014-01-17'
GROUP BY the_day

Transactions Per Day in Range (Unique Students, Buildings)

SELECT 
l.name,
date(t.date_created) as the_day,
count(*) as the_count
FROM (SELECT transaction_id, date_created, location_id FROM transactions WHERE date_created >= '2011-12-15' AND date_created <= '2012-01-25' GROUP BY resident_account_id) AS t
LEFT JOIN locations AS l ON l.location_id = t.location_id
group by the_day, l.name




Keywords:sql, mysql, queries, lobner, security, housing   Doc ID:45166
Owner:Christopher M.Group:UW-Milwaukee Student Affairs IT
Created:2014-11-21 11:53 CDTUpdated:2018-03-16 14:38 CDT
Sites:UW-Milwaukee Student Affairs IT
Feedback:  0   0