Are you aware of this possible combination of values?

WITH SomeTable AS(

SELECT CAST( '20141209' AS DATE) AS date,

'SomeClass' AS classification,

1 AS field,

1 AS somefield

),

AnotherTable AS(

SELECT 1 AS somefield, 1 AS otherfield UNION ALL

SELECT 1 AS somefield, 0 AS otherfield

)

SELECT

date,

classification,

MAX(

CASE

WHEN X.field = 1 AND Y.otherfield = 1 THEN 'New'

WHEN X.field = 1 AND Y.otherfield = 0 THEN 'Old'

WHEN X.field = 0 AND Y.otherfield = 1 THEN 'Foo'

END

) AS 'Status'

FROM

SomeTable X

JOIN

AnotherTable Y ON X.someField = Y.someField

GROUP BY

date,

classification;

It's easy to define priorities.

WITH SomeTable AS(

SELECT CAST( '20141209' AS DATE) AS date,

'SomeClass' AS classification,

1 AS field,

1 AS somefield

),

AnotherTable AS(

SELECT 1 AS somefield, 1 AS otherfield UNION ALL

SELECT 1 AS somefield, 0 AS otherfield

)

SELECT

date,

classification,

STUFF(MAX(

CASE

WHEN X.field = 1 AND Y.otherfield = 1 THEN '3New'

WHEN X.field = 1 AND Y.otherfield = 0 THEN '2Old'

WHEN X.field = 0 AND Y.otherfield = 1 THEN '1Foo'

END

),1,1,'') AS 'Status'

FROM

SomeTable X

JOIN

AnotherTable Y ON X.someField = Y.someField

GROUP BY

date,

classification;