March 7, 2007 at 6:35 am
Good morning everyone
Not sure if it's the lack of coffee or me just trying to overcomplcate things but here's the issue.
I'm asked to create a report which shows the hourly usage of some BI queries.
The souce table looks like this:
CREATE TABLE Usage (
[actionTime] [datetime] ,
[Report] [nvarchar] (50) ,
.
.
some other columns
)
I added an extra table
CREATE TABLE Hours(h tinyint)
This second table just holds the numbers 0 to 23, representing the hours
Here's my query:
DECLARE @date1 as datetime
Set @date1 = '20070306'
SELECT
h as [Hour],
ISNULL(COUNT(Report),0) As Executions
FROM Hours LEFT OUTER JOIN Usage
ON Hours.h = DATEPART(hh, Usage.Actiontime)
WHERE CONVERT(char(8),Actiontime,112) = @date1
GROUP BY h
ORDER BY [Hour]
Desired result:
Hour Executions
0 6
1 0
2 3
.
.
.
22 0
23 7
The problem is that the query doesn't return the hours without any reports run, even though I'm using and LEFT OUTER JOIN.
I could solve this issue using a temp table or view before joining it to the Hours table,
but I'm convinced there must be a more direct solution.
Any ideas?
Tia Markus
[font="Verdana"]Markus Bohse[/font]
March 7, 2007 at 6:59 am
The problem is the WHERE statement, it changes the LEFT JOIN to an INNER JOIN (there are several posts on this forum that details why)
Change the WHERE to an AND like this
DECLARE @date1 as datetime
SET @date1 = '20070307'
SELECT h as [Hour], ISNULL(COUNT(Report),0) AS [Executions]
FROM @Hours h
LEFT OUTER JOIN @Usage u
ON h.h = DATEPART(hh, u.Actiontime)
AND CONVERT(char(8),u.Actiontime,112) = @date1
GROUP BY h.h
ORDER BY h.h
I prefer not to use CONVERT in date matching but to use variables for date boudaries, like this
DECLARE @date1 as datetime,@date2 as datetime
SET @date1 = '20070307'
SET @date2 = DATEADD(day,1,@date1)
SELECT h as [Hour], ISNULL(COUNT(Report),0) AS [Executions]
FROM @Hours h
LEFT OUTER JOIN @Usage u
ON h.h = DATEPART(hh, u.Actiontime)
AND u.Actiontime >= @date1
AND u.Actiontime < @date2
GROUP BY h.h
ORDER BY h.h
Far away is close at hand in the images of elsewhere.
Anon.
March 7, 2007 at 7:27 am
Thank you david,
I knew it was something simple, but sometimes you just don't see it.
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply