![]() ![]() ![]() Note that we use the HourList.HourValue as the return column in the query and not the one dervied from Invoice. ON HourList.HourValue = GroupedInvoice.HourValue ,EXTRACT(HOUR FROM WhenInvoiced) AS HourValue It aggregates our Invoice records by hour */ It generates a list of hour values 0 through 23 */ ,COALESCE(GroupedInvoice.Counter, 0) AS Counter If that doesn't work because Firebird doesn't like joins onto functions (as I said, never used it so don't know) we can get around this by doing two derived tables: ON HourList.HourValue = EXTRACT(HOUR FROM WhenInvoiced) Now just a case of joining to the query you had. If that returns a 24 row list of 0 thru 23 then that's your derived table right there. I'm a SQL Server person.Ĭan you tell me if this works in Firebird: ON H.HOURLIST = EXTRACT(HOUR FROM WHENINVOICED) Current_timeStamp- 1Ĭolumn does not belong to referenced table I instead need it to generate a row for each 24 hours in the day, and place zero's against the hours for which there is no data. This works absolutely perfectly, except this only pulls data for hours where there is data to sum. SELECT sum(salestax * 7.66666666) AS COUNTER FROM INVOICE WHERE wheninvoiced> Current_timeStamp- 1 GROUP BY (EXTRACT(HOUR FROM WHENINVOICED)) What I want to achieve, is a sum of all SALESTAX figures multiplied by 7.66666666 for each hour. ![]() For the past 2 days I've been struggling with writing this query to export data from firebird. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |