代码之家 › 专栏 › 技术社区 › katy89

在SQL Server中使用嵌套CASE语句

case nested sql-server

-1

katy89 · 技术社区 · 6 年前

我正在使用SQL Server并尝试修改下面的查询,特别是针对 case 陈述

当 TransactionalCurrency 是 USD .

但我正在寻找修改。

例如,对于特定的MasterPolicyNumber,当 交易货币不是美元 ,然后我希望连接到MasterPolicyNumber上的另一个表(TransDetails),并将其值“Renewed Layer”列用于此查询。基本上,只有当TransactionalCurrency不是USD时,才会复制TransDetails的“更新层”的值并将其粘贴到下面的结果中。

            with PolicyDtls as 
            ( select 
                     MasterPolicyNumber,
                     PolicyNumber,  
                     NewRenewal,
                     TransactionalCurrency,     
                     LimitUSD, 
                     AttachmentType, 
                     ISNULL(AttachmentUSD, 0) + ISNULL(SIR_USD,0) +  ISNULL(DeductibleUSD,0)  as  AttachmentUSD     
                     from Policy_Test   
            )


            select
                   ref.Region,     
                   ref.MasterPolicyNumber,     
                   previous.MasterPolicyNumber as PriorMasterPolicyNumber,
                   curr.TransactionalCurrency, 
                   curr.LimitUSD,   
                   previous.LimitUSD as prevLimitUSD,
                   curr.AttachmentUSD,  
                   previous.AttachmentUSD as prevAttachUSD,
                   case when  curr.LimitUSD = previous.LimitUSD and curr.AttachmentUSD = previous.AttachmentUSD then 'Renewed Same Layer' 
                        when  (curr.LimitUSD <> previous.LimitUSD OR curr.AttachmentUSD <> previous.AttachmentUSD ) 
                              and previous.AttachmentUSD IS NOT NULL 
                              then 'Renewed Different Layer' 
                        else 'Renewed Unknown' 
                    end as   'Renewal Layer', 
                   curr.AttachmentType

            from Actuarial.Rptng.Renewal_XREF ref
            left join PolicyDtls curr on curr.MasterPolicyNumber = ref.MasterPolicyNumber 
            left join PolicyDtls previous on previous.MasterPolicyNumber = ref.Prior_MasterPolicyNumber
            where 
            curr.NewRenewal = 'Renewal'
            and curr.MasterPolicyNumber = '49-NPR-000013-02'
            order by curr.PolicyNumber

仅使用上述查询的示例数据:

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
    DROP TABLE #mytable

--===== Create the test table with 
CREATE TABLE #mytable 
(
    Region nvarchar(300), 
    MasterPolicyNumber nvarchar(300),
    PriorPolicyNumber nvarchar(300),
    TransactionalCurrency nvarchar(100), 
    LimitUSD float,
    prevLimitUSD  float, 
    AttachmentUSD  float,
    prevAttachUSD float, 
    RenewedLayer nvarchar(300), 
    AttachmentType nvarchar(100)
)

SET DATEFORMAT DMY

--===== Insert the test data into the test table
INSERT INTO #mytable (Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitUSD, prevLimitUSD, AttachmentUSD,  prevAttachUSD,  RenewedLayer,   AttachmentType)
    SELECT 'EUR', '47-ACA-000001-02', '47-ACA-000001-01', 'EUR', '7105.8', '6218.6', '32763', '23273', 'Renewed Differed Layer', 'Excess' 


    select *from #mytable

TransDetails表的示例数据

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#TransDetails') IS NOT NULL
    DROP TABLE #TransDetails

--===== Create the test table with 
CREATE TABLE #TransDetails 
(
    Region nvarchar(300), 
    MasterPolicyNumber nvarchar(300),
    PriorPolicyNumber nvarchar(300),
    TransactionalCurrency nvarchar(100), 
    LimitTrans float,
    prevLimitTrans  float, 
    AttachmentTrans  float,
    prevAttachTrans float, 
    RenewedLayer nvarchar(300), 
    AttachmentType nvarchar(100)
)

SET DATEFORMAT DMY

--===== Insert the test data into the test table
INSERT INTO  #TransDetails(Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitTrans, prevLimitTrans, AttachmentTrans,   prevAttachTrans,    RenewedLayer,   AttachmentType)
    SELECT 'EUR', '47-ACA-000001-02', '47-ACA-000001-01', 'EUR', '8000', '8000', '3000', '3000', 'Renewed Same Layer', 'Excess' 


    select *from  #TransDetails

预期结果

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
    DROP TABLE #mytable

--===== Create the test table with 
CREATE TABLE #mytable 
(
    Region nvarchar(300), 
    MasterPolicyNumber nvarchar(300),
    PriorPolicyNumber nvarchar(300),
    TransactionalCurrency nvarchar(100), 
    LimitUSD float,
    prevLimitUSD  float, 
    AttachmentUSD  float,
    prevAttachUSD float, 
    RenewedLayer nvarchar(300), 
    AttachmentType nvarchar(100)
)

SET DATEFORMAT DMY

--===== Insert the test data into the test table
INSERT INTO #mytable (Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitUSD, prevLimitUSD, AttachmentUSD,  prevAttachUSD,  RenewedLayer,   AttachmentType)
    SELECT 'EUR', '47-ACA-000001-02', '47-ACA-000001-01', 'EUR', '7105.8', '6218.6', '32763', '23273', 'Renewed Same Layer', 'Excess' 


    select *from #mytable

1 回复 | 直到 6 年前

Tab Alleman 6 年前

只要加入到 TransDetail 可以使其不会创建重复项,您只需将其作为外部联接添加到FROM子句中,然后对现有的CASE表达式执行类似操作:

               case 
                    when curr.TransactionalCurrency<>'USD' then TransDetail.RenewedLayer
                    when  curr.LimitUSD = previous.LimitUSD and curr.AttachmentUSD = previous.AttachmentUSD then 'Renewed Same Layer' 
                    when  (curr.LimitUSD <> previous.LimitUSD OR curr.AttachmentUSD <> previous.AttachmentUSD ) 
                          and previous.AttachmentUSD IS NOT NULL 
                          then 'Renewed Different Layer' 
                    else 'Renewed Unknown' 
                end as   'Renewal Layer',