Jump to content

Archived

This topic is now archived and is closed to further replies.

asacap1000

calcular períodos no select.

Recommended Posts

Amigos, estou com uma tarefa que faz parte do faturamento da empresa. A empresa cobra por períodos de 10 em 10 dias o armazenamento. Assim a empresa me solicitou um relatório em que trouxesse a quantidade de períodos cobrados dentro de uma certa data.

 

Tenho este select...

select distinct x.di_dde,
                (select min(io.time_in)
                   from in_out io, in_out_pos iop, we
                  where io.lager = iop.lager
                    and io.id_in_out = iop.id_in_out
                    and iop.lager = we.lager
                    and iop.typ_document = we.typ_doc
                    and iop.document = we.nr_doc
                    and io.art_in_out = 'LI'
                    and we.nr_lieferschein = x.lote) dt_entrada,
                (select dbr.time_desemb
                   from desmembr dbr
                  where dbr.nr_di = x.di_dde
                 union
                 select drg.time_neu
                   from dde_reg drg
                  where drg.nr_dde = x.di_dde) REGISTRO_DI_DDE,
                'COBERTURA',
                (select max(io.time_out)
                   from in_out io, in_out_pos iop
                  where io.lager = iop.lager
                    and io.id_in_out = iop.id_in_out
                    and iop.typ_document in ('DI', 'DDE')
                    and iop.document = x.di_dde) dt_saida,
                x.conhec,
                x.id_artikel produto,
                (select distinct sum (dbrpos.mng_desmembr)
                   from desmembr dbr, desmembrpos dbrpos
                  where dbr.nr_di = x.di_dde
                  and dbr.id_desmemb = dbrpos.id_desmemb
                 union
                 select drg.amount_total
                   from dde_reg drg
                  where drg.nr_dde = x.di_dde)quant,
                y.taxa_dolar,
                (decode(nvl(x.di_dde, '0'), '0', z.valor_cif, y.valor)) valor_cif_US$,
                (y.taxa_dolar *
                decode(nvl(x.di_dde, '0'), '0', z.valor_cif, y.valor)) valor_cif_R$,
                'PERÍODO',
                'PEÇAS',
                'PALLET',
                max(x.vlr_armaz) vlr_armaz,
                max(x.vlr_ger_risco) vlr_ger_risco,
                max(x.vlr_pesagem) vlr_pesagem,
                (SELECT count(io.id_in_out) nr_entrada
                   FROM desmembr   dd,
                        in_out_pos iop,
                        in_out     io,
                        spediteure s,
                        vehicle    v,
                        driver     d
                  WHERE dd.lote_ad = x.lote
                    AND iop.document = x.di_dde
                    AND dd.typ_process = 'DINACI'
                    AND dd.lager = iop.lager
                    AND 'DI' = iop.typ_document
                    AND dd.nr_di = iop.document
                    AND iop.lager = io.lager
                    AND iop.id_in_out = io.id_in_out
                    AND io.art_in_out IN ('CA', 'CC', 'CCE')
                    AND io.stat <> '80'
                    AND s.lager = io.lager
                    AND s.id_spediteur = io.id_spediteur
                    AND v.id_vehicle = io.id_vehicle
                    AND d.lager = io.lager
                    AND d.id_driver = io.id_driver) veiculos,
                
                max(x.vlr_movimentacao) vlr_movimentacao,
                x.lote,
                nvl(max(x.vlr_armaz), 0) + nvl(max(x.vlr_ger_risco), 0) +
                nvl(max(x.vlr_movimentacao), 0) +
                nvl(max(x.vlr_desunit), 0) + nvl(max(x.vlr_pesagem), 0) +
                nvl(max(x.vlr_averbacao), 0) +
                nvl(max(x.vlr_fat_minimo), 0) +
                nvl(max(x.vlr_outros_serv), 0) vlr_tot_nf,
                x.nr_nf

  from (select distinct dsbp.id_artikel,
                        sum(dsbp.mng_desmembr) quant,
                        bo.lager,
                        bo.id_os,
                        k.id_klient,
                        dsb.cod_conhec conhec,
                        dsb.time_desemb,
                        decode(length(k.suchbegriff),
                               14,
                               substr(k.suchbegriff, 1, 2) || '.' ||
                               substr(k.suchbegriff, 3, 3) || '.' ||
                               substr(k.suchbegriff, 6, 3) || '/' ||
                               substr(k.suchbegriff, 9, 4) || '-' ||
                               substr(k.suchbegriff, 13, 2),
                               substr(k.suchbegriff, 1, 3) || '.' ||
                               substr(k.suchbegriff, 4, 3) || '.' ||
                               substr(k.suchbegriff, 7, 3) || '-' ||
                               substr(k.suchbegriff, 10, 2)) cnpj_cpf,
                        k.name,
                        bi.div_1 lote,
                        bi.div_10 di_dde,
                        bo.nr_nf,
                        bo.date_bill,
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and osi.billite in
                                ('01.01', '01.02', '01.03', '01.04', '01.05')) vlr_armaz,
                        
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and osi.billite in ('03.04')) VLR_PALLET,
                        
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and osi.billite in
                                ('02.01', '02.02', '02.03', '02.04', '02.05')) vlr_ger_risco,
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and osi.billite in
                                ('03.01', '03.02', '03.04', '03.05')) vlr_movimentacao,
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and osi.billite in ('03.03', '03.11', '03.13')) vlr_desunit,
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and osi.billite in
                                ('04.01', '04.02', '04.03', '04.04')) vlr_pesagem,
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and osi.billite in ('05.01', '05.02', '05.03')) vlr_averbacao,
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.billite in ('06.01')) vlr_fat_minimo,
                        (select sum(round(osi.preis, 2))
                           from bill_os_item osi
                          where osi.lager = bo.lager
                            and osi.id_klient = k.id_klient
                            and osi.id_os = bo.id_os
                            and osi.billite = bi.billite
                            and osi.div_1 = bi.div_1
                            and nvl(osi.div_10, 'x') = nvl(bi.div_10, 'x')
                            and substr(osi.billite, 1, 2) > '07') vlr_outros_serv
          from bill_os      bo,
               bill_os_item bi,
               klienten     k,
               desmembr     dsb,
               desmembrpos  dsbp
         where bo.lager = bi.lager
           and bo.id_klient = bi.id_klient
           and bi.div_1 = dsb.lote_ad
           and bo.id_os = bi.id_os
           and bo.lager = k.lager
           and dsb.id_desmemb = dsbp.id_desmemb
           and bo.id_klient = k.id_klient
           and bo.status = '90'
           and bo.lager = 'PSI'
         group by bo.lager,
                  bo.id_os,
                  k.id_klient,
                  decode(length(k.suchbegriff),
                         14,
                         substr(k.suchbegriff, 1, 2) || '.' ||
                         substr(k.suchbegriff, 3, 3) || '.' ||
                         substr(k.suchbegriff, 6, 3) || '/' ||
                         substr(k.suchbegriff, 9, 4) || '-' ||
                         substr(k.suchbegriff, 13, 2),
                         substr(k.suchbegriff, 1, 3) || '.' ||
                         substr(k.suchbegriff, 4, 3) || '.' ||
                         substr(k.suchbegriff, 7, 3) || '-' ||
                         substr(k.suchbegriff, 10, 2)),
                  k.name,
                  bo.nr_nf,
                  bo.date_bill,
                  bi.billite,
                  bi.div_1,
                  bi.div_10,
                  dsbp.id_artikel,
                  dsb.cod_conhec,
                  dsb.time_desemb) x,
       (select MAX(d.valor_nf) valor,
               d.nr_di doc,
               d.id_klient,
               d.lager,
               D.DOLLAR_COTATION taxa_dolar
          from desmembr d
         where d.typ_process = 'DINACI'
         group by d.nr_di, d.id_klient, d.lager, D.DOLLAR_COTATION
        union
        select dde.preis     valor,
               dde.nr_dde    doc,
               dde.id_klient,
               dde.lager,
               NULL
          from dde_reg dde) y,
       (select a.valor_cif, a.bruecke_1, a.id_klient, a.lager, NULL
          from anliefpos a) z
 where x.lager = z.lager(+)
   and x.id_klient = z.id_klient(+)
   and x.lote = z.bruecke_1(+)
   and TRIM(x.di_dde) = TRIM(y.doc(+))
      --and (x.id_klient = '&cliente')
      --and trunc(x.cobertura) between '&inicio' and '&fim'
   and x.nr_nf = '68968'
   and x.di_dde = '1424526789'
      --and x.id_artikel = 'CZ181-60118'
   --and x.conhec = 'SUDUN4KSZKG2974X'
 group by x.lager,
          x.id_klient,
          x.id_os,
          x.cnpj_cpf,
          x.name,
          x.lote,
          x.di_dde,
          x.nr_nf,
          x.date_bill,
          TO_CHAR(trunc(x.date_bill, 'month')),
          z.valor_cif,
          y.valor,
          y.taxa_dolar,
          x.id_artikel,
          x.conhec
 order by x.nr_nf, x.di_dde asc

