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

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25
Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25
sql

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx
.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25

Query never ends execution

Hello

I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.

Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @.@.rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.

TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.

What could be wrong?What could be wrong?
I'll guess it is one of these issues:
A) Gerbils nibbling on your network cable.
B) Global warming affecting your server environment.
C) Bears. Big nasty ones.
D) Some problem with the code you did not bother posting.|||I vote for bears. they are always on the threatdown causing trouble.|||Here is the general structure of the problematic stored procedure (Consider that there are 2 remote tables and 2 local tables instead of just one, and that the operations are made for both of them in a similar fashion):

declare @.LastUpdate datetime
declare @.Workstation varchar(250)

set @.Workstation=host_name()

SET NOCOUNT ON

execute spGetLastUpdate @.LastUpdate output

insert into Synonym_MyRemoteTable1Temp
select
@.Workstation,
ID,
Value1,
Value2,
Value3
from myLocalTable1
where UpdateTimeStamp>@.LastUpdate

execute Synonym_spMyRemoteProcedure @.Workstation -- Explained below
/*
This remote procedure makes an update and an insert as follows:

update MyRemoteTable1
set
Value1=MyRemoteTable1Temp.Value1,
Value2=MyRemoteTable1Temp.Value2,
Value3=MyRemoteTable1Temp.Value3
from MyRemoteTable1
inner join MyRemoteTable1Temp on MyRemoteTable1Temp.ID=MyRemoteTable1.ID and WorkStation=@.WorkStation

insert into MyRemoteTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from MyRemoteTable1Temp
where
WorkStation=@.WorkStation and ID not in (select ID from MyRemoteTable1)
*/

update MyLocalTable1
set
Value1=T.Value1,
Value2=T.Value2,
Value3=T.Value3,
UpdateTimeStamp=GetDate()
from MyLocalTable1
inner join
(
select
ID,
Value1,
Value2,
Value3
from Synonym_MyRemoteTable1
where UpdateTimeStamp>@.LastUpdate and
WorkStation<>@.Workstation

) as T on T.ID=MyLocalTable1.ID

insert into MyLocalTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from Synonym_MyRemoteTable1
where
UpdateTimeStamp>@.LastUpdate and
Workstation<>@.WorkStation and
ID not in (select ID from MyLocalTable1)