obautista's questions

Como você faz a correspondência de padrões usando cte?

Eu tentei o seguinte:

with cte1 as (
    select '''' || '{' || accountids || '}' || '''' as accountids
    from table_a
    where email = '[email protected]'
)
select id 
from table_a
where accountids ^@ ANY (select accountids from cte1);

Eu esperaria que o conjunto de resultados retornasse a12 e c45. Isso é possível?

Não trabalhei com Pivot. Não tenho certeza se o pivô é a coisa certa a usar. É possível mostrar esses dados:

-------------------
| Name     | Code |
-------------------
|Test A    | 1    |
|Test B    | 2    |
|Test C    | 3    |
-------------------

Neste formato:

------------------------------
| Customer | Fleet | Account |
------------------------------
|Test A    |Test B |Test C   |
------------------------------

Se Código for 1 é Cliente. Se o Código for 2, é Frota. Se o Código for 3 é Conta.

Eu tenho uma tabela armazenando registros pai/filho como tal:

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6953  | Ajit       |   AB7   | comp2   |  7060      |     1     |
| 6957  | Rahul      |   DE1   | comp3   |  7060      |     1     |
| 6958  | uday       |   TG6   | comp4   |  7060      |     1     |
| 6959  | john       |   HY7   | comp5   |  7060      |     1     |
| 6960  | netaji     |   HY5   | comp6   |  7060      |     1     |
| 6961  | prakriti   |   GT6   | comp7   |  7060      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
| 6963  | santosh    |   KK5   | comp9   |  7060      |     1     |
| 6964  | Ravi       |   PP0   | comp10  |  7060      |     1     |
+-------+------------+---------+---------+------------+-----------+

É possível retornar registros em que o registro pai está registrado e o registro filho relacionado não está registrado?

Isso retorna o que eu preciso para 1 cliente específico:

select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.company = 'comp1' and a.Enrolled = 1
union all
select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.parentcustid= 7060 and b.Enrolled = 0

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
+-------+------------+---------+---------+------------+-----------+

Como posso estruturar a consulta para retornar esse tipo de conjunto de resultados para todos os registros pai filho na tabela?

É possível estruturar uma única consulta que retornaria 0 ou 1 (verdadeiro/falso) comparando o primeiro registro com os registros anteriores?

Exemplos de registros na tabela:

ProductID    SampleDate    RunFailure    LevelCode
101          20160322      0             2
101          20160321      0             3
101          20160320      1             2
101          20160318      1             2
102          20160317      0             3
102          20160316      0             2
102          20160315      0             1
103          20160314      1             1
103          20160313      0             2
103          20160312      1             3

Preciso avaliar o registro mais recente por ProductID. No exemplo acima, o último registro (em 20160322) tem um LevelCode de 2. Ele precisa ser comparado ao registro em 20160320 porque é o último registro que falhou com o mesmo LevelCode (portanto, ignoraria o registro em 20160318 porque é mais antigo que o registro do dia 20), portanto, neste caso a consulta retornaria 1 (ou verdadeiro).

Para ProductID 102, a consulta retornaria 0 (ou falso) porque o LevelCode em 20160317 é 3 e não há um registro mais antigo que falhou com o mesmo LevelCode.

ProductID 103 retornaria um 0 (falso) porque em 20160314 o LevelCode é 1 e o último registro para esse ProductID que falhou foi em 20160312 foi para LevelCode3

Idealmente, a consulta retornaria algo como isto:

ProductID    IsMostCurrentRunSameLevelAsLastFailedRun
101          1
102          0
103          0

Existe uma maneira de avaliar os dados em uma tabela para derivar se em um conjunto de ProductIDs um PRODUCTID passou ou falhou? Aqui está um exemplo dos meus dados:

DECLARE @example TABLE (ID INT NOT NULL, PRODUCTID INT, PASSED BIT, LEVEL INT, CREATEDATE DATETIME, PRIMARY KEY (ID))
INSERT INTO @example VALUES
 (  1, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  2, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  3, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  4, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  5, 101, 1, 1, '2016-01-10 07:35:02.123' )
