Unit conversion in SQL Server -

i tried converting unfortunately not working. converting units such kg, lt, piece etc.

i have conversion factor.

the table convert follows:

conversion_factor_x   alternative_unit  conversion_factor_y    unit ---------------------------------------------------------------------           16              kg                   1               piece           16              lt                   1               piece            1              piece                1               piece 

it's 1 piece = 16 kg, or 1 piece = 16 litres, , 1 piece = 1 piece

i tried first part i.e. alternative unit , tried convert it.. kg not others i.e lt , piece.

select      --alternativeunit     case         when stock = 0           0        else            case qty_unit_target               when 'kg'                                      case qty_unit_stock                         when 'kg'                            qty_stock * qty_x                        when 'st'                                                          case qty_unit_stock                                   when 'st'                                      qty_stock * qty_x                                  when 'lt'                                                                             case qty_unit_stock                                            when 'lt'                                               qty_stock * qty_x                                        end                                 end                      end            end      end       xyz 

above it's converting alternativeunit. @ first, checks unit. if both same.. multiplies qty conversion rate , checks units.

you structure seems wrong. think should

select   --alternativeunit case when stock = 0 0 else      case qty_unit_target        when 'kg' case qty_unit_stock when 'kg' qty_stock * qty_x end        when 'st' case qty_unit_stock when 'st' qty_stock * qty_x end       when 'lt' case qty_unit_stock when 'lt' qty_stock * qty_x  end     end  end  xyz