Query question

• sql_butterfly

  Ten Centuries

  Points: 1264

  More actions

  January 20, 2011 at 11:36 pm

  #97625

  Here is a condition:

  create table T1(Proc1 varchar(20), Proc2 varchar(20), Status varchar(20), Mdate datetime)

  insert into T1 values('AAA', 'A1', 'Stop', '2010-11-10')

  insert into T1 values('AAA', 'A2', 'Stop', '2010-11-10')

  insert into T1 values('AAA', 'A3', 'Running', '2010-11-10')

  insert into T1 values('AAA', 'A1', 'Running', '2010-11-12')

  insert into T1 values('AAA', 'A2', 'Running', '2010-11-12')

  insert into T1 values('AAA', 'A3', 'Completed', '2010-11-12')

  insert into T1 values('AAA', 'A1', 'Completed', '2010-11-13')

  insert into T1 values('AAA', 'A2', 'Completed', '2010-11-13')

  insert into T1 values('AAA', 'A3', 'Stop', '2010-11-13')

  create table T2(Proc1 varchar(20), Proc2 varchar(20), CurrStat varchar(20), PrevStat varchar(20))

  insert into T2 values('AAA', 'A1', NULL, NULL)

  insert into T2 values('AAA', 'A2', NULL, NULL)

  insert into T2 values('AAA', 'A3', NULL, NULL)

  Now i need to write a update statement for T2 to update CurrStat and PrevStat from T1 (stat columns) for that particular Proc1 and proc2. There might be various entries of every pair of Proc1 and Proc2 based on Mdate. The currstat and prevstat should be the status of latest two Mdates in T1.

• January 21, 2011 at 2:00 am

  This was removed by the editor as SPAM

• WayneS

  SSC Guru

  Points: 95460

  More actions

  January 21, 2011 at 6:52 am

  #1276516

  How's this?

  ;

  WITH CTE1 AS

  (

  -- get the row number with 1 being the most recent.

  -- restart numbering at each Proc1/Proc2

  SELECT Proc1, Proc2, [Status], MDate,

  RN = ROW_NUMBER() OVER (PARTITION BY Proc1, Proc2 ORDER BY MDate DESC)

  FROM #T1

  ), CTE2 AS

  (

  -- get an intermediate result set

  SELECT Proc1, Proc2,

  CurrStat = MAX(CASE WHEN RN = 1 THEN [Status] ELSE NULL END),

  PrevStat = MAX(CASE WHEN RN = 2 THEN [Status] ELSE NULL END)

  FROM CTE1

  GROUP BY Proc1, Proc2

  )

  -- now join to the intermediate result set to update the table

  UPDATE #T2

  SET CurrStat = CTE2.CurrStat,

  PrevStat = CTE2.PrevStat

  FROM #T2

  JOIN CTE2

  ON #T2.Proc1 = CTE2.Proc1

  AND #T2.Proc2 = CTE2.Proc2;

  -- and show the results

  SELECT * FROM #T2;

  Edit: FYI: I'm not wild about the column being named STATUS, since this is a sql reserved name. ProcStatus would be better.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2