I am trying to create a report in spiceworks that requires the previous month's data. I saw this link here: Query last day, last week, last month SQLite however the month example only returns the current month. I need the previous month. The report will be ran on the first of each month and here's what I have thus far:
SELECT a.id as "Ticket #", c.first_name||' '|| c.last_name as "Created By", a.c_location as "Location",
a.c_hud as "HUD", a.closed_at as "Closed", a.c_urgency as "Urgency", a.category as "Category",
a.summary as "Summary", SUM(b.time_spent)/3600.0 as "Time Spent (Hrs)",
d.price*d.quantity as "Purchases"
FROM tickets a
LEFT OUTER JOIN ticket_work b ON a.id = b.ticket_id
LEFT OUTER JOIN purchase_list_items d on a.id = d.ticket_id
JOIN users c ON a.created_by = c.id
WHERE closed_at BETWEEN datetime('now', 'start of month') AND datetime('now','localtime')
GROUP BY a.id
ORDER BY c_location;
Once I have it pull the correct data set, I have to convert the the total b.time_spent field which is integer type, into an hours format. So if the time spent sums to be 33600, I would need the output to be converted to 9.33, vice the 9 that its currently given. It's currently only giving me Any help would much be appreciated.
To do a floating-point division, at least one of the numbers must be a floating-point number. Use:
SUM(b.time_spent) / 3600.0