How to convert this XML into columns

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  March 30, 2012 at 10:16 am

  #1467069

  Sean Lange (3/30/2012)

  ---

  What do you have so far?

  Every Value is correct except for the last 3 columns;

  In DATE_CHANGED its giving me '2008-12-28 00:00:00.000'

  In FROM_PAY_RATE its giving me 0

  IN TO_PAY_RATE its giving me 15692.31000

  SO NOTHING THAT MATCHES THE XML FILE.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  March 30, 2012 at 10:18 am

  #1467070

  The query I provided should be a good starting point. Show me what you have tried.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  March 30, 2012 at 10:41 am

  #1467084

  Sean Lange (3/30/2012)

  ---

  The query I provided should be a good starting point. Show me what you have tried.

  One problem with your query is that its giving me:

  Msg 208, Level 16, State 0, Line 71

  Invalid object name '#EMPLOYEE2'.

  Here is where I inserted your query:

  -- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')

  CREATE TABLE #EMPLOYEE2

  (

  EMP_ID INT,

  EMP_NAME VARCHAR(MAX),

  EMP_ORGANIZATION VARCHAR(MAX),

  EMP_DEPARTMENT VARCHAR(MAX),

  EMP_DT_TERMINATION DATETIME

  )

  CREATE TABLE #EMPLOYEE_OTHER2

  (

  EMP_ID INT,

  RATE_HISTORY XML

  )

  INSERT INTO #EMPLOYEE2

  (

  EMP_ID,

  EMP_NAME,

  EMP_ORGANIZATION,

  EMP_DEPARTMENT,

  EMP_DT_TERMINATION

  )

  (SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

  )

  INSERT INTO #EMPLOYEE_OTHER2

  (

  EMP_ID,

  RATE_HISTORY

  )

  (SELECT '3' , '<Rates>

  <Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '5' ,

  '<Rates>

  <Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '6' ,

  '<Rates>

  <Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '7' ,

  '<Rates>

  <Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  )

  SELECT * FROM #EMPLOYEE2

  select * from #EMPLOYEE_OTHER2

  drop table #EMPLOYEE2

  drop table #EMPLOYEE_OTHER2

  DECLARE @x XML;

  select * from

  (

  SELECT

  (ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  e.EMP_ID,

  e.EMP_NAME,

  e.EMP_ORGANIZATION,

  e.EMP_DEPARTMENT,

  e.EMP_DT_TERMINATION,

  x.r.value('@date_changed','DATETIME') AS date_changed,

  x.r.value('@not_active_since','DATETIME') AS not_active_since,

  x.r.value('@rate','DECIMAL(10,5)') AS rate,

  x.r.value('@emp_pay_type','INT') AS emp_pay_type,

  x.r.value('@emp_work_hours','DECIMAL(10,5)') AS emp_work_hours,

  x.r.value('@rate2','DECIMAL(10,5)') AS rate2,

  x.r.value('@rate3','DECIMAL(10,5)') AS rate3,

  x.r.value('@benefit_hours','DECIMAL(10,5)') AS benefit_hours,

  x.r.value('@exempt_ind','CHAR(1)') AS exempt_ind

  FROM #EMPLOYEE2 e

  INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

  CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

  ) x where RowNum = 1

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  March 30, 2012 at 10:47 am

  #1467089

  njdevils39 (3/30/2012)

  ---

  Sean Lange (3/30/2012)

  ---

  The query I provided should be a good starting point. Show me what you have tried.

  One problem with your query is that its giving me:

  Msg 208, Level 16, State 0, Line 71

  Invalid object name '#EMPLOYEE2'.

  Here is where I inserted your query:

  -- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')

  CREATE TABLE #EMPLOYEE2

  (

  EMP_ID INT,

  EMP_NAME VARCHAR(MAX),

  EMP_ORGANIZATION VARCHAR(MAX),

  EMP_DEPARTMENT VARCHAR(MAX),

  EMP_DT_TERMINATION DATETIME

  )

  CREATE TABLE #EMPLOYEE_OTHER2

  (

  EMP_ID INT,

  RATE_HISTORY XML

  )

  INSERT INTO #EMPLOYEE2

  (

  EMP_ID,

  EMP_NAME,

  EMP_ORGANIZATION,

  EMP_DEPARTMENT,

  EMP_DT_TERMINATION

  )

  (SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

  )

  INSERT INTO #EMPLOYEE_OTHER2

  (

  EMP_ID,

  RATE_HISTORY

  )

  (SELECT '3' , '<Rates>

  <Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '5' ,

  '<Rates>

  <Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '6' ,

  '<Rates>

  <Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '7' ,

  '<Rates>

  <Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  )

  SELECT * FROM #EMPLOYEE2

  select * from #EMPLOYEE_OTHER2

  drop table #EMPLOYEE2

  drop table #EMPLOYEE_OTHER2

  DECLARE @x XML;

  select * from

  (

  SELECT

  (ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  e.EMP_ID,

  e.EMP_NAME,

  e.EMP_ORGANIZATION,

  e.EMP_DEPARTMENT,

  e.EMP_DT_TERMINATION,

  x.r.value('@date_changed','DATETIME') AS date_changed,

  x.r.value('@not_active_since','DATETIME') AS not_active_since,

  x.r.value('@rate','DECIMAL(10,5)') AS rate,

  x.r.value('@emp_pay_type','INT') AS emp_pay_type,

  x.r.value('@emp_work_hours','DECIMAL(10,5)') AS emp_work_hours,

  x.r.value('@rate2','DECIMAL(10,5)') AS rate2,

  x.r.value('@rate3','DECIMAL(10,5)') AS rate3,

  x.r.value('@benefit_hours','DECIMAL(10,5)') AS benefit_hours,

  x.r.value('@exempt_ind','CHAR(1)') AS exempt_ind

  FROM #EMPLOYEE2 e

  INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

  CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

  ) x where RowNum = 1

  That is because you have code referencing that table after it is dropped. Right before you declare your @x variable

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  March 30, 2012 at 1:54 pm

  #1467173

  Sean Lange (3/30/2012)

  ---

  njdevils39 (3/30/2012)

  ---

  Sean Lange (3/30/2012)

  ---

  The query I provided should be a good starting point. Show me what you have tried.

  One problem with your query is that its giving me:

  Msg 208, Level 16, State 0, Line 71

  Invalid object name '#EMPLOYEE2'.

  Here is where I inserted your query:

  -- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')

  CREATE TABLE #EMPLOYEE2

  (

  EMP_ID INT,

  EMP_NAME VARCHAR(MAX),

  EMP_ORGANIZATION VARCHAR(MAX),

  EMP_DEPARTMENT VARCHAR(MAX),

  EMP_DT_TERMINATION DATETIME

  )

  CREATE TABLE #EMPLOYEE_OTHER2

  (

  EMP_ID INT,

  RATE_HISTORY XML

  )

  INSERT INTO #EMPLOYEE2

  (

  EMP_ID,

  EMP_NAME,

  EMP_ORGANIZATION,

  EMP_DEPARTMENT,

  EMP_DT_TERMINATION

  )

  (SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

  )

  INSERT INTO #EMPLOYEE_OTHER2

  (

  EMP_ID,

  RATE_HISTORY

  )

  (SELECT '3' , '<Rates>

  <Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '5' ,

  '<Rates>

  <Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '6' ,

  '<Rates>

  <Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '7' ,

  '<Rates>

  <Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  )

  SELECT * FROM #EMPLOYEE2

  select * from #EMPLOYEE_OTHER2

  drop table #EMPLOYEE2

  drop table #EMPLOYEE_OTHER2

  DECLARE @x XML;

  select * from

  (

  SELECT

  (ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  e.EMP_ID,

  e.EMP_NAME,

  e.EMP_ORGANIZATION,

  e.EMP_DEPARTMENT,

  e.EMP_DT_TERMINATION,

  x.r.value('@date_changed','DATETIME') AS date_changed,

  x.r.value('@not_active_since','DATETIME') AS not_active_since,

  x.r.value('@rate','DECIMAL(10,5)') AS rate,

  x.r.value('@emp_pay_type','INT') AS emp_pay_type,

  x.r.value('@emp_work_hours','DECIMAL(10,5)') AS emp_work_hours,

  x.r.value('@rate2','DECIMAL(10,5)') AS rate2,

  x.r.value('@rate3','DECIMAL(10,5)') AS rate3,

  x.r.value('@benefit_hours','DECIMAL(10,5)') AS benefit_hours,

  x.r.value('@exempt_ind','CHAR(1)') AS exempt_ind

  FROM #EMPLOYEE2 e

  INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

  CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

  ) x where RowNum = 1

  That is because you have code referencing that table after it is dropped. Right before you declare your @x variable

  Ok, the date works with this query, now I need to be able to match the FROM_PAY_RATE rate column with the last row and The To_Pay_RATE column to match the 1st row.

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  March 30, 2012 at 2:37 pm

  #1467192

  This is what I have so far:

  -- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')

  CREATE TABLE #EMPLOYEE2

  (

  EMP_ID INT,

  EMP_NAME VARCHAR(MAX),

  EMP_ORGANIZATION VARCHAR(MAX),

  EMP_DEPARTMENT VARCHAR(MAX),

  EMP_DT_TERMINATION DATETIME

  )

  CREATE TABLE #EMPLOYEE_OTHER2

  (

  EMP_ID INT,

  RATE_HISTORY XML

  )

  INSERT INTO #EMPLOYEE2

  (

  EMP_ID,

  EMP_NAME,

  EMP_ORGANIZATION,

  EMP_DEPARTMENT,

  EMP_DT_TERMINATION

  )

  (SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

  )

  INSERT INTO #EMPLOYEE_OTHER2

  (

  EMP_ID,

  RATE_HISTORY

  )

  (SELECT '3' , '<Rates>

  <Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '5' ,

  '<Rates>

  <Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '6' ,

  '<Rates>

  <Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '7' ,

  '<Rates>

  <Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  )

  SELECT * FROM #EMPLOYEE2

  select * from #EMPLOYEE_OTHER2

  DECLARE @x XML;

  select * from

  (

  SELECT

  (ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  e.EMP_ID,

  e.EMP_NAME,

  e.EMP_ORGANIZATION,

  e.EMP_DEPARTMENT,

  e.EMP_DT_TERMINATION,

  convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,

  x.r.value('@rate','DECIMAL(10,5)') AS TO_PAY_RATE

  FROM #EMPLOYEE2 e

  INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

  CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

  ) x where RowNum = 1

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  March 30, 2012 at 3:04 pm

  #1467213

  What you have so far is the work that other people have given you. What have YOU tried? There are a couple ways to solve this. Either a self join to derived table from the above query joining on RowNum or a CTE. We are not getting paid for this and it just seems like you want us to hand you the solution with little to no effort on your part.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  March 30, 2012 at 3:18 pm

  #1467220

  I am only trying to figure out how to get the FROM_PAY_RATE column. Like the RowNUm has to be 1 and the From_Pay_RATE has to match the Final RowNum.

  So min(convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) value has to correspond to the initial and starting rate.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  March 30, 2012 at 8:48 pm

  #1467264

  I suggested a couple of options in my previous post. What happened when you tried either of those ideas?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  April 6, 2012 at 9:41 am

  #1470159

  Sean Lange (3/30/2012)

  ---

  I suggested a couple of options in my previous post. What happened when you tried either of those ideas?

  Here is my latest code, but still not the result I want in the From_PAY_Rate column. I need the rate to match the final row the XML format.

  -- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')

  CREATE TABLE #EMPLOYEE2

  (

  EMP_ID INT,

  EMP_NAME VARCHAR(MAX),

  EMP_ORGANIZATION VARCHAR(MAX),

  EMP_DEPARTMENT VARCHAR(MAX),

  EMP_DT_TERMINATION DATETIME

  )

  CREATE TABLE #EMPLOYEE_OTHER2

  (

  EMP_ID INT,

  RATE_HISTORY XML

  )

  INSERT INTO #EMPLOYEE2

  (

  EMP_ID,

  EMP_NAME,

  EMP_ORGANIZATION,

  EMP_DEPARTMENT,

  EMP_DT_TERMINATION

  )

  (SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

  )

  INSERT INTO #EMPLOYEE_OTHER2

  (

  EMP_ID,

  RATE_HISTORY

  )

  (SELECT '3' , '<Rates>

  <Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '5' ,

  '<Rates>

  <Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '6' ,

  '<Rates>

  <Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '7' ,

  '<Rates>

  <Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  )

  SELECT * FROM #EMPLOYEE2

  select * from #EMPLOYEE_OTHER2

  DECLARE @x XML;

  select * from

  (

  SELECT

  (ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  e.EMP_ID,

  e.EMP_NAME,

  e.EMP_ORGANIZATION,

  e.EMP_DEPARTMENT,

  e.EMP_DT_TERMINATION,

  convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,

  x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE where x.r.value('@date_changed','DATETIME') = min(x.r.value('@date_changed','DATETIME'))

  x.r.value('@rate','DECIMAL(10,5)') AS TO_PAY_RATE

  FROM #EMPLOYEE2 e

  INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

  CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

  ) x where RowNum = 1

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 6, 2012 at 10:10 am

  #1470175

  That won't run. There is a where clause stuck in there that I can't figure out what you are trying to with.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  April 6, 2012 at 10:26 am

  #1470183

  Sean Lange (4/6/2012)

  ---

  That won't run. There is a where clause stuck in there that I can't figure out what you are trying to with.

  Some XML Files have only one row and some have multiple rows, but I need the FROM_PAY_RATE to match the last row in the XML Field so RowNum has to equal 1 where the XML code is only one row or the last row where the XML has multiple rows. Here is the code without the Where clause.

  -- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')

  CREATE TABLE #EMPLOYEE2

  (

  EMP_ID INT,

  EMP_NAME VARCHAR(MAX),

  EMP_ORGANIZATION VARCHAR(MAX),

  EMP_DEPARTMENT VARCHAR(MAX),

  EMP_DT_TERMINATION DATETIME

  )

  CREATE TABLE #EMPLOYEE_OTHER2

  (

  EMP_ID INT,

  RATE_HISTORY XML

  )

  INSERT INTO #EMPLOYEE2

  (

  EMP_ID,

  EMP_NAME,

  EMP_ORGANIZATION,

  EMP_DEPARTMENT,

  EMP_DT_TERMINATION

  )

  (SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL

  SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL

  )

  INSERT INTO #EMPLOYEE_OTHER2

  (

  EMP_ID,

  RATE_HISTORY

  )

  (SELECT '3' , '<Rates>

  <Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />

  <Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '5' ,

  '<Rates>

  <Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  <Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '6' ,

  '<Rates>

  <Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  UNION ALL

  SELECT '7' ,

  '<Rates>

  <Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />

  </Rates>'

  )

  SELECT * FROM #EMPLOYEE2

  select * from #EMPLOYEE_OTHER2

  DECLARE @x XML;

  select * from

  (

  SELECT

  (ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  e.EMP_ID,

  e.EMP_NAME,

  e.EMP_ORGANIZATION,

  e.EMP_DEPARTMENT,

  e.EMP_DT_TERMINATION,

  convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,

  x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE,

  x.r.value('@rate','DECIMAL(10,5)') AS TO_PAY_RATE

  FROM #EMPLOYEE2 e

  INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID

  CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)

  ) x where RowNum = 1

• LutzM

  SSC Guru

  Points: 107049

  More actions

  April 6, 2012 at 10:32 am

  #1470185

  Are you looking for something like this?

  WITH cteRates AS

  (

  SELECT

  o.EMP_ID,

  (ROW_NUMBER() over(partition by o.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,

  x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE

  FROM #EMPLOYEE_OTHER2 o

  CROSS APPLY o.rate_history.nodes('/Rates/Rate') AS x(r)

  )

  SELECT

  e.*,

  c1.Emp_id,

  c1.DATE_CHANGED,

  c2.FROM_PAY_RATE AS fromRate,

  c1.FROM_PAY_RATE AS ToRate

  FROM #EMPLOYEE2 e

  INNER JOIN cteRates c1 ON e.EMP_ID=c1.EMP_ID

  INNER JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2

  If so, then this query is the result of plain guessing.

  After reading those 100(!!!!) posts I couldn't find a single hint that you might look for the most recent value of @rate and the value before. Especially your sample data did "help" to expand the confusion since you requested 1730.77 and 1730.77.

  Those values can be returned by duplicte output of @rate or the latest @rate value and the one before or the first and the last @rate value.

  It's the same like asking:

  "Give me the correct equation how to get a result of 6 if I have three equal values (2)."

  It can be

  2+2+2= 6

  or (2*2)+2= 6

  or (2^2)+2= 6

  @sean: I guess you're qualified to cover for St. Lynn 😀

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• njdevils39

  SSCrazy

  Points: 2676

  More actions

  April 6, 2012 at 10:44 am

  #1470189

  LutzM (4/6/2012)

  ---

  Are you looking for something like this?

  WITH cteRates AS

  (

  SELECT

  o.EMP_ID,

  (ROW_NUMBER() over(partition by o.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,

  x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE

  FROM #EMPLOYEE_OTHER2 o

  CROSS APPLY o.rate_history.nodes('/Rates/Rate') AS x(r)

  )

  SELECT

  e.*,

  c1.Emp_id,

  c1.DATE_CHANGED,

  c2.FROM_PAY_RATE AS fromRate,

  c1.FROM_PAY_RATE AS ToRate

  FROM #EMPLOYEE2 e

  INNER JOIN cteRates c1 ON e.EMP_ID=c1.EMP_ID

  INNER JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2

  If so, then this query is the result of plain guessing.

  After reading those 100(!!!!) posts I couldn't find a single hint that you might look for the most recent value of @rate and the value before. Especially your sample data did "help" to expand the confusion since you requested 1730.77 and 1730.77.

  Those values can be returned by duplicte output of @rate or the latest @rate value and the one before or the first and the last @rate value.

  It's the same like asking:

  "Give me the correct equation how to get a result of 6 if I have three equal values (2)."

  It can be

  2+2+2= 6

  or (2*2)+2= 6

  or (2^2)+2= 6

  @sean: I guess you're qualified to cover for St. Lynn 😀

  Where should I put this query in my original SQL CODE? Note: Include EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT and EMP_DT_TERMINATION.

• CapnHector

  SSCoach

  Points: 16143

  More actions

  April 6, 2012 at 10:49 am

  #1470193

  njdevils39 (4/6/2012)

  ---

  LutzM (4/6/2012)

  ---

  Are you looking for something like this?

  WITH cteRates AS

  (

  SELECT

  o.EMP_ID,

  (ROW_NUMBER() over(partition by o.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

  convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,

  x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE

  FROM #EMPLOYEE_OTHER2 o

  CROSS APPLY o.rate_history.nodes('/Rates/Rate') AS x(r)

  )

  SELECT

  e.*,

  c1.Emp_id,

  c1.DATE_CHANGED,

  c2.FROM_PAY_RATE AS fromRate,

  c1.FROM_PAY_RATE AS ToRate

  FROM #EMPLOYEE2 e

  INNER JOIN cteRates c1 ON e.EMP_ID=c1.EMP_ID

  INNER JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2

  If so, then this query is the result of plain guessing.

  After reading those 100(!!!!) posts I couldn't find a single hint that you might look for the most recent value of @rate and the value before. Especially your sample data did "help" to expand the confusion since you requested 1730.77 and 1730.77.

  Those values can be returned by duplicte output of @rate or the latest @rate value and the one before or the first and the last @rate value.

  It's the same like asking:

  "Give me the correct equation how to get a result of 6 if I have three equal values (2)."

  It can be

  2+2+2= 6

  or (2*2)+2= 6

  or (2^2)+2= 6

  @sean: I guess you're qualified to cover for St. Lynn 😀

  Where should I put this query in my original SQL CODE? Note: Include EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT and EMP_DT_TERMINATION.

  seriously? after 100 posts of frustrating the volunteers you need us to hand it to you? i can bairly follow this thread and i have been through it 4 times.

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]