case statements

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  September 15, 2010 at 3:13 am

  #226884

  Hi,

  have a table with two columns where id and its status has been mentioned.

  declare @Tmp table (IDint, STATUSint )

  insert into @Tmp

  select 191,6 union all

  select 192,5 union all

  select 192,7 union all

  select 193,6 union all

  select 194, 5 union all

  select 194, 5

  select id

  , case when status = 5 or status =7 then 'ordered'

  when status =6 then 'not confirmed'

  else 'ordered'

  end status from @Tmp

  when the same ids are having both 5 and 7 then i should get 'ordered' when it is having only 5 then i should get confirmed.

  when the same ids are having 6 then i should get 'not confirmed'

  here for the id 194 i should get 'confirmed'

  thanks,

  ami

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 15, 2010 at 3:20 am

  #1221199

  SELECT DISTINCT

  ID,

  CASE

  WHEN EXISTS (SELECT 1 FROM @Tmp sub WHERE sub.id = t.ID AND Status = 5)

  AND EXISTS (SELECT 1 FROM @Tmp sub WHERE sub.id = t.ID AND Status = 7)

  THEN 'Ordered'

  WHEN Status = 5

  AND NOT EXISTS (SELECT 1 FROM @Tmp sub WHERE sub.id = t.ID AND Status = 7)

  THEN 'Confirmed'

  WHEN Status = 6

  THEN 'Not Confirmed'

  ELSE 'Unknown'

  END AS OrderStatus

  FROM @Tmp t

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 15, 2010 at 3:34 am

  #1221202

  WITH Summary AS (

  SELECT ID,

  SUM(CASE WHEN status=5 THEN 1 ELSE 0 END) AS Fives,

  SUM(CASE WHEN status=6 THEN 1 ELSE 0 END) AS Sixes,

  SUM(CASE WHEN status=7 THEN 1 ELSE 0 END) AS Sevens,

  COUNT(*) AS Total

  FROM @Tmp

  GROUP BY ID)

  SELECT ID,

  CASE WHEN Fives>0 AND Sevens>0 THEN 'ordered'

  WHEN Fives=Total THEN 'confirmed'

  WHEN Sixes>0 THEN 'not confirmed'

  END AS Status

  FROM Summary;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 15, 2010 at 4:04 am

  #1221209

  bitmask "nerd-kind" variation :-D:

  ;with stbb

  as ( SELECT 5 AS Status, 1 AS BitMask UNION

  SELECT 6 AS Status, 2 AS BitMask UNION

  SELECT 7 AS Status, 4 AS BitMask )

  ,tbm

  as ( select id, sum(distinct stbb.BitMask) StBM

  from @Tmp t join stbb on t.status = stbb.status

  group by id )

  select id, case when StBM & 2 = 2 then 'not confirmed'

  when StBM & 1 = 1 and StBM & 4 = 4 then 'ordered'

  when StBM & 1 = 1 then 'confirmed'

  else 'unknown'

  end Status

  from tbm

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]