Unusual SQL query, giving "Invalid column name" error

[MrPaul]

Howdy,

The database I am working with has a table of transactions (called MunitTransaction) and I wish to retrieve the total number of transactions, and their total monetary value, between two dates. However, I want them grouped into groups.

For example, in the following query, I want to retrieve information about todays transactions, with the transactions grouped into hourly chunks:

SELECT
    COUNT( [MunitID] ) AS [TransCount],
    SUM( [Value] ) AS [Revenue],
    (DATEDIFF(hh, 2006-07-14 00:00:00, [DateCreated])) AS [Interval]
FROM
    [MunitTransaction]
WHERE
    (   [MunitTransaction].[DateCreated]
        BETWEEN
        2006-07-14 00:00:00
        AND
        2006-07-15 00:00:00 )
GROUP BY
    [Interval]
ORDER BY
    [Interval]

The error I am getting is Invalid column name Interval. I have tried a few things but they make no difference, or produce other errors. If anybody has an idea of how to do this, I would appreciate it if you could post back.

Thanks peeps.

 

[IUnknown]

Try by replacing [interval] with (DATEDIFF(hh, 2006-07-14 00:00:00, [DateCreated])) in the Group By and Order By parts.

 

[Puiu]

use just
...
group by DateCreated
order by DateCreated