For the past few days I've been struggling to get this to work, and you guru's will probably be able to take one look, laugh at me, and identify the problem within a matter of seconds.
Quick bit of history. I have a table called INVOICE which contains a column called "WHENINVOICED" which contains datetime data, and a column called "SALESTAX" which contains financial data which I want to collate into hourly increments for today only, from 00:00 - 23:59
I then have another table called "HOURLIST" with a column called "HourValues" which contains row data of:
01.01.2011, 02:00:00.000 .... etc
0-23 for each hour of the day. This column is also formatted as timestamp, for what I thought would make the process easier, having both fields identically formatted.
What I'm trying to accomplish is a query which sums all salestax data multiplied by 7.66666666 for every given hour, and even report those hours for which there is no data.
Here's my query so far:
COALESCE(sum(salestax * 7.66666666), 0)
LEFT OUTER JOIN INVOICE ON extract(HOUR from hourvalues) = EXTRACT(HOUR FROM wheninvoiced)
WHERE wheninvoiced > 'yesterday'
group by HOURLIST.HOURVALUES order by hourlist.HOURVALUES
This produces only 17 rows:
As you can see, as there are only 17 results, I'm missing 7 hours of zero records. I'm also not confident that it's pulling "today's data" from 00:00 - 23:59.