V_immo's questions

Estou escrevendo uma consulta SQL para replicar abaixo do resultado do Excel.

Na Coluna AI tenho 252 valores na ordem ASC de 30/10/2018 a 29/10/2019 Na Coluna BV eu tenho o respectivo Número PL Eu quero calcular a coluna TestPL tendo valor como Ignorar os primeiros 3 valores PL e a 4ª célula seria adição de B2:B5, 5ª célula seria B3:B6 e assim por diante para todos os 251 valores

TestPL deve começar da linha 4 e deve ter a soma das células B2:B5, e a linha 5 seria B3:B6 e assim por diante em um fluxo até a linha 252, que seria B249:B252

Estou tentando inserir os 253 principais valores para cada trader de uma tabela de traders temporários na tabela de resultados temporários.

Estou usando o seguinte código para fazer isso.

CREATE TABLE #trad ( Trad varchar(50))    
IF @trad is not null    
INSERT INTO #Trad    
SELECT DISTINCT * FROM dbo.Split(@trad,',') -- split will have traders seperated by commas          
ELSE INSERT INTO #Traders    
Select distinct Trader from TraderMap tm     
where traderorgroup = 'trader'  


CREATE TABLE #result      
(port VARCHAR(50),portdate DATE,P MONEY,V_95 MONEY,      
V_99 MONEY,VLimit MONEY,V_90 MONEY,H_99 MONEY,      
H_975 MONEY,H_95 MONEY,H_90 MONEY,H_80 MONEY,dCnt 
INT,Trad varchar(100))  

DECLARE @traderlist AS Varchar(100)      
DECLARE traderlist CURSOR FOR      
SELECT DISTINCT Traders FROM #traders      
OPEN traderlist      
FETCH next FROM traderlist INTO @traderlist      
WHILE @@FETCH_STATUS = 0      
BEGIN  
INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit, 
dCnt,Trad)      
SELECT  TOP 253 pl.Port,pl.Portdate,pl.dtd_P 
PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,      
Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC) 
dCnt,pl.TradName      
FROM Trad_PL pl INNER JOIN rep_tab var      
ON pl.Portdate = var.V_Date      
AND pl.Port = var.Port     
AND pl.TradName = var.Trad      
INNER JOIN Trad_Guid gui      
ON pl.Port = gui.Port     
AND pl.TradName = gui.Trad      
WHERE pl.Portdate <= @portdate      
AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE 
@port END      
AND pl.ONA = 'Act'  
AND pl.TradName = @tradlist          
AND var.PType = '0Trad' 
group by  
pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC
FETCH next FROM tradlist INTO @tradlist      
END      
CLOSE tradlist      
DEALLOCATE tradlist    

Este código é executado por horas. Existe alguma maneira eficiente de obter as 253 principais linhas para cada negociação de Trad_PL e rep_tab.

Estou tentando replicar a função Small no Excel, que é Small (Array, K) fornece o Kth menor elemento do fluxo de dados.

Eu quero encontrar o valor 13th Min para valores de coluna perticular. na minha tabela eu tenho 2 colunas A e B para cada valor de linha da coluna A que tenho digamos 20 valores B. Estou tentando encontrar o 13º menor valor para cada valor A da coluna B

Estou usando a seguinte consulta para obter min do grupo por cláusula

select A,min(B) from test group by Level_Name

Eu quero encontrar o 13º menor valor e não o menor valor.

Eu adicionei tentei uma nova consulta como

with a as(
select A,B,ROW_NUMBER() over (order by A,B) as rownum 
 from test 
group by Level_Name,pl
)
select A,B from a 
where rownum=13
group by A,B

Ele está retornando apenas o 13º número para 1 do valor A, mas há várias linhas em A.

Eu tenho o seguinte código desenvolvendo 2 arquivos XML, mas gostaria que eles estivessem em um arquivo com várias tags de organização.

SELECT 
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
    UNION ALL 
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

SELECT 
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from
(
    Select 'Coll' AS 'orgRole'
    UNION ALL 
    Select 'Lega' AS 'orgRole'
) a
FOR XML PATH(''), TYPE, ELEMENTS
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

Quando eu executo isso, ele produz 2 arquivos XML como saída como abaixo.

<collection>
 <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
  </externalCodes>
</organisation>

E

<collection>
 <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

Embora eu queira adicioná-lo em um único arquivo para produzir resultados como Coleção como raiz e mesclar esses arquivos na marca da organização da seguinte maneira.

 <collection>
  <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
   </externalCodes>
  </organisation>
  <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

Por favor, comente abaixo se qualquer outra informação for necessária. Obrigado.

