sql server - Get Other Column to Include on Pivot Calculation SQL -


i have following table:

-------------------------------------------------------------- inspectyear     part       cpoint  pos1    pos2    pos3    pos4 -------------------------------------------------------------- 2009            001        0.2     8       8       9       7 2009            002        0.2     9       7       8       6 2011            001        0.3     9       9       8       7 2011            002        0.7     7       8       6       8 2013            001        0.2     8       9       7       9 2013            002        0.4     7       7       8       8 2015            001        0.2     8       8       7       4 2015            002        0.8     7       6       9       8

then have following query:

declare @inspectyear nvarchar(max), @calc nvarchar(max), @query nvarchar(max);  set @inspectyear = stuff((select distinct ',' + quotename(inspectyear) ##t2 c                   xml path(''), type).value('.','nvarchar(max)'),1,1,'')  select @calc = ', (' + quotename(max(inspectyear)) + ' - ' + quotename(max(inspectyear)-2)           + ') / 2.0 calc1, 1.0 * (' + quotename(max(inspectyear)) + ' - ' + quotename(min(inspectyear))           + ') / (' + cast(max(inspectyear) char(4)) + '-' + cast(min(inspectyear) char(4)) + ') calc2'  #t2;  set @query = ';with data (       select      inspectyear, cpoint,                   partno, pos, number       #t2       unpivot       (             number             pos in ([pos1], [pos2], [pos3], [pos4])       ) unpvt ) select * ' + @calc + ' ##temp data pivot (       sum(number)       inspectyear in (' + @inspectyear + ') ) pvt order partno';  exec sp_executesqk @query = @query; select * ##temp; drop table ##temp;

the result should be:

---------------------------------------------------------------------------- part   cpoint      pos      2009    2011    2013    2015    calc1     calc2 ---------------------------------------------------------------------------- 001    0.2         pos1     8       9       8       10      1         0.33 001    0.3         pos2     8       9       9       8       -0.5      0 001    0.2         pos3     9       8       7       7       0         -0.33 001    0.2         pos4     7       7       9       4       -2.5      -0.5

my question is, how can add new calculation newest value minus cpoint value...? let say, calc3.

for example, on first record on result above.

---------------------------------------------------------------------------------- part   cpoint      pos      2009    2011    2013    2015    calc1     calc2   calc3 ---------------------------------------------------------------------------------- 001    0.2         pos1     8       9       8       10      1         0.33    9.8

10 value on newest year column minus 0.2 cpoint. result calc3 = 9.8

does have idea this? thank you.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more