If you want this to be displayable as as widget (bar chart or pie graph) within Spiceworks, you have to be able to set up your report so it is a single series data, labels in the first column and values in the second. Given that, how would you expect your report to display data? One column per hour for each of opened and closed?

I think if you wanted to change is report to display just yesterday, then in the first part of each of the where clauses, you need to add the modifier for '-1 day'.
That should mean change each " =date('now') " to " =date('now', '-1 day') "

If you mean you want to see the 7am-7:59am time slot totaled for all 14 days and so on (i.e., showing each timeslot only once in the graph) then if you change the two date clauses to
where date(created_at)>=date('now','-14 days')
then I think that should work.

This can be simply extended for two weeks, but I'm afraid that it will become too condensed for one screen. Instead, you can build a separate report for previous week by changing in every date comparison statement the value of days subtracted: for instance '-6 days' become '-13 days', '-5 days' become '-12 days' and so on.