Kaishu's questions

A tableTarefa tem colunas como:

Task(TaskID, TaskName, CreatedDate)

E deseja executar o bloco de código com base na diferença de data e hora como:

SE a data de criação da tarefa passou 15 minutos ENTÃO

Execute o bloco #01

ELSE IF Task CreatedDate ainda não passou de 15 minutos ENTÃO

Execute o bloco #02

Eu testei abaixo da consulta, mas dá resultado como61633440

SELECT DATEDIFF(MINUTE, CONVERT(VARCHAR(10), CreatedDate, 108), CURRENT_TIMESTAMP) AS MinuteDiff

Como poderíamos alcançar acima em sql?

Tabela #01 Status:

StatusID    Status
-----------------------
 1          Opened
 2          Closed
 3          ReOpened
 4          Pending

Tabela #02 Claims:

ClaimID     CompanyName StatusID
--------------------------------------
1               ABC     1
2               ABC     1
3               ABC     2
4               ABC     4
5               XYZ     1
6               XYZ     1

Resultado esperado:

CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims
--------------------------------------------------------------------------------
ABC                 2           1                      0               1
XYZ                 2           0                      0               0

Como eu preciso escrever a consulta para que eu possa obter o resultado conforme o esperado?

Tenho uma sqltabela da qual estou tentando obter resultados com base em alguns critérios como:

Da tabela acima, quero criar xml, que deve retornar o resultado como:

Aqui está a consulta que escrevi para o

DECLARE
    @ResourceName VARCHAR(50)
, @XMLResult XML = NULL
, @xMSG XML = NULL

SET @ResourceName = 'Property'   
SET @xmsg = '<Results ReplyCode="0" ReplyText="Operation successful"><Result></Result></Results>'          

SET @xMSG = 
(
    SELECT 0 AS [@ReplyCode]
    , 'Operation Successfull' AS [@ReplyText]
    , @ResourceName AS ResourceName
    , ( SELECT *
        FROM ( 
                SELECT SystemName
                        , DBName
                        , ShortName
                        , ClassSortOrder
                        , Pcode
                        , TabNumber
                        , ColumnNumber
                        , RowNumber
                FROM ScreenOrder
            ) SearchFieldsList
        ORDER BY ClassSortOrder
        FOR XML PATH('Field'), TYPE
     )
    FOR
    XML PATH('Results')
)

SET @XMLResult = @xMSG
SELECT   @XMLResult    

Mas esta consulta não retorna o resultado esperado. Esta consulta retorna o resultado como:

Estou tentando colocar todos esses campos como classe sábia em xml. Onde preciso modificar a consulta?

Estou inserindo valores em tablefrom xmlcomo:

DECLARE @XMLRESULT XML

DECLARE @tbl_XMLResult TABLE
(
    RowID INT IDENTITY(1,1),
    Status VARCHAR(50),
    Address VARCHAR(250),
    ListPrice INT,
    SoldPrice INT,
    YearBuilt INT   
)

EXEC usp_GetReportResult @query = @query, @ReportName = @ReportName, @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult
(
    Status,
    Address,
    ListPrice,
    SoldPrice,
    YearBuilt
)
SELECT
    L.value('(ListingStatus/text())[1]','VARCHAR(50)') AS Status,
    L.value('(PMCReportData/Listing_StreetAddress/text())[1]','VARCHAR(250)') AS Address,
    L.value('(ListPrice/text())[1]','INT') AS ListPrice,
    L.value('(ClosePrice/text())[1]','INT')AS SoldPrice,
    L.value('(YearBuilt/text())[1]','INT')AS YearBuilt
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing')AS Result(L)

Mas, como mostrado acima, para YearBuilta coluna, o tipo de dados está intno qual estamos armazenando apenas yearem qual propriedade é construída. E, consequentemente, subsequentes calculationssão feitos. Mas algumas vezes os valores para YearBuiltvêm de xmlcomo emformat

• "Novo"
• "0-5 anos"
• "70+"

Mas quando o tipo de resultado acima YearBuiltchega xml, estamos obtendo conversion error. Precisa obter valores do resultado acima como se YearBuilt como:

• "Novo" - Aqui queremos considerar o ano atual e armazenar o resultado como 2016 do ano atual.
• "0-5 anos" - 0-5 anos. Aqui, considere o limite superior, ou seja, 5. Portanto, armazene o resultado como 2011 as(2016 - 5 = 2011)
• "70+" - considere 70 como => 2016 - 70 Ano = 1946

Como podemos fazer isso para armazenar o resultado apropriado em@tbl_xmlResult