O resultado de datas estão em dois sub-select.

(select min(io.time_in)
                   from in_out io, in_out_pos iop, we
                  where io.lager = iop.lager
                    and io.id_in_out = iop.id_in_out
                    and iop.lager = we.lager
                    and iop.typ_document = we.typ_doc
                    and iop.document = we.nr_doc
                    and io.art_in_out = 'LI'
                    and we.nr_lieferschein = x.lote) dt_entrada,
                
                (select max(io.time_out)
                   from in_out io, in_out_pos iop
                  where io.lager = iop.lager
                    and io.id_in_out = iop.id_in_out
                    and iop.typ_document in ('DI', 'DDE')
                    and iop.document = x.di_dde) dt_saida,

Como eu faria para calcular estes períodos? O faturamento utiliza em uma planilha esta fórmula (SAÍDA - ENTRADA) / -10. Por exemplo (10/12/2014 - 23/12/14) /-10 neste calculo ele me traz dois períodos.

Share this post


Link to post
Share on other sites

A subtração entre duas datas dá o resultado em dias (ou fração) , tente usar sua query como uma subquery

 

select dt_saida - dt-entrada

from

(

...sua query

)

 

mas eu revisaria esta query , com certeza ela pode ser melhorada.

Share this post


Link to post
Share on other sites

Olá,

Realmente o seu código precisa muito de um refactoring, mas talvez para revolver o seu problema de forma mais rápida, utilize uma view em cima desse select realizando o calculo das datas que você precisa, acho mais limpo do que criar um outro subselect dentro desse SQL.

Mas reescreva esse select, pois dar manutenção a ele me parece algo insano.

Share this post


Link to post
Share on other sites

×

Important Information

Ao usar o fórum, você concorda com nossos Terms of Use.