,(  6, 102, 0, 1, '2016-01-10 07:35:02.123' )
,(  7, 102, 1, 2, '2016-01-10 07:35:02.123' )
,(  8, 103, 1, 1, '2016-01-10 10:35:02.123' )
,(  9, 103, 1, 2, '2016-01-10 09:35:02.123' )  
,( 10, 103, 1, 2, '2016-01-10 08:35:02.123' ) 
,( 11, 103, 1, 3, '2016-01-10 07:35:02.123' )  

É possível estruturar uma consulta que retorne:

PRODUCTID    ISPASSED
100          0
101          0
102          0
103          1

As condições são: 2 APROVADOS para 2 NÍVEIS diferentes para um determinado PRODUCTID.

• 100 é um 0 porque todos os 4 registros são para NÍVEL 1
• 101 é um 0 porque há apenas 1 PASS e para apenas 1 NÍVEL
• 102 é um 0 porque um registro PASSOU e o outro falhou
• 103 é 1 porque existem 2 registros PASSED para NÍVEIS diferentes.

Muito obrigado!

EDITAR:

Eu criei esta consulta, mas ela é curta porque só consigo qualificar a primeira parte dos requisitos:

WITH cte(PRODUCTID, ISPASSED) AS
(
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) = 2 THEN 1 ELSE 0 END AS ISPASSED
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC), LEVEL, PASSED
    FROM @example
    GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
  WHERE RowNumber <=2
  GROUP BY PRODUCTID
)
select * from cte

Existe uma maneira de agrupar e comparar o registro atual com os registros anteriores ao ler de uma tabela. Minha tabela é algo como abaixo.

ID    PASSED    LEVEL    CREATEDATE
100   1         2        2016-01-10 07:35:02.123
100   1         2        2016-01-10 07:05:33.135
100   0         2        2016-01-09 08:35:29.123
100   1         2        2016-01-08 09:35:30.123
100   1         1        2016-01-07 11:35:31.123
101   1         1        2016-01-10 08:45:12.123
102   1         1        2016-01-10 09:45:22.222
102   1         1        2016-01-09 08:15:29.123
103   0         1        2016-01-10 07:35:02.123
103   1         2        2016-01-09 06:35:29.023

Idealmente, preciso retornar um valor BIT para cada ID na tabela.

A condição do valor BIT é retornar 1 para cada ID quando PASSED é 1 e os NÍVEIS são diferentes por ordem CREATEDATE DESC.

Então, na minha tabela de exemplo, o valor BIT para

• ID 100 retornaria 1 porque PASSED é 1 no primeiro e no quarto registro (o nível muda no quarto registro).
• ID 101 retornaria um valor BIT de 0 porque há apenas 1 registro PASSED com valor 1.
• ID 102 retornaria um valor de BIT de 0 porque os NÍVEIS são os mesmos para ambos os registros, embora PASSED fosse 1 em ambos os registros.
• O ID 103 retornaria 0 porque ambos os registros não PASSARAM, embora os NÍVEIS sejam diferentes.

Esperando que uma consulta possa ser estruturada de forma que a resposta seja assim:

ID    STATUS_PASSED
100   1
101   0
102   0
103   0

Obrigado por qualquer ajuda fornecida.

Preciso retornar um 0 ou 1 dependente dos registros anteriores. Tabela de exemplo:

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);
INSERT @x VALUES
(101, 0, '20151201', 1),    
(101, 1, '20151205', 2),    
(101, 0, '20151206', 3),
(101, 1, '20151208', 2), 
(102, 1, '20151202', 1),
(102, 0, '20151204', 2),    
(102, 0, '20151205', 3),
(103, 0, '20160101', 1),
(103, 1, '20160102', 2),
(103, 0, '20160103', 2),
(104, 0, '20160101', 1),
(104, 0, '20160102', 2),
(104, 0, '20160103', 3);

