How to get all Hours as a result of this query

[DaOtH]

All,

I have the following problem...

I am running the following query :

SELECT Items.Type, Hour([Time]) AS [Hour], Sum(Sales.Qty) AS Qty
FROM Sales INNER JOIN Items ON Sales.Item = Items.Item
GROUP BY Items.Type, Hour([Time]);



Result of this query is as follows :

Type1 Hour1 Qty
Type1 Hour2 Qty
Type2 Hour1 Qty
Type2 Hour2 Qty

Etc.

I use this to put the sales / hour / type in graphs.
Problem however is that i need to fix the x-axis of the graph in that way that i alwa's get the same format (e.g. all hours, even if not given a result in the query)

So, the query can come back with the following hours :
Type Hr Qty
Type1 00 10
Type1 01 15
Type1 02 8
Type1 08 30
Type1 14 50

But i need it to return
Type Hr Qty
Type1 00 10
Type1 01 15
Type1 02 8
Type1 03 0
Type1 04 0
Type1 05 0
Type1 06 0
Type1 07 0
Type1 08 30
...
Type1 23 0


Anybody any idea on how to do this

I worked it out using another table containing all possible hours and an inner join, that works fine. However, i can't have extra queries / tables in the database. This query needs to be directly in the graphs data source. "In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[dalchri]

You might try a cross-tab query. With a cross tab query, your types (Type1, Type2...) would be row headers and Hours would become column names.

Type Hour1 Hour2 Hour3
Type1 Qty Qty Qty
Type2 Qty Qty Qty
Type3 Qty Qty Qty

Without getting into the details of your data structures, the syntax would be:

TRANSFORM SUM(Quantity)
SELECT Type
FROM YourTable
GROUP BY Type
PIVOT Hour In ('Hour1','Hour2','Hour3')

The in clause will allow you to specify the number of columns without the use of an additional table.

Hope this helps!

 

[DaOtH]

not really what i meant. Putting it in a crosstab won't give exactly what i need...

If, let's say Hour '06' isn't in the dataset, it won't be in the query, which is exactly the problem.

It needs to return all Hours, the ones with Qty, with the Qty and if there is no Qty for the specific Hour (and therefore it is not (yet) in the query result) it should return a Qty of 0 for that Hour. "In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963