A seguir function, escrevi que aceita xmle retorna tablecomo resultado.

CREATE FUNCTION FunctionTest(@ID INT,@XML_Details xml)
RETURNS @RESULT TABLE
(
    Value1 INT,
    Value2 INT
)
AS
BEGIN
    DECLARE @tbl_Xml_Result Table
    (
        Value1 INT,
        Value2 INT
    )

    INSERT INTO @RESULT(Value1,Value2)
    SELECT 
        l.v.value('Value2[1]','INT'),
        l.v.value('Value1[1]','INT')
    FROM @XML_Details.nodes('/Listings/listing')l(v)
    RETURN
END

E a seguir está o código que estou usando para executar acima function, mas sempre retornando o Emptyresultado.

DECLARE @tbl_Xml_Result Table
(
        Value1 INT,
        Value2 INT
)
INSERT INTO @tbl_xml_Result
values(1,2),(2,3),(3,4),(4,5),(5,6)


DECLARE @xml_Temp xml

SET @xml_Temp = (   SELECT * 
                    FROM @tbl_xml_Result
                    FOR XML PATH('Listing'),ROOT('Listings')
                )

DELETE FROM @tbl_xml_Result
INSERT INTO @tbl_xml_Result(Value1,Value2)
Select 
    T.Value1,
    T.Value2
FROM FunctionTest(1,@xml_Temp) T

select * from @tbl_Xml_Result

Eu criei um tipo definido da seguinte forma:

CREATE TYPE [dbo].[TypeListingDatePrice] AS TABLE(
    [RowID] [int] NULL,
    [ListPrice] [int] NULL,
    [SoldPrice] [int] NULL,
    [PendStatusDOM] [int] NULL,
    [ListingDate] [int] NULL,
    [StatusDate] [int] NULL,
    [PendDate] [int] NULL,
    [SaleDate] [int] NULL
)

Isso funciona bem no SQL Server 2012, mas não consigo fazer o mesmo no SQL Server 2005. Dá um erro na execução:

Msg 156, Nível 15, Estado 1, Linha 1
Sintaxe incorreta perto da palavra-chave 'AS'.

Tenho a seguinte estrutura, e estou fazendo uma SEToperação em uma CASEcláusula mas está gerando um erro:

Sintaxe incorreta próximaSET

Consulta:

DECLARE @tbl_Result TABLE
(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

DECLARE 
    @Declining FLOAT,
    @StableStart SMALLINT,
    @StableEnd SMALLINT,
    @Increasing SMALLINT;

SELECT
    @Declining = 0.90,
    @StableStart = 90,
    @StableEnd = 110,
    @Increasing = 110;

DECLARE @TrendValue FLOAT; 

UPDATE R
SET R.TtlNOfCmpSaleSpanFirst =
    CASE
        WHEN R.TtlNOfCmpSale7to12 != 0 THEN
            SET @TrendValue = R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12
            (
                CASE 
                    WHEN @TrendValue > @Increasing THEN 0
                    WHEN @TrendValue >  @StableStart 
                        AND @TrendValue <  @StableEND THEN 1
                    WHEN @TrendValue < @Declining THEN 2
                END
            )
    END,
    R.TtlNOfCmpSaleSpanSecond = 
        CASE 
            WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END,
    R.TtlNOfCmpSaleSpanThird = 
        CASE 
            WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
                (
                    CASE 
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart 
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END
FROM Result R;

Seria melhor se houvesse outra maneira de armazenar o resultado da expressão em @TrendValue para que não houvesse necessidade de calcular essa expressão todas as vezes.

Também seria bom evitar erros de divisão por zero (é por isso que adicionei as várias R.TtlNOfCmpSale7to12 != 0verificações).

Eu defini functioncom table value parameterqual returns medianconforme os valores passados ​​para ele. Functiondefinido como:

    CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY)
    RETURNS INT
    AS
    BEGIN
    <function body>
    END

E Table Type TypeMediandefinição como:

CREATE TYPE [dbo].[TypeMedian] AS TABLE(
    [VALUE] [int] NULL
)

Agora eu tenho um table Listingcom valores preenchidos nele como e table RESULTa ser preenchido de acordo com table Listing:

Tables structureComo

LISTING(ListingCol1,ListingCol2,ListingCol3,ListingCol4,ListingCol5)
RESULT(Col1,Col2,Col3,Col4,Col5)

A tabela de listagem tem mais de 1.000 linhas de dados.

Todos columnde ambos tablessão do tipo int. Agora eu quero preencher columnse RESULT tableisso columnpode ser calculado como:

Col1 = SUM(ListingCol1)
Col2 = SUM(ListingCol2)
Col3 = dbo.fn_GetMedian(ListingCol3)
Col4 = dbo.fn_GetMedian(ListingCol4)
Col5 = dbo.fn_GetMedain(ListingCol5)

E estou fazendo assim:

INSERT INTO RESULT(Col1)
SELECT SUM(ListingCol1)

Update RESULT
SET Col2 = SUM(ListingCol2)

DECLARE @tbl_Median TypeMedian

INSERT INTO @tbl_Median
SELECT ListingCol3
FROM Listing

UPDATE RESULT
SET Col3 = dbo.fn_GetMedian(@tbl_Median)

-- For next column
DELETE FROM @tbl_Median

INSERT INTO @tbl_Median
SELECT ListingCol4
FROM Listing

UPDATE RESULT
SET Col4 = dbo.fn_GetMedian(@tbl_Median); 

--E esta consulta de atualização é repetida para as colunas restantes. Como eu poderia fazer isso em uma única consulta?

Aqui está a função de valor de tabela que declarei como:

CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY)
RETURNS INT
AS
BEGIN
    RETURN 
    (
        Select 
        (
            (
                Select Top 1 Value
                From   
                (
                    Select  Top 50 Percent Value
                    From    @List
                    Where   Value Is NOT NULL
                    Order By Value
                ) As A
                Order By Value DESC
            ) + 
            (
                Select Top 1 Value
                From   
                (
                    Select  Top 50 Percent Value
                    From    @List
                    Where   Value Is NOT NULL
                    Order By Value DESC
                ) As A
                Order By Value Asc
            )
        ) / 2
    )
