Can't get this darn thing to work!

I have a query where I'm trying to find all ProjectId's where a certain condition is met... The condition is that two records in a table are not in the correct sequence. In the results below, PREXTERNALID: APX04 should be 65 and is not in the correct sequence.

If I query the db for a single project, the results looks like this:
PREXTERNALID--PRID--PRPROJECTID--PRWBSSEQUENCE--PRWBSLEVEL
---4---- 5072393--5005958------64--------1
--APX04--5706280--5005958------63--------2

So, to find out where this condition is true I wrote the following query to find where the value in PRWBSSEQUENCE for PREXTERNALID = APX04 is < the value in PRWBSSEQUENCE for PREXTERNALID = 4

SELECT PRPROJECTID FROM niku.PRTASK GROUP By PRPROJECTID
HAVING((
(SELECT PRWBSSEQUENCE
FROM niku.PRTASK
WHERE (CONVERT(Char(5),PREXTERNALID) = 'APX04') AND (PRPROJECTID = 5005958)
Group By PRPROJECTID,PRWBSSEQUENCE)
-
(SELECT PRWBSSEQUENCE
FROM niku.PRTASK
WHERE ((PREXTERNALID = '4.00') OR (PREXTERNALID = '4')) AND (PRPROJECTID = 5005958)
Group By PRPROJECTID,PRWBSSEQUENCE)
) < 0 AND (PRPROJECTID = 5005958))

If I don't include PRPROJECTID = 5005958 the query returns every record from niku.PRTASK table whether or not the condition is met. If I include the project id it will return the correct result but I want to find ALL that meet the condition in the table.

I'm seriously tearing my hair out over this one! Any help would be greatly appriciated!!!

Quote:

Originally Posted by papaparsons

I have a query where I'm trying to find all ProjectId's where a certain condition is met... The condition is that two records in a table are not in the correct sequence. In the results below, PREXTERNALID: APX04 should be 65 and is not in the correct sequence.

If I query the db for a single project, the results looks like this:
PREXTERNALID--PRID--PRPROJECTID--PRWBSSEQUENCE--PRWBSLEVEL
---4---- 5072393--5005958------64--------1
--APX04--5706280--5005958------63--------2

So, to find out where this condition is true I wrote the following query to find where the value in PRWBSSEQUENCE for PREXTERNALID = APX04 is < the value in PRWBSSEQUENCE for PREXTERNALID = 4

SELECT PRPROJECTID FROM niku.PRTASK GROUP By PRPROJECTID
HAVING((
(SELECT PRWBSSEQUENCE
FROM niku.PRTASK
WHERE (CONVERT(Char(5),PREXTERNALID) = 'APX04') AND (PRPROJECTID = 5005958)
Group By PRPROJECTID,PRWBSSEQUENCE)
-
(SELECT PRWBSSEQUENCE
FROM niku.PRTASK
WHERE ((PREXTERNALID = '4.00') OR (PREXTERNALID = '4')) AND (PRPROJECTID = 5005958)
Group By PRPROJECTID,PRWBSSEQUENCE)
) < 0 AND (PRPROJECTID = 5005958))

If I don't include PRPROJECTID = 5005958 the query returns every record from niku.PRTASK table whether or not the condition is met. If I include the project id it will return the correct result but I want to find ALL that meet the condition in the table.

I'm seriously tearing my hair out over this one! Any help would be greatly appriciated!!!

do you reset count everytime PRPROJECTID change? or PRWBSSEQUENCE is sequential, like a record number?|||

Quote:

Originally Posted by ck9663

do you reset count everytime PRPROJECTID change? or PRWBSSEQUENCE is sequential, like a record number?

No I don't think I do... I think what's wrong with the query is that it's not checking the "Having" clause for every record in PRTASK individually and returning those where the condition is met. What it is doing, is returning all records from the PRTASK table if the condition is met by any record in the table. What I need it to do, is return just the records where the condition is met. I hope that makes sense...|||

Quote:

Originally Posted by papaparsons

No I don't think I do... I think what's wrong with the query is that it's not checking the "Having" clause for every record in PRTASK individually and returning those where the condition is met. What it is doing, is returning all records from the PRTASK table if the condition is met by any record in the table. What I need it to do, is return just the records where the condition is met. I hope that makes sense...

I should have put this in there to show you what I think should be working but doesn't...

SELECT PRPROJECTID FROM niku.PRTASK GROUP By PRPROJECTID
HAVING((
(SELECT PRWBSSEQUENCE
FROM niku.PRTASK
WHERE (CONVERT(Char(5),PREXTERNALID) = 'APX04')
Group By PRPROJECTID,PRWBSSEQUENCE)
-
(SELECT PRWBSSEQUENCE
FROM niku.PRTASK
WHERE ((PREXTERNALID = '4.00') OR (PREXTERNALID = '4'))
Group By PRPROJECTID,PRWBSSEQUENCE)
) < 0 )

This query returns all records from PRTASK if the condition is met by any record in the table.