A única coisa que importa no último registro é o LevelCode (ou seja, o último registro de cada ProductID). Não importa se o último registro foi aprovado/reprovado. Em seguida, examinamos todos os outros registros para esse ProductID (portanto, todos os registros antes do último registro) e, se houver uma falha com o mesmo LevelCode do último registro, definimos IsLastRunSameLevelAsPreviousRun como 1 senão 0:

 ProductID     IsLastRunSameLevelAsPreviousRun
 101           1
 102           0
 103           1
 104           0

Se não houver falhas para a , ProductIDo IsLastRunSameLevelAsPreviousRundeve retornar 0.

Qualquer ajuda ou dicas são muito apreciadas.

Como posso comparar o valor de registros anteriores?

Mesa:

ID    SAMPLEDATE    LEVEL    PASSED
1     1/1/2016      0        1
2     1/2/2016      1        1
3     1/3/2016      2        0
4     1/4/2016      1        1
5     1/5/2016      2        0

O último registro (ordenado por SAMPLEDATE) é igual LEVELao último registro que falhou? Se sim, retorne TRUE, caso contrário, retorne FALSE.

Então, nos meus dados de exemplo, o último registro que falhou com LEVEL2. Então o último registro (antes do registro 05/01/2016) que falhou em 03/01/2016 era o mesmo LEVEL, então TRUE seria retornado. Se fosse diferente, seria retornado um NÍVEL FALSO.

Como comparo a diferença de timestamp entre 2 registros consecutivos?

Este é o meu exemplo:

DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date);
INSERT @x VALUES
(101, 1, '2016-01-01 10:09:19.617'),    
(101, 0, '2016-01-05 11:09:19.617'),    
(101, 1, '2016-01-07 12:09:19.617'),
(101, 0, '2016-01-09 12:09:19.617'),    
(101, 1, '2016-01-15 13:09:19.617'),    
(101, 0, '2016-01-17 13:09:19.617'),
(101, 0, '2016-01-29 14:09:19.617'),    
(101, 0, '2016-02-02 15:09:19.617');

;WITH cte(ProductID, CCVPassed)
AS
(      
   SELECT ProductID, CASE WHEN SUM(LastTwoRunsPass) >= 2 THEN 1 ELSE 0 END
   FROM (SELECT ProductID, LastTwoRunsPass = CASE WHEN SingleRunFailure = 0 THEN 1 ELSE 0 END,           
             RowNumber = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC)
        FROM @x) AS aggregation
   WHERE RowNumber <= 2
   GROUP BY ProductID   
)

SELECT * FROM cte;

Isso me retorna um Pass ou Fail onde o sinalizador SingleRunFailure era falso nos últimos 2 registros. Esta parte funciona como esperado.

Preciso adicionar uma lógica onde, se a diferença SampleDate nos últimos 2 registros for menor que 15 minutos, retorne True.

Resposta esperada:

ProductID    CCVPassed
101          0

Isso ocorre porque a diferença entre os últimos 2 registros é de 4 dias. Se a diferença SampleDate entre os 2 últimos registros for menor que 15 minutos, CCVPassed retornará 1.

ID    MODEL    MODELSTATUS    CONTROL
11     100      GOOD           XN
24     100      TRENDING       BF
33     101      GOOD           XN
46     102      BAD            BF
50     103      BAD            XN
64     103      BAD            BF
77     104      PENDING        XN
89     104      TRENDING       BF
92     105      TRENDING       BF
93     105      TRENDING       XN

Dados os dados acima, como eu retornaria os resultados abaixo. Pode haver 1 ou 2 (no máximo) registros com o mesmo MODELO. Se houver 2 registros para um MODEL o CONTROL será diferente.

Se MODESTATUS for 'Trending', retorne esse registro. Caso contrário, se MODELSTATUS for 'Pendente' ou 'Bad', retorne esse registro. Else se houver 2 registros para um MODEL onde MODELSTATUS são o mesmo registro de retorno com CONTROL é 'XN'

ID    MODEL    MODELSTATUS    CONTROL
24    100      TRENDING       BF
33    101      GOOD           XN
46    102      BAD            BF
50    103      BAD            XN
77    104      PENDING        BF
93    105      TRENDING       XN

