Início / dba / Perguntas / 228914
Accepted
kn3l
Asked: 2019-02-05 23:03:57 +0800 CST

Consulta onde a condição é retirada da lista de valor

  • 772
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT
       (
         SELECT ForeignCurrencyBal
         FROM [InsightWarehouse].[dbo].[v_Account] AS subA
         WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]
               AND 
               subA.AccountNum = STMT.ACCOUNT_NUMBER
       ) AS Balance,
       ACCOUNT_NUMBER,
       CASE WHEN STMT.CRF_TYPE='CREDIT' 
            THEN STMT.AMOUNT_LCY
            ELSE NULL
       END as 'CreditAmount',
       CASE WHEN STMT.CRF_TYPE='DEBIT' 
            THEN STMT.AMOUNT_LCY
            ELSE  NULL
       END as 'DebitAmount',
       STMT.BOOKING_DATE,
       STMT.VALUE_DATE,
       CRF_TYPE
FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 
     LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A 
     ON a.AccountNum = STMT.ACCOUNT_NUMBER
WHERE STMT.MIS_DATE='2019-02-01' 
      AND 
      STMT.ACCOUNT_NUMBER='14498478'

Nesta linha WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]esta lista pode ser obtida por subconsulta.

Eu quero colocar a condição onde no dia anterior, significa o segundo item desta lista. algo assim

BusinessDate='2019-01-31'

sql-server t-sql

3 respostas

  1. Best Answer

    Algo assim:

    SELECT 
    DISTINCT (
        SELECT ForeignCurrencyBal
        FROM [InsightWarehouse].[dbo].[v_Account] AS subA
            JOIN (
                SELECT TOP (1) dt
                FROM (
                    VALUES
                        ('2019-02-01')
                    ,   ('2019-01-31')
                    --,etc..
                )d(dt)
                WHERE d.dt < STMT.MIS_DATE
                ORDER BY d.dt DESC
            ) x ON x.dt = BusinessDate
        WHERE subA.AccountNum = STMT.ACCOUNT_NUMBER
    ) AS Balance,

    ACCOUNT_NUMBER,
    CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
        ELSE NULL
    END as 'CreditAmount',
    CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
        ELSE  NULL
    END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,

    CRF_TYPE

FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 
    LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A ON a.AccountNum = STMT.ACCOUNT_NUMBER

WHERE STMT.MIS_DATE='2019-02-01'
    AND STMT.ACCOUNT_NUMBER='14498478'

    UPD: Preste atenção que sua subconsulta tem que retornar uma linha então talvez você deva usar TOP (1)ou alguma função de agregação para evitar possíveis erros.

    • 1 
  2. declare @Date date='2019-02-01'
declare @Accountno varchar(20)='14498478'

SELECT 
    DISTINCT
    (SELECT ForeignCurrencyBal
          FROM [InsightWarehouse].[dbo].[v_Account] AS subA
          WHERE BusinessDate=dateadd(day,-1,STMT.MIS_DATE)
          AND subA.AccountNum = STMT.ACCOUNT_NUMBER) AS Balance,

    ACCOUNT_NUMBER
        ,CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
          ELSE NULL
          END as 'CreditAmount'
          ,CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
          ELSE  NULL
          END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,
    CRF_TYPE
  FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 

  LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A ON a.AccountNum = STMT.ACCOUNT_NUMBER
  WHERE STMT.MIS_DATE=@Date AND STMT.ACCOUNT_NUMBER=@Accountno

    Ou

    declare @Date date='2019-02-01'
declare @Accountno varchar(20)='14498478'

SELECT  
   ForeignCurrencyBal AS Balance,
    ACCOUNT_NUMBER
        ,CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
          ELSE NULL
          END as 'CreditAmount'
          ,CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
          ELSE  NULL
          END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,

    CRF_TYPE
  FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 
outer apply(select top 1 ForeignCurrencyBal from [InsightWarehouse].[dbo].[v_Account] AS subA 
where a.AccountNum = STMT.ACCOUNT_NUMBER and BusinessDate<STMT.MIS_DATE order by BusinessDate desc)OA
WHERE STMT.MIS_DATE=@Date AND STMT.ACCOUNT_NUMBER=@Accountno
    • 1

  3. Você pode tentar WHERE BusinessDate = CAST(GETDATE() -1 AS date), será no dia anterior.

    Por exemplo 2019-01-31, levará 2019-01-30e 2019-03-01levará 2019-02-28, etc.

    Além disso, se você deseja incluir apenas os dias da semana (segunda a sexta), adicionando outra condiçãoAND DATEPART(dw, GETDATE()-1) NOT IN (7,1)

    • 0

relate perguntas