Alternative to CASE in SQL Server -

-

i have following 2 tables:

 create table [dbo].[tab1](      [coil_id] [varchar](25) not null,      [coil_date] [datetime] null,      [grade] [float] null,      [quality] [float] null,      [furnace_num] [float] null,      [fm_gauge] [float] null,      [fm_width] [float] null,      [fintemp_aim] [float] null,      [slab_gauge] [float] null,      [slab_width] [float] null,      [slab_length] [float] null,      [do_temp] [float] null,      [gap_reference_pr1_1] [float] null,      [gap_reference_pr1_2] [float] null,      [gap_reference_pr1_3] [float] null,      [gap_reference_pr1_4] [float] null,      [gap_reference_pr1_5] [float] null,      [gap_reference_pr1_6] [float] null,      [gap_reference_pr1_7] [float] null,      [gap_reference_pr1_8] [float] null,      [gap_reference_pr1_9] [float] null,      [gap_reference_pr1_10] [float] null,      [gap_reference_pr1_11] [float] null,      [gap_reference_pr1_12] [float] null,      [gap_reference_pr1_13] [float] null,      [gap_reference_pr1_14] [float] null,      [gap_reference_pr1_15] [float] null,      [gap_reference_pr1_16] [float] null,      [gap_reference_pr1_17] [float] null,      [gap_reference_pr1_18] [float] null,      [gap_reference_pr1_19] [float] null,      [gap_reference_pr1_20] [float] null,      [gap_reference_pr1_21] [float] null,      [gap_reference_pr1_22] [float] null,      [gap_reference_pr1_23] [float] null,      [gap_reference_pr1_24] [float] null,      [gap_reference_pr1_25] [float] null  )     create table [dbo].[tab2](      [coil_id] [varchar](25) not null,      [_segmentnr] [int] not null,      [hrm metal in mill] [real] null,      [hrm screws homed] [real] null,      [hrm tracking current pass number] [real] null,      [topmotor_armcur] [real] null,      [max_topmtr_armcur_pass] [real] null,      [botmotor_armcur] [real] null,      [max_botmtr_armcur_pass] [real] null,      [speed] [real] null,      [power] [real] null,      [temp] [real] null,      [t_load] [real] null,      [d_load] [real] null,      [posfb] [real] null,      [dif_pos] [real] null,      [guide_entry] [real] null,      [guide_exit] [real] null,      [thread_achi] [real] null  )

there 1 row in [dbo].[tab1] coild-id , multiple rows in [dbo].[tab2].

the out put need follows:

coil-id  hrm-tracking-current-pass-number      gap  1             1                              value of gap_reference_pr1_1                       1             2                              value of gap_reference_pr1_2                           1             3                              value of gap_reference_pr1_3

i using following code:

select a.coil_id coil_id, b.[hrm tracking current pass number] , max(case b.[hrm tracking current pass number] when 1   a.exit_gauge_pr1_1 when 2   a.exit_gauge_pr1_2 when 3   a.exit_gauge_pr1_3 when 4   a.exit_gauge_pr1_4 when 5   a.exit_gauge_pr1_5 when 6   a.exit_gauge_pr1_6 when 7   a.exit_gauge_pr1_7 when 8   a.exit_gauge_pr1_8 when 9   a.exit_gauge_pr1_9 when 10  a.exit_gauge_pr1_10 when 11  a.exit_gauge_pr1_11 when 12  a.exit_gauge_pr1_12 when 13  a.exit_gauge_pr1_13 when 14  a.exit_gauge_pr1_14 when 15  a.exit_gauge_pr1_15 when 16  a.exit_gauge_pr1_16 when 17  a.exit_gauge_pr1_17 when 18  a.exit_gauge_pr1_18 when 19  a.exit_gauge_pr1_19 when 20  a.exit_gauge_pr1_20 when 21  a.exit_gauge_pr1_21 when 22  a.exit_gauge_pr1_22 when 23  a.exit_gauge_pr1_23 when 24  a.exit_gauge_pr1_24 when 25  a.exit_gauge_pr1_25 end)  exit_gauge,

i want know if there more efficient way writing query.

thanks in advance help.

starting in sql server 2012, there choose method:

select choose ( 3, 'manager', 'director', 'developer', 'tester' ) result;    result   -------------   developer

so in case, use this:

select max(     choose(         b.[hrm tracking current pass number],         a.exit_gauge_pr1_1,         a.exit_gauge_pr1_2,         a.exit_gauge_pr1_3,         ...     ) )

Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more