In-Progress:
Here is a working sample of a complex SQL statement I use to present data from my osTicket database using MySQL. I customized the osTicket application using the extent of their administrative UI to allow me populate additional fields in osTicket. This was just a sanity check script I made before I placed the logic in a Java web application.
SELECT name, plan_tickets, (design_credits_hrs-design_used_hrs) design_bal_total_hrs, (develop_credits_hrs-develop_used_hrs) development_bal_total_hrs,
design_credits_hrs, design_used_hrs, develop_credits_hrs, develop_used_hrs, plan_start, plan_end, earliest_date, latest_date, total_tickets
FROM
(SELECT oo.name, COUNT(ot.number) plan_tickets, ots.name slaname, SUM(IFNULL(design,0)) design_bal_total_hrs, SUM(IFNULL(develop,0)) development_bal_total_hrs,
abs(sum(case when design < 0 then design else 0 end)) as design_credits_hrs,
sum(case when design > 0 then design else 0 end) as design_used_hrs,
sum(case when develop < 0 then develop else 0 end) as develop_credits_hrs,
sum(case when develop > 0 then develop else 0 end) as develop_used_hrs,
MAX(plan_start) plan_start, MAX(plan_end) plan_end,
DATE_FORMAT(MIN(ot.created),"%Y-%m-%d") earliest_date,
DATE_FORMAT(MAX(ot.created),"%Y-%m-%d") latest_date,
(SELECT COUNT(ot.number) total_tickets
FROM ost_ticket ot
LEFT JOIN ost_user ou ON ot.user_id = ou.id
WHERE ou.org_id = oo.id) total_tickets
FROM ost_ticket ot LEFT JOIN ost_ticket__cdata otc USING (ticket_id)
LEFT JOIN ost_department otd USING (dept_id)
LEFT JOIN ost_sla ots ON ot.sla_id = ots.id
LEFT JOIN ost_user ou ON ot.user_id = ou.id
LEFT JOIN ost_organization oo ON ou.org_id = oo.id
LEFT JOIN (SELECT *, DATE_SUB(DATE_ADD(plan_start, INTERVAL duration MONTH), INTERVAL 1 DAY) as plan_end
FROM
(SELECT org_id,
(SELECT date(from_unixtime(value)) AS plan_start
FROM sbogb_ost1912.ost_form_entry
LEFT JOIN sbogb_ost1912.ost_form_entry_values ON id = entry_id
WHERE form_id=4 and object_id=org_id and field_id=39) as plan_start,
(SELECT value AS duration FROM sbogb_ost1912.ost_form_entry
LEFT JOIN sbogb_ost1912.ost_form_entry_values ON id = entry_id
WHERE form_id=4 and object_id=org_id and field_id=40) as duration
FROM (SELECT ost_form_entry_values.entry_id, field_id, value, org_id
FROM ost_form_entry_values
LEFT JOIN (SELECT org_id, entry_id
FROM sbogb_ost1912.ost_organization
LEFT JOIN (SELECT id entry_id, object_id org_id
FROM sbogb_ost1912.ost_form_entry
WHERE form_id = 4) as form_entry_id
ON id=org_id) as org_entry_ids
ON ost_form_entry_values.entry_id=org_entry_ids.entry_id
WHERE field_id IN (39,40)) as start_dur
GROUP BY org_id,plan_start,duration) as start_info) as plan_start_end_info
ON plan_start_end_info.org_id = oo.id
GROUP BY oo.name) as sums