Eu tenho que criar um arquivo XML usando sql

 <externalCodes>
  <externalCode>
   <system>CALYPSO</system>
   <extCode>cptname</extCode>
  </externalCode>
 <externalCode>
   <system>CMS-GDP</system>
   <extCode>cptname</extCode>
 </externalCode>
 <externalCode>
   <system>Manual</system>
   <extCode>cptname</extCode>
 </externalCode>

Meu SQL é o seguinte

(SELECT system, extCode from(Select 'CALYPSO' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') a
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from(Select 'CMS-GDP' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') b
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from( Select 'MANUAL' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') c
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode"

Minha saída vem como abaixo:

    <externalCodes>
      <externalCode>
        <system>CALYPSO</system>
        <extCode>Mon</extCode>
        <system>CMS-GDP</system>
        <extCode>Mon</extCode>
        <system>MANUAL</system>
        <extCode>Mon</extCode>
      </externalCode>
     </externalCodes>

Mas a saída esperada é como abaixo

   <externalCodes>
     <externalCode>
       <system>CALYPSO</system>
       <extCode>Mon</extCode>
     </externalCode>
    <externalCode>
       <system>CMS-GDP</system>
       <extCode>Mon</extCode>
     </externalCode>
     <externalCode>
       <system>MANUAL</system>
       <extCode>Mon</extCode>
     </externalCode>
    </externalCodes>

Procedimento armazenado

CREATE     procedure [dbo].[ImproveProcedure] (@port varchar(50), @portdate datetime)    
as    

Declare @intdate datetime    
select @intdate = max(rate_date) from Interestrate where rate_type = 'Zero'    
and rate_date <= @portdate    

Update transactiontable set NonDiscount = null, Discount = null, NonDiscountTcurr = null, DiscountTcurr = null,    
NonDiscountNew = null, DiscountNew = null    
where    
port = @port and portdate = @portdate    

Update tr set NonDiscount = (case sss_ind when 'P' then -exposure else exposure end) *    
      dbo.Foo(ccd, 'USD', @portdate,    
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1     
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48     
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))     
end),    
NonDiscountTcurr = (case sss_ind when 'P' then -exposure else exposure end)    
from    
Phy p1    
where    
port = @port and portdate = @portdate    
and    
tr.trans = p1.trans    
and    
p1.Sub <> 'Option'    


Update tr set NonDiscount = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end) *    
      dbo.Foo(ccd, 'USD', @portdate,     
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1     
 when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48     
 else  datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),    
NonDiscountTcurr = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end)    
from    
Phy p1    
where    
port = @port and portfolio = @portfolio    
and    
tr.trans = p1.trans    
and    
p1.Sub = 'Option'





CREATE   function [dbo].[Foo]  
(@currency1 varchar(10),   
@currency2 varchar(10),  
@portdate datetime, @month int) returns float  

As  
BEGIN  
Declare  
@CurrentRate float,  
@Ratedate datetime  


select @Ratedate = max(rate_date) from fx where  
(  
currency1 = @currency1 and currency2 = @currency2 and forward_month = @month  
or  
currency2 = @currency1 and currency1 = @currency2 and forward_month = @month  
)  
and  
@portdate >= Rate_date  


IF exists ( select * from fx where currency1 = @currency1  
and currency2 = @currency2  and rate_date = @Ratedate and forward_month = @month)   


  SELECT @CurrentRate =  Rate from fx where currency1 = @currency1 and  
  currency2 = @currency2 and rate_date = @Ratedate and forward_month = @month  



ELSE   



  IF exists ( select * from fx where currency1 =  
  @currency2 and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month)  


  select @CurrentRate = 1/Rate  from fx  where currency1 = @currency2  
  and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month  


  ELSE  
   select @CurrentRate = 1   


return (@CurrRate )  
END  

Eu entendo que as chamadas de função escalar podem limitar o desempenho de uma consulta. Alguém pode ver uma maneira de remover as chamadas de função, substituindo-as por um JOINou CROSS APPLYou algo, para ver se isso ajuda no desempenho? Eu tentei, mas não consegui encontrar uma maneira.

Mais informações sobre o desempenho atual: uma única execução do procedimento, atualizando cerca de 25.000 linhas, leva de 20 a 25 minutos para ser executada. Olhando para o plano de execução, vejo muitas leituras amarradas na função e estou pensando que com uma JOINsolução (ou algo assim), esse número de leituras cairia drasticamente e o desempenho poderá melhorar. Além disso, como observado acima, ouvi dizer que as chamadas de função escalar são (em geral) "ruins" nas consultas.