none
Criar sequência RRS feed

  • Pergunta

  • Presados queria criar uma sequência quando o dia de uma data muda, ele recomece a contagem.

    Exemplo

    Data	                        Sequencia
    2017-07-19 16:27:58.8700000	1
    2017-07-19 16:27:58.9070000	2
    2017-07-19 16:27:58.9430000	3
    2017-07-19 16:27:58.9800000	4
    2017-07-19 16:27:59.0300000	5
    2017-07-19 16:27:59.0770000	6
    2017-07-19 16:27:59.1200000	7
    2017-07-19 16:27:59.1630000	8
    2017-07-19 16:27:59.2130000	9
    2017-07-19 16:27:59.2670000	10
    2017-07-20 16:39:54.5500000	1
    2017-07-20 16:39:54.5630000	2
    2017-07-20 16:39:54.5800000	3
    2017-07-20 16:39:54.5970000	4
    2017-07-20 16:39:54.6170000	5
    2017-07-20 16:39:54.6400000	6
    2017-07-20 16:39:54.6630000	7
    2017-07-20 16:39:54.6930000	8
    2017-07-21 15:55:10.3370000	1
    2017-07-21 15:55:10.3530000	2
    2017-07-21 15:55:10.3670000	3
    2017-07-21 15:55:10.3830000	4
    2017-07-21 15:55:10.4030000	5
    2017-07-21 15:55:10.4230000	6
    2017-07-21 15:55:10.4500000	7
    

    sexta-feira, 15 de setembro de 2017 13:24

Respostas

  • veja se isso ajuda

    --2017-07-19 16:27:58.8700000	1
    --2017-07-19 16:27:58.9070000	2
    --2017-07-19 16:27:58.9430000	3
    --2017-07-19 16:27:58.9800000	4
    --2017-07-19 16:27:59.0300000	5
    --2017-07-19 16:27:59.0770000	6
    --2017-07-19 16:27:59.1200000	7
    --2017-07-19 16:27:59.1630000	8
    --2017-07-19 16:27:59.2130000	9
    --2017-07-19 16:27:59.2670000	10
    --2017-07-20 16:39:54.5500000	1
    --2017-07-20 16:39:54.5630000	2
    --2017-07-20 16:39:54.5800000	3
    --2017-07-20 16:39:54.5970000	4
    --2017-07-20 16:39:54.6170000	5
    --2017-07-20 16:39:54.6400000	6
    --2017-07-20 16:39:54.6630000	7
    --2017-07-20 16:39:54.6930000	8
    --2017-07-21 15:55:10.3370000	1
    --2017-07-21 15:55:10.3530000	2
    --2017-07-21 15:55:10.3670000	3
    --2017-07-21 15:55:10.3830000	4
    --2017-07-21 15:55:10.4030000	5
    --2017-07-21 15:55:10.4230000	6
    --2017-07-21 15:55:10.4500000
    
    
    DECLARE @tabela TABLE
    (
    Data DATETIME
    )
    INSERT INTO @tabela
            ( Data )
    VALUES  
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10')
    
    SELECT T.Data,Sequencia = ROW_NUMBER() OVER(PARTITION BY  CAST(T.Data AS DATE) ORDER BY CAST(T.Data AS DATE)  ) FROM @tabela AS T

    Wesley Neves - Brasilia-DF

     
    https://wesleyneves.wordpress.com/
    MTA-SQL Server
    MTA- Web Development
    Analista Desenvolvedor.NET
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"





    Wesley Neves

    sexta-feira, 15 de setembro de 2017 13:37

Todas as Respostas

  • veja se isso ajuda

    --2017-07-19 16:27:58.8700000	1
    --2017-07-19 16:27:58.9070000	2
    --2017-07-19 16:27:58.9430000	3
    --2017-07-19 16:27:58.9800000	4
    --2017-07-19 16:27:59.0300000	5
    --2017-07-19 16:27:59.0770000	6
    --2017-07-19 16:27:59.1200000	7
    --2017-07-19 16:27:59.1630000	8
    --2017-07-19 16:27:59.2130000	9
    --2017-07-19 16:27:59.2670000	10
    --2017-07-20 16:39:54.5500000	1
    --2017-07-20 16:39:54.5630000	2
    --2017-07-20 16:39:54.5800000	3
    --2017-07-20 16:39:54.5970000	4
    --2017-07-20 16:39:54.6170000	5
    --2017-07-20 16:39:54.6400000	6
    --2017-07-20 16:39:54.6630000	7
    --2017-07-20 16:39:54.6930000	8
    --2017-07-21 15:55:10.3370000	1
    --2017-07-21 15:55:10.3530000	2
    --2017-07-21 15:55:10.3670000	3
    --2017-07-21 15:55:10.3830000	4
    --2017-07-21 15:55:10.4030000	5
    --2017-07-21 15:55:10.4230000	6
    --2017-07-21 15:55:10.4500000
    
    
    DECLARE @tabela TABLE
    (
    Data DATETIME
    )
    INSERT INTO @tabela
            ( Data )
    VALUES  
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:58'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-19 16:27:59'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-20 16:39:54'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10'),
    ('2017-07-21 15:55:10')
    
    SELECT T.Data,Sequencia = ROW_NUMBER() OVER(PARTITION BY  CAST(T.Data AS DATE) ORDER BY CAST(T.Data AS DATE)  ) FROM @tabela AS T

    Wesley Neves - Brasilia-DF

     
    https://wesleyneves.wordpress.com/
    MTA-SQL Server
    MTA- Web Development
    Analista Desenvolvedor.NET
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"





    Wesley Neves

    sexta-feira, 15 de setembro de 2017 13:37
  • Silas,

    Se as suas datas já existem em alguma tabela e você só quer retornar a "Sequência" numa consulta, utilize o SELECT que o Wesley Neves passou.

    Agora se você quer inserir os dados de Data + Sequência como você está fazendo? Utilizando um GETDATE() para data e a sequência vc obtém o último + 1?


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    sexta-feira, 15 de setembro de 2017 16:10
  • Mariana, o problema é que dessa forma não reinicia quando troca a data

    Carlos Henrique Quadrado - Lençóis Paulista - SP - Brasil

    ITIL v3 | ITMP |

    Caso a resposta tenha sido útil clique em Votar. Caso a resposta tenha resolvido sua necessidade marque como Resposta. smile

    Please use Vote As Helpful if a post was useful or use Marked as Answer if my post solved your necessity.  smile

    sexta-feira, 15 de setembro de 2017 17:21