Como eu retornaria TRUE ou FALSE se quatro dos últimos seis registros falhassem para um determinado MAKEID? Portanto, deve haver 4 falhas (ou registros FALSE) nos últimos seis

MAKEID       FAILURES    CREATEDATE
101          TRUE        12/1/2015
101          FALSE       12/5/2015
101          TRUE        12/6/2015
101          FALSE       12/8/2015
101          TRUE        12/10/2015
101          FALSE       12/15/2015
101          FALSE       1/1/2016
101          FALSE       1/2/2016
102          TRUE        12/2/2015
102          TRUE        12/4/2015
102          FALSE       12/5/2015
102          TRUE        12/6/2015
102          FALSE       12/7/2015
102          TRUE        1/1/2016

A consulta retornaria o seguinte:

MAKEID    FOUROFLASTSIXFAILED
101       TRUE
102       FALSE

O requisito é que haja 4 ou mais falhas nos últimos seis registros. Portanto, se houver 4, 5 ou 6 falhas nos últimos seis registros, FOUROFLASTSIXFAILED retornará TRUE. Se houvesse apenas 1, 2, 3 registros para um determinado MAKEID, retornaria FALSE, pois é necessário que haja pelo menos 4 falhas nos seis registros anteriores.

Estou perplexo. Preciso retornar um TRUE ou FALSE por ProductId se os 2 registros mais recentes criados forem 'pass' e os valores de LEVEL forem diferentes.

PRODUCTID  CREATEDATE    STATUS    LEVEL
ABC        1/3/2016      fail      1
ABC        1/4/2016      pass      2
ABC        1/5/2016      pass      3
DEF        1/1/2016      pass      1
DEF        1/2/2016      pass      1
DEF        1/10/2016     pass      1
DEF        1/11/2016     fail      1
GHI        12/29/2015    pass      1
GHI        12/30/2015    fail      1
JKL        1/1/2016      pass      1
JKL        1/2/2016      pass      1

Com base nos valores acima, o seguinte seria retornado:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE

Qualquer ajuda é muito apreciada.

• POST ATUALIZADO com lógica adicional.

Eu adicionei a coluna LEVEL. A consulta precisa retornar conforme descrito acima.

Retorne TRUE ou FALSE por ProductId se os 2 registros mais recentes criados forem 'pass' e os valores LEVEL forem diferentes.

Se um ProductID tiver apenas um total de registro e for PASS, será FALSE. Deve haver dois 'passes' seguidos.

Estou preso em como retornar um valor de bit com base em registros com determinados valores. A Consulta precisa retornar VERDADEIRO ou FALSO se houver três Níveis diferentes do mesmo Controle:

Dados de exemplo:

PRODUCT    CONTROL    LEVEL
test1      8          1
test1      8          2
test1      8          3
test2      8          1
test2      8          1
test2      8          3
test4      8          1
test4      8          3
test4      8          7

Preciso de uma consulta que retorne:

PRODUCT    STATUS
test1      PASS
test2      FAIL
test4      PASS

Não sei por onde começar. Quaisquer dicas são imensamente apreciadas.

Estou tentando encontrar a maneira mais eficiente de pesquisar registros em uma coleção de cerca de 130.000 registros no total. Resumindo, temos cerca de 130.000 registros do Active Directory. Estou construindo um site que procura usuários no AD. Gostaria de implementar a funcionalidade TypeAhead em uma caixa de pesquisa. Eu poderia consultar o AD diretamente no código, mas estou pensando em despejar os dados no SQL Server e consultá-los. O TypeAhead faz uma pesquisa enquanto você digita os caracteres na caixa de texto. É eficiente pesquisar uma tabela de 130.000 registros por caracteres únicos? Estou tentando determinar se a pesquisa deve ser feita pelo mecanismo do SQL Server ou no site? Estou pensando no SQL Server. Se verdadeiro, é melhor usar pesquisas de texto completo FREETEXT ou CONTÉM?

Curioso para saber o que a comunidade pensa sobre isso? Ou estou longe?