在另一个计算中使用产品 - SQL select语句

Question

这是我冗长的SQL代码。我基本上试图将数据库信息首先从每月，每日和每年费率转换为每小时。然后将此小时数转换为美元，如果它尚未。

到目前为止，我已将兑换率转换为“兑换率”，但在转换为美元时如何使用此新兑换率？即在：

WHEN wo.currency = 'GBP' 然后（wo.avg_rate * 1.66）

我不能改变wo.avg_rate要么wo.converted_rate，x.converted_rate， “转化率，” 或“转化率。'我怎样才能做到这一点 ？

SELECT 
    x.work_order_ref as 'Work Order Reference' 
    ,x.buyer_code as 'Buyer Code' 
    ,x.submit_time as 'Work Order Submit Time' 
    ,x.rate_unit_for_spend_calc as 'UOM' 
    ,x.avg_rate as 'ST Rate' 
    ,x.converted_rate as 'Converted Rate' 
    ,x.currency as 'Currency' 
    ,x.converted_amount AS 'Converted Amount' 
    ,jp.title AS 'JP Title' 
    ,lbt.name AS 'Labor Type' 
    ,jp.max_distribution_level AS 'Max Distr Level' 
    ,si.city AS 'City' 
    ,si.state_province as 'State' 
    ,si.country AS 'Country' 
    ,ctg.name AS 'Category' 
    ,count(jpd.supplier_code) as 'Suppliers' 
from 
(select 
     wo.work_order_ref 
     ,wo.buyer_code 
     ,wo.submit_time 
     ,wo.rate_unit_for_spend_calc 
     ,"converted_rate" = 
         CASE 
         WHEN wo.rate_unit_for_spend_calc='Hr' then wo.avg_rate 
         WHEN wo.rate_unit_for_spend_calc='MO' then (wo.avg_rate/173.333) 
         WHEN wo.rate_unit_for_spend_calc='Day' then (wo.avg_rate/8) 
         WHEN wo.rate_unit_for_spend_calc='Yr' then (wo.avg_rate/2080) 
         END 
     ,wo.avg_rate 
     ,wo.currency 
     ,wo.job_posting_id 
     ,wo.site_id 
     ,wo.worker_id 
     ,wo.uploaded_flag 
     ,wo.sequence 
     ,"converted_amount" = 
         CASE 
         WHEN wo.currency='USD' then (wo.avg_rate) 
         WHEN wo.currency='GBP' then (wo.avg_rate*1.66) 
         WHEN wo.currency='SEK' then (wo.avg_rate*0.14) 
         WHEN wo.currency='EUR' then (wo.avg_rate*1.31) 
         WHEN wo.currency='CAD' then (wo.avg_rate*0.92) 
         WHEN wo.currency='AUD' then (wo.avg_rate*0.93) 
         WHEN wo.currency='INR' then (wo.avg_rate*0.017) 
         WHEN wo.currency='MXN' then (wo.avg_rate*0.076) 
         WHEN wo.currency='COP' then (wo.avg_rate*0.00052) 
         WHEN wo.currency='BRL' then (wo.avg_rate*0.44) 
         WHEN wo.currency='MYR' then (wo.avg_rate*0.32) 
         WHEN wo.currency='CNY' then (wo.avg_rate*0.16) 
         WHEN wo.currency='RON' then (wo.avg_rate*0.3) 
         WHEN wo.currency='CZK' then (wo.avg_rate*0.047) 
         WHEN wo.currency='SGD' then (wo.avg_rate*0.8) 
         WHEN wo.currency='PLN' then (wo.avg_rate*0.31) 
         WHEN wo.currency='JPY' then (wo.avg_rate*0.0096) 
         WHEN wo.currency='NZD' then (wo.avg_rate*0.84) 
         WHEN wo.currency='CHF' then (wo.avg_rate*1.09) 
         --ELSE (wo.avg_rate*x.conversion_factor) 
         END 
    from work_order wo(nolock)) as x 

...

如果它使它更好地为我发布完整的代码和/或什么我得到的，我能做到这一点。

Answer 1

如果您将转换存储在表中，它将使查询变得更加容易。你甚至可以摆脱内部查询：

declare @uom table (
    unit char(3) not null primary key, 
    divisor decimal(10, 5) not null 
); 

insert into @uom values 
    ('Hr', 1), ('MO', 173.333), ('Day', 8), ('Yr', 2080); 

declare @rate table (
    currency char(3) not null primary key, 
    usd_mult decimal(10, 5) not null 
); 

insert into @rate values 
    ('USD', 1), ('GBP', 1.66), ('SEK', 0.14), ('EUR', 1.31), 
    ('CAD', 0.92), ('AUD', 0.93), ('INR', 0.017), ('MXN', 0.076), 
    ('COP', 0.00052), ('BRL', 0.44), ('MYR', 0.32), ('CNY', 0.16), 
    ('RON', 0.3), ('CZK', 0.047), ('SGD', 0.8), ('PLN', 0.31), 
    ('JPY', 0.0096), ('NZD', 0.84), ('CHF', 1.09); 

select 
    wo.work_order_ref, 
    wo.buyer_code, 
    wo.submit_time, 
    wo.rate_unit_for_spend_calc, 
    wo.avg_rate/u.dividend as converted_rate, 
    wo.avg_rate, 
    wo.currency, 
    wo.job_posting_id, 
    wo.site_id, 
    wo.worker_id, 
    wo.uploaded_flag, 
    wo.sequence, 
    -- is this where you wanted converted_rate * currency_mult? 
    wo.avg_rate/u.dividend * r.usd_mult as converted_amount 
from 
    work_order wo with (nolock) 
     inner join 
    @uom u 
     on wo.rate_unit_for_spend_calc = u.unit 
     inner join 
    @rate r 
     on wo.currency = r.currency 
) 

如果没有这样做，你可以隐藏功能后面的计算。这使事情变得更容易阅读，但可以减缓查询，为优化器通过功能看到的麻烦：

create function dbo.HourlyRate(@unit as char(3), @rate as decimal(10, 5)) returns decimal(10, 5) as 
begin 
    return case @unit 
     when 'Hr' then @rate 
     when 'MO' then @rate/173.333 
     when 'Day' then @rate/8 
     when 'Yr' then @rate/2080 
    end 
end 

create function dbo.ConvertToUSD(@currency as char(3), @amount as decimal(20, 5)) returns decimal(20, 5) as 
begin 
    return case @currency 
     when 'USD' then @amount 
     when 'GBP' then @amount * 1.66 
     ... 
    end 
end 

说它像这样：

select 
    ... 
    dbo.HourlyRate(wo.rate_unit_for_spend_calc, wo.avg_rate) as converted_rate, 
    ... 
    dbo.ConvertToUSD(wo.currency, dbo.HourlyRate(wo.rate_unit_for_spend_calc, wo_avg_rate) as converted_amount, 
    ... 
from 
    work_order wo with (nolock)