calculate trend of a column

• Dwain Camps

  SSC Guru

  Points: 86948

  More actions

  August 23, 2012 at 11:54 pm

  #1529561

  As the OP indicated he already has this in a temp table, why not add a column (status) and do it with a quirky update?

  CREATE TABLE #Temp

  (temp_id INT, temp_test_id INT, temp_status VARCHAR(10), status INT)

  DECLARE @status INT = 0, @temp_status VARCHAR(10) = ''

  INSERT INTO #Temp (temp_id, temp_test_id, temp_status)

  select 1, 2, 'failed' union all

  select 2, 2, 'failed' union all

  select 3, 2, 'passed' union all

  select 4, 17, 'failed' union all

  select 5, 17, 'passed' union all

  select 6, 17, 'failed' union all

  select 7, 17, 'failed' union all

  select 8, 17, 'passed'

  UPDATE #Temp

  SET @status = status =

  CASE temp_status

  WHEN 'passed' THEN CASE WHEN @temp_status = 'passed' THEN @status + 1 ELSE 1 END

  ELSE CASE WHEN @temp_status = 'failed' THEN @status - 1 ELSE -1 END END

  ,@temp_status = temp_status

  SELECT * FROM #Temp

  DROP TABLE #Temp

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St