ignore row if one of the Rcodes is null

[CrystalProgDev]

I have a data set

Num RCode Date Code MaxCode
12 'R1' '12/1/2010' 9 10
12 NULL '12/1/2010' 10 10
13 'R8' '12/1/2010' 11 11
13 NULL '12/1/2010' 8 11
14 'S1' '10/1/2010' 11 12
14 'S2' '10/1/2010' 12 12
15 'A1' '5/1/2011' 10 13
15 'A1' '5/1/2011' 10 13
16 'P1' '4/1/2011' 8 13
16 'NULL '4/1/2011' 8 13
17 'NULL '1/1/2011' 3 3
18 'S7' '1/1/2011' 3 9
19 'S1' '1/1/2011' 9 9
19 'S2' '1/1/2011' 9 9

I need below as output...

Num RCode Date Code MaxCode
12 R1 12/1/2010 9 10
13 R8 12/1/2010 11 11
14 S1 10/1/2010 11 12
14 S2 10/1/2010 12 12
15 A1 5/1/2011 10 13
15 A1 5/1/2011 10 13
16 P1 4/1/2011 8 13
17 NULL 1/1/2011 3 3
18 S7 1/1/2011 3 9
19 S1 1/1/2011 9 9
19 S2 1/1/2011 9 9

Can any one help me.

Thank you

 

[bborissov]

SELECT * FROM YourTable WHERE RCode IS NOT NULL

?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[CrystalProgDev]

No it won't work... when the count of Num group by Num >=2 and the one of the Rcodes is Not Null.
and if count (Num )=1 irrespective of rcode value I should get that value.

for example.. 17 has 1 record with null value. I need that data.
16,12,13 have 2 records with one not null value. and also I need to consider
the below scenario.... for num 19 - if count(Num) >=2 and both RCodes are null.. I need Null as out put

here is the data set that I have

12 'R1' '12/1/2010' 9 10
12 NULL '12/1/2010' 10 10
13 'R8' '12/1/2010' 11 11
13 NULL '12/1/2010' 8 11
14 'S1' '10/1/2010' 11 12
14 'S2' '10/1/2010' 12 12
16 'P1' '4/1/2011' 8 13
16 'NULL '4/1/2011' 8 13
17 'NULL '1/1/2011' 3 3
18 'S7' '1/1/2011' 3 9
19 NULL 1/12011 1 2
19 NULL 1/1/2011 2 2

I need Output as
12 'R1' '12/1/2010' 9 10
13 'R8' '12/1/2010' 11 11
14 'S1' '10/1/2010' 11 12
14 'S2' '10/1/2010' 12 12
16 'P1' '4/1/2011' 8 13
17 'NULL '1/1/2011' 3 3
18 'S7' '1/1/2011' 3 9
19 NULL 1/12011 2 2

 

[sam4help]

Please check this if helps

SELECT     Num, RCode, Date, Code, MaxCode
FROM         tableName where RCode is not null or Num in (select Num from Test where Rcode is null group by Num 
 having count(Num)=1 and Num not in (select Num from Test group by Num 
 having count(Num)>1 ))

Best Wishes,
Sam

 

[bborissov]

SELECT YourTable.*
FROM YourTable 
LEFT JOIN (SELECT DISTINCT Num WHERE RCode IS NOT NULL)  Tbl1
     ON YourTable.Num = Tbl1.Num
WHERE YourTable.RCode IS NOT NULL OR
      Tbl1.Num IS NULL

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[sam4help]

I just saw the changes in your requirement you mentioned in your next reply so in that case;

SELECT     Num, RCode, Date, Code, MaxCode
FROM         yourTableName where RCode is not null or  Num in (select Num from yourTableName where Rcode is null group by Num 
 having count(Num)=1 and Num not in (select Num from yourTableName group by Num 
 having count(Num)>1 )) or Num in (select distinct(Num) from yourTableName where rcode is  null and Num not in (
select distinct(Num) from yourTableName where Num not in (select distinct(Num) from yourTableName where Num not in 
(select Num from yourTableName where Rcode is not null)))) group by Num,rcode,Date, Code, MaxCode

Best Regards,

Sam

 

[sam4help]

bborissov, your code works great a far far better way
just a small correction in your code

SELECT test.*
FROM TEST
LEFT JOIN (SELECT DISTINCT Num from test WHERE RCode IS NOT NULL )  Tbl1
     ON test.Num = Tbl1.Num
WHERE test.RCode IS NOT NULL OR
      Tbl1.Num IS NULL

* where test is yourTableName

 

[CrystalProgDev]

bborissov, your code works as expected
except one scenario... If both the rcodes are null (for 19) it still showing two records... I need one record only with Code 2 and MaxCode 2.

 

[bborissov]

Why this?
What is the logic?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[CrystalProgDev]

I need MaxDate, code on maxdate and the rcode (previously entered rcode if the rcode for max(Code) is null.

and if all the rcodes for a num are null.. then null and max(Code), MaxDate

 

[bborissov]

And what if you have equal Code values?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[CrystalProgDev]

Both has to display

 

[bborissov]

Sorry for the deley:

DECLARE @test TABLE (Num int, RCode CHAR(2) NULL, Date datetime NULL, Code int, MaxCode int)
INSERT INTO @Test VALUES(12, 'R1', '12/1/2010', 9, 10)
INSERT INTO @Test VALUES(12, NULL, '12/1/2010', 10, 10)
INSERT INTO @Test VALUES(13, 'R8', '12/1/2010', 11, 11)
INSERT INTO @Test VALUES(13, NULL, '12/1/2010', 8 ,11)
INSERT INTO @Test VALUES(14, 'S1', '10/1/2010', 11, 12)
INSERT INTO @Test VALUES(14, 'S2', '10/1/2010', 12, 12)
INSERT INTO @Test VALUES(16, 'P1', '4/1/2011', 8, 13)
INSERT INTO @Test VALUES(16, NULL, '4/1/2011', 8, 13)
INSERT INTO @Test VALUES(17, NULL, '1/1/2011', 3, 3)
INSERT INTO @Test VALUES(18, 'S7', '1/1/2011', 3, 9)
INSERT INTO @Test VALUES(19, NULL, '1/1/2011', 1, 2)
INSERT INTO @Test VALUES(19, NULL, '1/1/2011', 2, 2)


SELECT test.*
FROM @TEST Test
LEFT JOIN (SELECT DISTINCT Num 
                  from @test
           WHERE RCode IS NOT NULL )  Tbl1
     ON test.Num = Tbl1.Num
LEFT JOIN (SELECT DISTINCT NUM, MAX(Code) AS Code
                  from @test
           WHERE RCode IS NULL
           GROUP BY Num)  Tbl2
     ON test.Num = Tbl2.Num
WHERE test.RCode IS NOT NULL OR
      (Tbl1.Num   IS NULL AND
       Tbl2.Code = Test.Code)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[CrystalProgDev]

It is working... Thank you for the solution. Thanks alot