calcular períodos no select.

[asacap1000 0]

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.

[Motta 645]

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.

[rodrigoords 1]

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.

[Motta 645]

Tem horas que é melhor fazer do zero.