Início / dba / Perguntas / 224638
Accepted
kn3l
Asked: 2018-12-12 02:02:22 +0800 CST

Selecione os campos da subconsulta, mas o campo de rosto não pôde ser limitado

  • 772
SELECT 
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,    
    a.AcctBranchName, 
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE 
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE 
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12 
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM         
     v_Account AS a 
LEFT join v_Customer AS c 
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate

WHERE     
    a.AccountNum IN ( 
        SELECT  aa.AccountNum FROM v_Account AS aa WHERE   
         aa.Category = 'Deposit' AND aa.ProductCode IN ('6003', '6253') 
        AND aa.Businessdate='2018-12-06' AND aa.ClosedDate is not NULL
    )  
    AND a.Category = 'Deposit' 
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'

Como posso selecionar aa.ClosedDateda minha subconsulta ou qualquer solução alternativa que eu possa obter o ClosedDate de 2018-12-06.

Exemplo. Número da conta: 123456 está fechado na data 2018-12-06 (fechado significa sem saldo) Portanto, para obter o saldo desse número de conta, preciso procurar isso em 2018-12-05 (antes de 1 dia, esta conta é fechada ), então eu posso obter o saldo para minha finalidade de cálculo, por exemplo.

sql-server

2 respostas

  1. Best Answer

    Com base em seu comentário sobre a questão de saber se várias linhas poderiam sair de sua subconsulta para um determinado número de conta, você indicou que isso não era possível. Se isso for verdade, não vejo por que você não pode simplesmente LEFT JOINpelo número da conta em relação a um resultado derivado de sua subconsulta.

    Não testado, mas basicamente assim:

    SELECT 
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,    
    a.AcctBranchName, 
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE 
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE 
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12 
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM         
     v_Account AS a 
LEFT join v_Customer AS c 
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate
LEFT JOIN
(
    SELECT AccountNum, ClosedDate FROM v_Account WHERE   
        Category = 'Deposit' AND ProductCode IN ('6003', '6253') 
    AND Businessdate='2018-12-06' AND ClosedDate is not NULL
) aa on aa.AccountNum = a.AccountNum
WHERE 
        aa.AccountNum = a.AccountNum
    AND a.Category = 'Deposit' 
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'
    • 1 
  2. SELECT 
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,    
    a.AcctBranchName, 
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    c.ClosedDate,
    CASE 
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE 
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12 
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'
FROM         

     v_Account AS a 
LEFT join v_Customer AS c 
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate

WHERE     

    AND a.Category = 'Deposit' 
    AND a.ProductCode IN ('6003', '6253')
    AND (a.Businessdate='2018-12-05' OR a.Businessdate='2018-12-06' AND 
         a.ClosedDate is not NULL)
    • 0

relate perguntas