none
Como transformar um período da Data julian em texto "ANO" Exemplo De: 118001 ate 118365 em "2018" RRS feed

  • Pergunta

  • Boa tarde!

    Alguém poderia me informar o que está errado no comando abaixo? 

    CASE WHEN abupmj  in ('118001' AND'118366') then '2018'

    Preciso trazer um pedrio da data julain no ano correspondente.

    Exemplo:

    Quero que todos os registros que o campo ABUPMJ estaja dentro de 118001 até 118365 me traga com o texto "2018"

    Hoje consigo extrair o total, porém gostaria que ele faça o count por ANO

    Minha SQL está desta forma:

    select tipo , count(TIPO) as TOTAL FROM
    (select DISTINCT CASE WHEN abat1 IN ('E') then 'CADASTRO DE FUNCIONARIO' when abat1 IN ('DM') then 'CADASTRO DE CLIENTE' when abat1 IN ('S','SR','SI','XA','TAX','SIR') then 'CADASTRO DE FORNECEDOR'  else ' ' end as TIPO,
    aban8,abalph, abalky, abtax, abat1,Case when abat1 IN ('E') then 'CADASTRO DE FUNCIONARIO' when abat1 IN ('DM') then 'CADASTRO DE CLIENTE' when abat1 IN 'S' then 'FORNECEDOR DIRETO'
    when abat1 IN ('SR') then 'FORNECEDOR FACTORY DIRETO' when abat1 in ('SI') then 'FORNECEDOR INDIRETOS' when abat1 IN('XA') then 'FORNECEDOR BLOQUADO'
    when abat1 IN ('TAX') then 'FORNECEDOR INDIRETOS'  when abat1 IN ('SL') then 'SALES LEADER'  when abat1 IN ('O') then 'AVON COMPANY' when abat1 IN ('SIR') then 'FORNECEDOR FACTORY INDIRETO' else ' ' end as TIPO_DE_CADASTRO,
    ABTX2, ABUSER, TO_CHAR(TO_DATE(TO_CHAR(abupmj+1900000),'YYYYDDD'), 'DD/MM/YYYY') AS DATA_CADASTRO ,abupmj
    from proddta.f0101
    where ABAT1 NOT IN ('SL','U','XU','SP','RE','C', 'F', 'IM','O','X','L')
    AND abupmj  between 118001 and 118366 order by abupmj DESC, aban8)
    group by TIPO


    terça-feira, 27 de fevereiro de 2018 18:14

Respostas

