Consulta com sub-consultas e agregações

[rtfacincani 0]

Prezados, necessito gerar uma consulta ao banco de dados Oracle e estou enfrentando dificuldades em retornar valores. Abaixo vou explicar como as tabelas estão montadas

 

Tab1:NotaFiscal

codnota,datanota,codveiculo,datasaida,datachegada

 

Tab2:Combustivel

codveiculo,dataabastecimento,kmpercorrido,qtdlitros,medialistros

 

O que preciso? Preciso saber dentro do período da NotaFiscal, qual o veículo que entre a datasaida e datachegada, foram abastecidos, e qual o total de kilometros percorridos entre as datas de saida e chegada da notafiscal.

 

A saída ficaria assim:

 

codnota,datanota,codveiculo,totalkmpercorrido,totalqtdlitros,medialitros.

 

Tentei algo assim, mas não deu certo estou tendo esta dificuldade:

 

select n.codnota,n.datanota,n.codveiculo,sum(c.totalkmpercorrido) as totalkmpercorrido,sum(c.totalqtdlitros) as totalqtdlitros,avg(c.medialitros) as medialitros from notafiscal n inner join combustivel c on n.codveiculo = c.codveiculo

where c.dataabastecimento between n.datasaida and n.datachegada

group by codnota,datanota,codveiculo

 

Porém não estou conseguindo retornar os valores, principalmente por não ter em combustível o motivo para o abastecimento, visto que pode ser que seja uma viagem e entre a datasaida e datachegada pode existir vários abastecimentos e a única coisa que tenho para ligar é o código do veículo e as datas de saída e chegada emitida pela nota.

 

Será que alguém poderia me auxiliar com essa consulta?

 

 

 

[Motta 645]

O que há de errado com o seu sql !?

 

 

Parece-me um problema de modelo , a NF se refere a compra do combustivel ou a ao frete ?

 

O kmpercorridido se refere ao abastecimento em questao ou ao anterior ?

 

O sql parece 'certo' , o modelo nem tanto.

[rtfacincani 0]

O que há de errado com o seu sql !?

 

 

Parece-me um problema de modelo , a NF se refere a compra do combustivel ou a ao frete ?

 

O kmpercorridido se refere ao abastecimento em questao ou ao anterior ?

 

O sql parece 'certo' , o modelo nem tanto.

Motta, obrigado por responder, mas a consulta em questão é referente a um modelo onde é emitida uma nota fiscal de compra ou serviço, e o abastecimento é referente ao abastecimento de caminhões que farão o serviço.

 

Quanto ao km percorrido se refere ao abastecimento em uma data específica que pode ou não estar entre a data de saida e chegada, pois o caminhão faz diversos abastecimentos e pode não ter a ver com a nota em questão, por isso que estou atrelando ao período da saida e chegada da nota, qualquer abastecimento entre estas datas, subentende-se que pode estar atrelado a nota em questão, porém não é garantido.

 

uma coisa que acontece é que ao incluir o número da nota, na pesquisa, ele acaba fazendo um plano cartesiano e colocando todas as notas mesmo não estando envolvidos.

 

Não sei se errei ao montar a estrutura, mas faço uma busca em uma terceira tabela que é uma tabela de ligação, onde contém o numero da nota, o código do veículo e a ordem de serviço, então faço a ligação apenas do código do veículo que é a única coisa que ambas as tabelas tem em comum, visto que o número da nota não está envolvida.

 

Esta tabela de ligação é composta pelos seguintes campos:

 

tabela 3:

 

codordemserviço, codveiculo, codnota

 

tabela 4

codveiculo, placa,modelo, chassi

 

levando em consideração que a tabela de veículo tem a placa do veículo e o seu código e a tabela de abastecimento tem a placa do veículo, mas não o código do veículo, faço uso desta terceira tabela para descobrir o código do veículo para montar a estrutura.

 

Não passei essa informação pois achei que o problema não estaria ai, e sim no group by ou sum.

 

Mas a estrutura real é assim. são quatro tabelas onde eu tenho a tabela da nota fiscal, a tabela de abastecimento com a placa do veículo, a tabela de veículo que tem o código do veiculo e a sua placa e a tabela de ligação que tem o numero da nota, o código do veículo e o numero da ordem de atendimento, porém em abastecimento só tenho a placa do veículo.

 

A questão da kilometragem e a quantidade de litros, toda a vez que o caminhão faz o abastecimento é planilhado a informação e posteriormente lançado no sistema. Mas tenho que descobrir quanto que uma nota "paga" para abastecer o caminhão para realizar o seu percurso, por isso é que tenho que fazer essa ligação, mas infelizmente não tem como pegar na planilha do lançamento de abastecimento qual foi a nota que aquele caminhão naquela data está atendendo.

 

Peço a sua ajuda a fim de tentar me ajudar nesta descoberta.

 

Atenciosamente,

 

Ricardo

[Motta 645]

Capture mais um dado ,

 

Codnota

Kminicialnf

Kmfinalnf

 

Com isto ao menos se tem o quanto um veiculo rodou por nf

 

 

Uma pergunta , mais de um veiculo pode atender uma NF ?

[rtfacincani 0]

Capture mais um dado ,

 

Codnota

Kminicialnf

Kmfinalnf

 

Com isto ao menos se tem o quanto um veiculo rodou por nf

 

 

Uma pergunta , mais de um veiculo pode atender uma NF ?

Sim, uma nota pode ter vários veículos atendendo. Por isso que uma nota tem ordem de serviço que é a ordem para que um caminhão vá pegar a carga em outro lugar. As informações de KMInicial, e KMFinal, já são calculados e lançados no sistema pelo KM Percorrido no abastecimento, por isso não preciso informar

[Motta 645]

Tá mas você não tem em combustivel

 

Imagino algo do tipo , dia 1 o caminhão é abastecido,

 

codveiculo dataabastecimento kmpercorrido qtdlitros medialistros

1 1/x/16 100 80 xx..xx

mesmo sem OS

 

no dia 2 ele faz a OS 1,2,e 3

ainda com combustivel ele faz a OS 4 , mas faz um reabastecimento neste dia

seria algo do tipo

OS kminicial kmfinal

1 100 102

2 102 110

3 110 117

...

 

Na minha opinião seria mais correto ter os km inicial e final de cada OS , além das do abastecimento

com isto seria possível saber que o veiculo x rodou y kms para a OS z

 

Calculando o custo médio deste veiculo (preço médio * litros / km rodados) seria

possivel calcular o custo do combustível na OS

 

Senão não se tem a informação do "custo de combustivel da OS" , pois numa OS o caminhao pode ir no bairro

na outra no outro lado da cidade

 

Espero ter ajudado