Query question

I'm having a problem with a query where it is not doing
what I would like for it to do. I have a Call table
where helpdesk calls get logged. I then have a Severity
table that stores the severity level of the call. Each
call must have a severity of 1,2 or 3.
I want to query the database and return Call counts for
each of the severity (1,2 and 3) given a date period.
For example, in the month of January, there were a total
of 2 severity 1 calls, 25 severity 2, and 50 severity 3.
My output would look like this.
Severity CallCount
-- --
1 2
2 25
3 50
Now, for the month of February, say there were zero(0)
severity 1 calls, 30 severity 2, and 77 severity 3, I
want my output to look like this
Severity CallCount
-- --
1 0
2 30
3 77
....However, my problem is that I do not get a record
for severity 1 cuz there are no calls for that month.
My query is below. What am I doing wrong? There is no
relationship between the two tables. The Severity table
just stores detail information regarding each of the
severities such as required response time and repair time
to fix a problem based on the severity. Please help.
SELECT
Severity.Severity,
Severity.ResponseTime,
Severity.RepairTime,
Count(Call.CallNo) As CallCount
FROM
Severity
Left Join Call
On Severity.Severity = Call.Severity
WHERE
Call.DateTimeSubmitted Between '01/01/2004'
and '01/31/2004'
AND
Call.Status <> 'CANCELLED'
GROUP BY
Severity.Severity,
Severity.ResponseTime,
Severity.RepairTime,
Order By
Severity.Severity
TIA,
VicPut Your WHERE criteria into the ON clause:
SELECT S.severity, S.responsetime, S.repairtime, COUNT(C.callno) AS
callcount
FROM Severity AS S
LEFT JOIN Call AS C
ON S.Severity = C.Severity
AND C.datetimesubmitted >= '20040101'
AND C.datetimesubmitted < '20050101'
AND C.status <> 'CANCELLED'
GROUP BY S.severity, S.responsetime, S.repairtime
ORDER BY S.Severity
As above, you may want to use >= and < for the date range rather than
BETWEEN. The code you posted will exclude rows where call was submitted on
2004-12-31 but the datetimesubmitted was timed after midnight.
Hope this helps.
David Portas
SQL Server MVP
--|||REPOST: I misread the dates
Put Your WHERE criteria into the ON clause:
SELECT S.severity, S.responsetime, S.repairtime, COUNT(C.callno) AS
callcount
FROM Severity AS S
LEFT JOIN Call AS C
ON S.Severity = C.Severity
AND C.datetimesubmitted >= '20040101'
AND C.datetimesubmitted < '20040201'
AND C.status <> 'CANCELLED'
GROUP BY S.severity, S.responsetime, S.repairtime
ORDER BY S.Severity
As above, you may want to use >= and < for the date range rather than
BETWEEN. The code you posted will exclude rows where call was submitted on
2004-01-31 but the datetimesubmitted was timed after midnight.
Hope this helps.
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:91cb01c4332b$51b42fb0$a301280a@.phx.gbl...
> I'm having a problem with a query where it is not doing
> what I would like for it to do. I have a Call table
> where helpdesk calls get logged. I then have a Severity
> table that stores the severity level of the call. Each
> call must have a severity of 1,2 or 3.
> I want to query the database and return Call counts for
> each of the severity (1,2 and 3) given a date period.
> For example, in the month of January, there were a total
> of 2 severity 1 calls, 25 severity 2, and 50 severity 3.
> My output would look like this.
> Severity CallCount
> -- --
> 1 2
> 2 25
> 3 50
>
> Now, for the month of February, say there were zero(0)
> severity 1 calls, 30 severity 2, and 77 severity 3, I
> want my output to look like this
> Severity CallCount
> -- --
> 1 0
> 2 30
> 3 77
> ....However, my problem is that I do not get a record
> for severity 1 cuz there are no calls for that month.
> My query is below. What am I doing wrong? There is no
> relationship between the two tables. The Severity table
> just stores detail information regarding each of the
> severities such as required response time and repair time
> to fix a problem based on the severity. Please help.
> SELECT
> Severity.Severity,
> Severity.ResponseTime,
> Severity.RepairTime,
> Count(Call.CallNo) As CallCount
> FROM
> Severity
> Left Join Call
> On Severity.Severity = Call.Severity
> WHERE
> Call.DateTimeSubmitted Between '01/01/2004'
> and '01/31/2004'
> AND
> Call.Status <> 'CANCELLED'
> GROUP BY
> Severity.Severity,
> Severity.ResponseTime,
> Severity.RepairTime,
> Order By
> Severity.Severity
> TIA,
> Vic