please help with the query

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  October 25, 2012 at 8:08 am

  #270192

  i have data like this

  AssetIdFailureIdRootCauseId

  112210141016

  11221019NULL

  112210291016

  11221031NULL

  i want to add a column for 'Not Done' if value of RootCauseId has null value within AssetId group

  the results should be:

  AssetIdFailureIdRootCauseIdIsCompleted

  112210141016 Not Done

  11221019NULL Not Done

  112210291016 Not Done

  11221031NULL Not Done

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 25, 2012 at 8:26 am

  #1552719

  DECLARE @t TABLE(AssetId INT,FailureId INT,RootCauseId INT)

  INSERT INTO @t(AssetId,FailureId,RootCauseId)

  VALUES

  (1122, 1014, 1016),

  (1122, 1019, NULL),

  (1122, 1029, 1016),

  (1122, 1031, NULL);

  SELECT AssetId,FailureId,RootCauseId,

  CASE WHEN SUM(CASE WHEN RootCauseId IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY AssetId) > 0 THEN 'Not Done' ELSE 'Done' END AS IsCompleted

  FROM @t

  ____________________________________________________

  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