Query not returning correct results

[WayneGretzky]

This query still produces results that are wrong. It should never return any crc unless it = 'error' and it does.

Second, it should only return results where there are more than 2 records with the same phone number for those records where the crc = 'error'

select calldatetime, crc, projname, phonenum from dbo.history h
WHERE ProjName IN ('sss' , 'mmm')
AND ((EXISTS(SELECT Phonenum FROM history hh WHERE h.CRC = 'ERROR'AND hh.phonenum = h.phonenum GROUP BY hh.Phonenum HAVING COUNT(hh.Phonenum) > 2 )))
AND (CallDateTime between dateadd(mi, -130, GetDate()) and Getdate())
AND PhoneNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

This query also does the same thing:

select h.calldatetime, h.crc, h.projname, h.phonenum from dbo.history h
WHERE ProjName IN ('sss' , 'mmm')
AND (SELECT Count(*) FROM history WHERE CRC = 'ERROR' and phonenum = h.phonenum HAVING Count(*) > 2) > 2
AND (CallDateTime between dateadd(mi, -130, GetDate()) and Getdate())
AND PhoneNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Output looks like this which is not correct:

2006-01-05 16:25:19.000 OK sss 8668762739
2006-01-05 16:47:22.000 error mmm 8668762739
2006-01-05 16:53:23.000 OK mmm 8668762739
2006-01-05 17:15:26.000 AMM sss 8668762739

I should not be seeing OK or AMM, and I should be seeing more than one error for the same phone number

 

[SQLBill]

One thing I noticed right away about the second query is that you have a HAVING but no GROUP BY. I believe you have to use a GROUP BY when you use HAVING.

Why should you have more that one error for the same phone number? Your code just asks for the phone number WHERE the COUNT(*) is greater than 2. Let's say you have this:

2006-01-05 16:47:22.000 error mmm 8668762739
2006-01-05 16:53:23.000 OK mmm 8668762739
2006-01-05 17:15:26.000 AMM sss 8668762739

Guess what? The COUNT(*) for phone number 8668762739 IS greater than 2! And one of the crc's is ERROR. So that meets the WHERE and HAVING requirements.

So, are you sure there is more than one error for the same phone number?

-SQLBill



Posting advice: FAQ481-4875

 

[SQLBill]

To add, nowhere do you eliminate AMM and OK.

Breaking down your query...using these rows:

2006-01-05 16:47:22.000 error mmm 8668762739
2006-01-05 16:53:23.000 OK mmm 8668762739
2006-01-05 17:15:26.000 AMM sss 8668762739

--select all these columns
select h.calldatetime, h.crc, h.projname, h.phonenum
from dbo.history h
--based on this criteria
WHERE ProjName IN ('sss' , 'mmm') --yes for all 3 rows
AND (SELECT Count(*) FROM history WHERE CRC = 'ERROR' and phonenum = h.phonenum HAVING Count(*) > 2) --yes there are more than 2 rows and the CRC 'ERROR' is in the group
> 2 --I don't see the purpose of this extra >2
--yep, the following two ANDs are met also
AND (CallDateTime between dateadd(mi, -130, GetDate()) and Getdate())
AND PhoneNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

-SQLBill

Posting advice: FAQ481-4875

 

[WayneGretzky]

The purpose of seeking results where there are 2 records with the same phone number that have crc = error is to notify our network guys of an error. They don't want to be notified if there is only one error record for a specific phone number.

 

[WayneGretzky]

>>>To add, nowhere do you eliminate AMM and OK

But I shouldn't have to, the sub select speficially looks for crc = 'error' and nothign else

 

[WayneGretzky]

the kind of output I expect to see is like this for example:

2006-01-05 16:47:22.000 error mmm 8668762739
2006-01-05 16:53:23.000 error mmm 8668762739
2006-01-05 17:15:26.000 error sss 8668762739

there is a unique id for each of those by the way...such as:

10000 2006-01-05 16:47:22.000 error mmm 8668762739
11332 2006-01-05 16:53:23.000 error mmm 8668762739
33232 2006-01-05 17:15:26.000 error sss 8668762739

the primary ID is just for informational purposes for you to understand that each of these instances are individual records in the history table.

 

[Walid]

My understanding is you want to record the event of an error associated with the same phone number and assemble a list of each incident. Based on this understanding I constructed this test data.

create table dbo.history
(
KeyID int identity,
calldatetime datetime default GetDate(),
crc varchar(10),
projectname char(3),
phonenumber char(10)
)
GO

insert into history values ('2006-01-05 16:25:19.000','OK', 'sss', '8668762739')
insert into history values ('2006-01-05 16:47:22.000','error', 'mmm', '8668762739')
insert into history values ('2006-01-06 16:47:22.000','error', 'mmm', '8668762739')
insert into history values ('2006-01-05 16:53:23.000','OK', 'mmm', '8668762739')
insert into history values ('2006-01-05 17:15:26.000','AMM', 'sss', '8668762739')

This query will do the job.

SELECT T1.CallDateTime,T1.PhoneNumber, T1.ProjectName, T1.crc
FROM dbo.history T1
JOIN (SELECT PhoneNumber, crc, COUNT(*) as anything
FROM dbo.history
where crc = 'error'
GROUP BY PhoneNumber, crc
HAVING COUNT(*) >= 2 ) T2
ON T1.PhoneNumber = T2.PhoneNumber AND T1.crc = T2.crc


CallDateTime PhoneNumber ProjectName crc
-------------------------- ----------- ----------- ----
2006-01-05 16:47:22.000 8668762739 mmm error
2006-01-06 16:47:22.000 8668762739 mmm error

(2 row(s) affected)



Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[SQLBill]

All the subselect does is narrow down the results to those projname that include CRC = 'error'. Your subselect says: These projname are in 'sss' or 'mmm' AND at least two entries have a crc of 'error'. Now take that projname and using it select the following columns:
h.calldatetime, h.crc, h.projname, h.phonenum

Walid's suggestion should work for you with the addition of this after the ON line...

WHERE ProjName IN ('sss' , 'mmm')
  AND (CallDateTime between dateadd(mi, -130, GetDate()) and  Getdate())

-SQLBill

Posting advice: FAQ481-4875

 

[maswien]

try this:

select calldatetime, crc, projname, phonenum
from dbo.history 
where phonenum in 
(
select phonenum
from dbo.history h
group by phonenum
having sum(case crc='error' then 1 else 0 end ) > 1
) and crc='error' and ProjName IN ('sss' , 'mmm')
AND (CallDateTime between dateadd(mi, -130, GetDate()) and  Getdate())
AND PhoneNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'