How to set the Flag for Only one Record

• hegdesuchi

  Hall of Fame

  Points: 3047

  More actions

  April 25, 2016 at 1:02 pm

  #325880

  Hi,

  I have a requirement where I have to check for records and if Record is present for both the values, then Flag is 0 and if it is present for only one value then flag is 1.

  For Example

  ID Value

  1 'Season16'

  2 'Season16'

  2 'Season15'

  3 'Season15'

  3 'Season15'

  4 'Season16'

  Now I want,

  ID Flag

  1 1

  2 0

  3 0

  4 1

  I.e. If ID has both Season16 and Season 15 then Flag is 0 else 1.

  Can you guys pour in your thoughts?

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  April 25, 2016 at 1:36 pm

  #1874363

  As simple as this, or more sophistication required?

  IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL

  DROP TABLE #test;

  CREATE TABLE #test (Id INT, Value VARCHAR(20));

  INSERT #test

  (Id, Value)

  VALUES (1, 'Season 16'),

  (2, 'Season 16'),

  (2, 'Season 15'),

  (3, 'Season 15'),

  (3, 'Season 15'),

  (4, 'Season 16');

  SELECT t.Id

  , Flag = IIF(COUNT(1) > 1, 0, 1)

  FROM #test t

  GROUP BY t.Id;

  By the way, in future, please include set-up code, as above, when posting questions like this.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Con Alexis

  Old Hand

  Points: 331

  More actions

  April 27, 2016 at 11:26 pm

  #1875142

  I notice that you're looking for multiple seasons for same ID

  I.e. If ID has both Season16 and Season 15 then Flag is 0 else 1.

  Since row with id = 3 has both records as "Season 15", you want the flag to be "1"

  Slight modification to Phil's answer. Including "distinct" on the count would be required.

  SELECT t.Id

  , Flag = IIF(COUNT( distinct value) > 1, 0, 1)

  FROM #test t

  GROUP BY t.Id;