END

Aqui está a tabela temporária de código e a consulta que estou executando:

DECLARE @Temp Table
(ID INT,ID2 INT)

select fn_GetMedian(ID)
from @Temp

Mas, estou recebendo erro como:

Msg 195, Nível 15, Estado 10, Linha 9
'fn_GetMedian' não é um nome de função reconhecido.

Esta questão está relacionada com:

Como posso passar coluna para função no sql?

Estou calculando mediana como:

DECLARE @TEMP TABLE
(
    ID INT
)

Select 
(
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID
            ) As A
            Order By ID DESC
        ) + 
        (
            Select Top 1 ID
            From   
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID DESC
            ) As A
            Order By ID Asc
        )
) / 2

Consulta acima que eu quero usar. Mas, no meu caso, há tantos columnspara aqueles que quero calcular MEDIAN. Mas acho que seria ruim repetir o bloco de código acima para cada arquivo column. Então, estou tentando definir uma função separada que aceite columnvalores, processe e retorne a mediana. Devo definir table-value-funtionisso ou há outra optimizedmaneira de fazer isso?

Esta questão está relacionada com as seguintes questões:

• Como escrever a função em sql que aceita tabela como entrada e retorna o resultado como tabela?
• Obtendo o erro "deve declarar a variável escalar" na função SQL, mesmo que seja declarada
• por que não é possível executar a função com valor de tabela?
• https://dba.stackexchange.com/questions/127328/how-to-use-udf-function-in-insert-into-select-from-clause
• Calculando várias medianas

Estou alterando functioncomo:

ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY)
RETURNS TypePreviousListingResult 
AS 
BEGIN
    DECLARE @tbl_ListingDateDetails TypePreviousListingResult   
    RETURN @tbl_ListingDateDetails
END
GO

Mas jogando erro como:

Msg 137, Level 16, State 1, Procedure fn_CalculateListing, Line 6
Must declare the scalar variable "@tbl_ListingDateDetails".

Por que está dando erro e pedindo declaração mesmo eu tendo declarado isso antes de usar aquela variável?

TypePreviousListingResult é um tipo de tabela criado como CREATE TYPE TypePreviousListingResult AS TABLE....

Pergunta anterior: Como escrever uma função em sql que aceita tabela como entrada e retorna o resultado como tabela?

Quero passar uma variável de tabela para uma função (que conteria valor diferente em momentos diferentes) como entrada, retornando um resultado calculado na forma de outra tabela, que seria declarada na função.

Algo assim:

CREATE FUNCTION fn_CalculateListing(@ListingData TABLE(ROWID INT,value1 INT, value2 INT))
RETURNS TABLE
AS
BEGIN
    DECLARE @result TABLE
    (
        result1 INT,
        result2 INT
    )

    INSERT INTO @result(result1,result2)
    SELECT value1*value2, value1/value2
    FROM ListingDate

    RETURN @result
END

