STDEVP help

• dwilliscp

  SSCrazy Eights

  Points: 9270

  More actions

  May 28, 2013 at 2:39 pm

  #274224

  If you have a record, where there are 12 columns, one for each of the last 12 months, and you want to use STDEVP, how would you go about it? Since the data is in different columns? Create a CTE and load the data?

  David

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  May 28, 2013 at 2:52 pm

  #1619121

  dwilliscp (5/28/2013)

  ---

  If you have a record, where there are 12 columns, one for each of the last 12 months, and you want to use STDEVP, how would you go about it? Since the data is in different columns? Create a CTE and load the data?

  David

  I would first see if it is possible to normalize this data. That sounds like a nightmare to deal with. Each month do you have to update all 12 columns so the last 12 months are in the "correct" column?

  If you can't normalize it than I would create a CTE or maybe a view to normalize the data so you can use it.

  select ID, Col1 as MyValue from MyTable union all

  select ID, Col2 from MyTable union all

  select ID, Col3 from MyTable union all

  select ID, Col4 from MyTable union all

  select ID, Col5 from MyTable union all

  select ID, Col6 from MyTable union all

  select ID, Col7 from MyTable union all

  select ID, Col8 from MyTable union all

  select ID, Col9 from MyTable union all

  select ID, Col10 from MyTable union all

  select ID, Col11 from MyTable union all

  select ID, Col12 from MyTable

  _______________________________________________________________

  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/

• dwilliscp

  SSCrazy Eights

  Points: 9270

  More actions

  May 28, 2013 at 3:12 pm

  #1619125

  I am accessing a 3rd party's database to get the data, and they store it that way... They create a generated key to link various parts of the data... shipments, realinged shipments, ect. They are all stored that way though. I guess I could try and pull it out, into new tables, but that seems like a bit of an overkill.. unless this view ends up runing slow.

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  May 28, 2013 at 4:01 pm

  #1619136

  Sean Lange (5/28/2013)

  ---

  dwilliscp (5/28/2013)

  ---

  If you have a record, where there are 12 columns, one for each of the last 12 months, and you want to use STDEVP, how would you go about it? Since the data is in different columns? Create a CTE and load the data?

  David

  I would first see if it is possible to normalize this data. That sounds like a nightmare to deal with. Each month do you have to update all 12 columns so the last 12 months are in the "correct" column?

  If you can't normalize it than I would create a CTE or maybe a view to normalize the data so you can use it.

  select ID, Col1 as MyValue from MyTable union all

  select ID, Col2 from MyTable union all

  select ID, Col3 from MyTable union all

  select ID, Col4 from MyTable union all

  select ID, Col5 from MyTable union all

  select ID, Col6 from MyTable union all

  select ID, Col7 from MyTable union all

  select ID, Col8 from MyTable union all

  select ID, Col9 from MyTable union all

  select ID, Col10 from MyTable union all

  select ID, Col11 from MyTable union all

  select ID, Col12 from MyTable

  Wouldn't you be better suggesting a UNIONed CROSS APPLY rather than all those selects??

  select ID,ca.col_num,ca.colx

  from MyTable

  cross apply (

  select 1,col1 union all

  select 2,col2 union all

  select 3,col3 union all

  select 4,col4 union all

  select 5,col5 union all

  select 6,col6 union all

  select 7,col7 union all

  select 8,col8 union all

  select 9,col9 union all

  select 10,col10 union all

  select 11,col11 union all

  select 12,col12

  ) ca(col_num, colx)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]