Todas as Respostas

  • Deleted
    terça-feira, 27 de fevereiro de 2018 18:54
  • Eu consegui incluir o período, mas não consigo fazer que no Count em vez do Ttotal eme me mostre o valor de ano a no como no Exemplo?

    TIPO                                              2016     2017   2018

    CADASTRO DE CLIENTE                     1000     2500   800

    CADASTRO DE FORNECEDORES            500      800   700

    ACcredito que o problema esteja no inicio da SQL

    select TIPO  , count(TIPO) as TOTAL FROM


    terça-feira, 27 de fevereiro de 2018 19:47
  • Flavio,

    Veja se esta função te ajuda:

    create FUNCTION dbo.JulianDate_To_StandardDate(@JulianDate INTEGER)
    RETURNS smalldatetime 
    
    
    AS 
    BEGIN
    
    
    Declare @year  as int 
    Declare @dayofyear  as int
    
    
    Declare @STD_Dt  as smalldatetime 
    Declare @DayoftheMonth  as int
    
    
     select @year =(case when (0+(LEFT(@JulianDate,2))<30)  then 2000  else 1900 end ) +  cast(substring( (case when len(ltrim(rtrim(@JulianDate)))<6 then '0'+ cast(@JulianDate as varchar) else cast(@JulianDate as varchar) end)  ,2,2) as int)
     set @dayofyear = RIGHT(@JulianDate,3)
    
    
    
    Declare @MonthoftheYear  as varchar(3)
    
    
    declare @IsLeap as bit
    declare @Jan as int
    declare @Feb as int
    declare @Mar as int
    declare @Apr as int
    declare @May as int
    declare @Jun as int
    declare @Jul as int
    declare @Aug as int
    declare @Sep as int
    declare @Oct as int
    declare @Nov as int
    declare @Dec as int
    
    
    declare @JanStart as int
    declare @FebStart as int
    declare @MarStart as int
    declare @AprStart as int
    declare @MayStart as int
    declare @JunStart as int
    declare @JulStart as int
    declare @AugStart as int
    declare @SepStart as int
    declare @OctStart as int
    declare @NovStart as int
    declare @DecStart as int
    declare @DecEnd as int
    
    
    declare @Months as int 
    
    
    declare @MonthStart as int 
    declare @MonthEnd as int 
    
    
    set @Months = 1 
    
    
    set @Jan = 31
    set @Feb = 28
    set @Mar = 31
    set @Apr = 30
    set @May = 31
    set @Jun = 30
    set @Jul = 31
    set @Aug = 31
    set @Sep = 30
    set @Oct = 31
    set @Nov = 30
    set @Dec = 31
    
    
    
    
      IF @year % 400 = 0
         -- Years divisible by 400 (e.g. 1600, 2000) are always leap years
         set @IsLeap = 1 
      ELSE
      BEGIN
        IF @year % 100 = 0
           -- Years not divisible by 400 but divisible by 100 (e.g. 1900) are never leap years
         set @IsLeap =0
        ELSE
        BEGIN
          IF @year % 4 = 0
             -- Years not divisible by 400 or 100 but divisible by 4 (e.g. 1976) are always leap years
         set @IsLeap =1
          ELSE
         set @IsLeap =0
        END
      END
    
    
    if @IsLeap = 1
            set @Feb = 29
    else 
            set @Feb = 28
    
    
    
    
    set  @JanStart = 1
    set  @FebStart = @Jan
    set  @MarStart =@Jan+@Feb 
    set  @AprStart =@Jan+@Feb+@Mar 
    set  @MayStart =@Jan+@Feb+@Mar+@Apr
    set  @JunStart =@Jan+@Feb+@Mar+@Apr+@May 
    set  @JulStart =@Jan+@Feb+@Mar+@Apr+@May+@Jun 
    set  @AugStart =@Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul 
    set  @SepStart =@Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug 
    set  @OctStart =@Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug+@Sep 
    set  @NovStart =@Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug+@Sep+@Oct 
    set  @DecStart =@Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug+@Sep+@Oct+@Nov 
    
    
    
    
    WHILE (@Months) < 13
    BEGIN
    
    
    set @MonthStart = case @Months when 1 then @JanStart
                                    when 2 then @FebStart
                                    when 3 then @MarStart
                                    when 4 then @AprStart
                                    when 5 then @MayStart
                                    when 6 then @JunStart
                                    when 7 then @JulStart
                                    when 8 then @AugStart
                                    when 9 then @SepStart
                                    when 10 then @OctStart
                                    when 11 then @NovStart
                                    when 12 then @DecStart end
    
    
    
    set @MonthEnd = case @Months    when 1 then @Jan 
                                    when 2 then @Jan+@Feb 
                                    when 3 then @Jan+@Feb+@Mar 
                                    when 4 then @Jan+@Feb+@Mar+@Apr
                                    when 5 then @Jan+@Feb+@Mar+@Apr+@May 
                                    when 6 then @Jan+@Feb+@Mar+@Apr+@May+@Jun 
                                    when 7 then @Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul
                                    when 8 then @Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug
                                    when 9 then @Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug+@Sep
                                    when 10 then @Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug+@Sep+@Oct
                                    when 11 then @Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug+@Sep+@Oct+@Nov 
                                    when 12 then @Jan+@Feb+@Mar+@Apr+@May+@Jun+@Jul+@Aug+@Sep+@Oct+@Nov+@Dec end
    
    
    
    
       IF ( @dayofyear  >=@MonthStart and @dayofyear <=@MonthEnd )
            begin
    
    
    
    set @DayoftheMonth = case @Months when 1 then 0
                                    when 2 then @FebStart 
                                    when 3 then @MarStart 
                                    when 4 then @AprStart 
                                    when 5 then @MayStart 
                                    when 6 then @JunStart 
                                    when 7 then @JulStart 
                                    when 8 then @AugStart 
                                    when 9 then @SepStart 
                                    when 10 then @OctStart 
                                    when 11 then @NovStart 
                                    when 12 then @DecStart  end
    
    
    
    set @MonthoftheYear = case @Months      when 1 then 'Jan'
                                    when 2 then 'Feb'  
                                    when 3 then 'Mar'
                                    when 4 then 'Apr'
                                    when 5 then 'May' 
                                    when 6 then 'Jun' 
                                    when 7 then 'Jul'
                                    when 8 then 'Aug'
                                    when 9 then 'Sep'
                                    when 10 then 'Oct'
                                    when 11 then 'Nov' 
                                    when 12 then 'Dec' end
    
    
    
    set @DayoftheMonth = @dayofyear - @DayoftheMonth 
    
    
    set @STD_Dt = cast(@DayoftheMonth as varchar(2) )+'-'+cast(@MonthoftheYear as varchar(3))+'-'+cast(ltrim(rtrim(@year)) as varchar(4))
    
    
          BREAK
            end
    
    
    
    set @Months = @Months + 1 
    
    
    END
    
    
    return @STD_Dt
    
    
    END
    GO
    
    
    
    


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 28 de fevereiro de 2018 12:52
  • Deleted
    quarta-feira, 28 de fevereiro de 2018 23:49