Mas a sintaxe acima me dá um erro perto da declaração @ListingDate TABLEcomo sintaxe incorreta.

A sintaxe acima está correta ou devo usar outra?

CREATE TABLE TEST2
(
    RowID INT IDENTITY(1,1),
    Ratio INT
)
CREATE TABLE TEST
(
    RowID INT IDENTITY(1,1),
    Col1 INT,
    Col2 INT,
    Col3 INT
)

Agora, eu quero inserir valores table TESTcomo

    INSERT INTO TEST
        (Col1, Col2, Col3)
    values
         (10, 
          col1, 
          col2 * (select Ratio 
                  from TEST2 T2 INNER JOIN TEST T 
                       ON T2.RowID = T.RowID)
         );

Acima queryé o que eu quero. Como poderíamos fazer inserta operação acima usando a maneira otimizada?

Eu peguei a média da data da data e tentei obter o resultado da data média, mas gerou um erro como:Conversion failed when converting date and/or time from character string.

consulta sql executada como:

select 
CONVERT
(
    DATE,
    LEFT(
          convert(
                  varchar(10),
                  AVG(
                        convert(
                                 int, 
                                 convert(
                                           varchar(10), 
                                           OnMarkDate, 
                                           112
                                         )
                                 )
                       )
                  ),8
          )
)
from @tbl_XMLResult

Os valores da coluna são como:

   '2001-10-11',
   '2001-10-03',
   '2001-09-24',
   '2000-05-31',
   '2001-10-15',
   '2008-01-01'

com tipo de dados data.

DECLARE @tbl_XMLResult table
(
    Status varchar(50),
    Address varchar(100),
    ListPrice varchar(25),
    SoldPrice varchar(25),
    YearBuilt varchar(25),
    SF varchar(25),
    Acres varchar(25),
    OnMarkDate varchar(25),
    PendDate varchar(25),
    SaleDate varchar(25),
    DOM varchar(25)
)

DECLARE @XMLRESULT xml

exec usp_ReportResults @query = "759,905,1048,170,725,80129", @ReportName = "GenReport", @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult(Status, Address, ListPrice, SoldPrice,
        YearBuilt, SF, Acres, OnMarkDate, PendDate, SaleDate, DOM)
    SELECT
        Listing.value('(/ListingStatus/text())[1]', 'varchar(50)') AS Status, 
        ReportData.value('(/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address,
        Listing.value('(/ListPrice/text())[1]', 'varchar(50)') AS ListPrice,
        Listing.value('(/ClosePrice/text())[1]', 'varchar(50)')AS SoldPrice,
        Listing.value('(/YearBuilt/text())[1]', 'varchar(50)') AS YearBuilt,
        Listing.value('(/TotalLvSpace/text())[1]', 'varchar(50)') AS SF,
        Listing.value('(/AcresApx/text())[1]', 'varchar(50)') AS Acres,
        Listing.value('(/ListDate/text())[1]', 'varchar(50)') OnMarkDate,
        Listing.value('(/PendingDate/text())[1]', 'varchar(50)') AS PendDate,
        Listing.value('(/CloseDate/text())[1]', 'varchar(50)') AS SaleDate,
        Listing.value('(/DaysOnMarket/text())[1]', 'varchar(50)') DOM
    FROM 
        @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing),
        @XMLRESULT.nodes('/Results/Report/Listings/Listing/PMCReportData') AS TempTable(ReportData)

SELECT *
FROM @tbl_XMLResult

Estou tentando obter o resultado do procedimento armazenado no formato xml e, lendo esse resultado, armazená-lo na tabela temporária criada acima. Mas estou obtendo resultado como nullprocedimento armazenado atual retornando apenas seis registros em xml, mas mostrando 36 linhas na tabela temporária com nullvalores nela. Como consertar isso?

Este é o formato xml:

Estou chamando um procedimento armazenado como este:

usp_ReportResults @query = '759,905,1048,170,725,80129', 
                  @ReportName = 'GenRepot'

onde usp_ReportResulté definido como

CREATE PROCEDURE [dbo].[usp_ReportResults]
     @query VARCHAR(MAX), 
     @ReportName VARCHAR(100), 
     @AutoSelectXML BIT = 1, 
     @XMLResult XML = NULL OUTPUT

Estou tentando obter resultado em xmlvariável para fazer mais processos como:

DECLARE @XMLRESULT xml

SET @XMLRESULT = exec usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName = 'GenRepot'

Mas não consigo obter o resultado @XMLRESULTe não consigo ler e armazenar o resultado dos dados xmlna tabela.