kn3l's questions

WITH trips_by_day AS
                      (
                      SELECT DATE(trip_start_timestamp) AS trip_date,
                          COUNT(*) as num_trips
                      FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                      WHERE trip_start_timestamp >= '2016-01-01' AND trip_start_timestamp < '2018-01-01'
                      GROUP BY trip_date
                      ORDER BY trip_date
                      )
                      SELECT trip_date,
                          avg(num_trips)
                          OVER (
                               order by trip_date
                               rows between 15 preceding and 15 following
                               ) AS avg_num_trips
                      FROM trips_by_day

Alguém poderia me explicar o significado de rows between 15 preceding and 15 following?

SELECT TOP 1000 [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END 'Amount' --I want to split into type of amount debit or crdit
     ,CURRENCY

Como eu poderia dividir o 'Valor' em 2 colunas debite creditbasear na condição se > 0 crédito ou débito.

/****** 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'

Eu tenho T1 como tabela 1

----------------
field1 | field2
---------------
 val1  | val2

Eu tenho T2 como tabela 2

----------------
field1 | field2
---------------
 val11  | val22

Eu quero escrever uma consulta que pode produzir resultados como este

 field1 | field2 | T1.T2 | 
 ------------------------|
 val11  | val22  | T2    | 
 val1   | val2   | T1    | 

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.

SELECT 
      CurrencyCodeFrom,
      CurrencyCodeTo, 
      MidRevalRate,
      MultiplierRate,
      RevalRate,
      CurrencyMarketCode,
      Added, 
      Modified, 
      UpdateReason
FROM 
     [InsightWarehouse].[dbo].[DimCurrency] as dc
 INNER  JOIN 
     [InsightWarehouse].[dbo].[FactCurrency] as fc 
     ON fc.CurrencyId = dc.CurrencyId
     AND fc.BusinessDate  = '2018-11-27'
     AND ( CurrencyCodeFrom <> 'KHR' OR CurrencyCodeTo <> 'KHR')

Resultado

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode
THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.750000000   122.750000000   1.000000000 10
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9

Eu quero ordenar o CurrencyMarketCodeDESC por manter o grupoCurrencyCodeFrom CurrencyCodeTo

Deveria estar

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode

THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
THB                 KHR             122.750000000   122.750000000   1.000000000 10

USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10