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!!!