How to make calculations, with conditions

[priajf]

Good afternoon guys
I have a code here that returns me two lines. Equal partly
One is outflow of stock and another return.
I asked for 30 products. And I returned 15. In my I need only the total of those who left.
ie only 15. As I could do this. I have no idea how
I await replies

[Jimmy Biff]

Let me see if I understand
You asked for 30 products and returned 15. You want the total of that no returned, that in the case is 15?

[priajf]

Exactly this
an example of my code

Select all

 
 (select me.nr_solicitacao, me.dt_movto, me.cd_estado, me.tp_movto, me.qt_movto 
 from movto_est_medicamento me 
 where me.cd_estado 
 and me.dt_movto between '14/07/2011' and '14/07/2011')

[Jimmy Biff]

Some of these fields serves as a parameter to know what came out and what returned? Ex. What returned records with a 'R'.
In this select, the range '14 / 07/2011 '- '14 / 07/2011' is the products that left?

[priajf]

No. He brings everything.
All that left. But it makes a copy of what was returned também. When I return it does not update and yes it creates another line.
This date should bring all producer who were provided.
That is,
returned 15, should return 15.
They left 30
I do not know if he could understand

[Jimmy Biff]

So let me see if this example fits
in the range '14/07 / 2011 '- '14 / 07/2011' You asked to list all the products that left, right? In total, 15 products left. In Select, you should return 15. However, when you register the return, it writes another line. So, instead of returning 15, returns 30. Is that pri?

[priajf]

Exactly this
returns the 30 that left
and also the 15 they returned. But I want it to leave the total actually out. Even with the return.

[priajf]

Someone can help me there

I was able to do

Select all

sum(decode(m.tp_movto,'S',m.qt_servico)) - sum(decode(m.tp_movto,'R',m.qt_servico))

does what I want. But you do not return anything when you only have S.
Someone can give me a light.; ....

[marcus.kobel]

Hehehehe
What a sculptive ....
Priscilla could barely explain your problem.
The main question here is: How do you know that a product returned? Is there a field that tells you this? What table is that you can consulting?

[marcus.kobel]

When I make return it does not update and yes it creates another line

So, you should watch the records in a traffic history table, right?
Each time you enter or leave products, you register what was bustling, right?

Type Thus:

Select all

 
cod_prod  nome_prod      qt_movimentada  dt_movimentada  tp_movimento 
     123  produto ABC               30       14/07/2011  Saida 
     123  produto ABC               15       14/07/2001  Retorno 

Is that there?
From then on, you need to read the records and deduce that you have 15 products that have not returned, right?

[priajf]

That's right

[marcus.kobel]

So, follow the way you're going that's right.
uses Sum (Decode (....)) as you are using, and then boot a group by in the main fields: cod_product, name_pring, dt_mement, etc.

] This should work. Otherwise, it warns.

[priajf]

Dae Marcus
Looks OS
It did not work

Select all

 
select  sum(decode(m.tp_movto,'S',m.qt_servico,0)) - sum(decode(m.tp_movto,'R',m.qt_servico,0))testesa, m.dt_movto, m.nr_solicitacao, pj.nm_entidade,  
       s.ds_servico, m.qt_servico, m.dt_validade,  
       m.cd_lote, pfabr.nm_fantasia, m.fg_ddu 
from solicitacao_medicamento sm,  
     movto_est_medicamento m, 
     item_solicitacao_medicamento i, 
     pessoa_juridica pj, 
     servicos s, 
     pessoa_juridica pfabr 
where  m.dt_movto between '14/07/2011' and '14/07/2011' 
and sm.nr_solicitacao = m.nr_solicitacao 
and sm.nr_solicitacao = i.nr_solicitacao 
and i.cd_servico = m.cd_servico 
and sm.cd_entidade_solicitacao = pj.id_pessoajuridica 
and i.cd_servico = s.cd_servico 
and m.cd_fabricante = pfabr.id_pessoajuridica(+) 
group by m.cd_lote,m.dt_movto, m.nr_solicitacao, pj.nm_entidade,  
       s.ds_servico, m.qt_servico, m.dt_validade,  
        pfabr.nm_fantasia, m.fg_ddu

He still returns the two normal lines. The result of Sum da. Exit 20 and then return -15. Where the result should be 5

[gfkauer]

Post the two lines of the result for evaluation ...

[priajf]

Select all

 
 
teste   data          Nome                     Servico                                 Tipo 
 20	14/07/2011  3 HOSPITAL	LIOFILIZADO FATOR VIII - 250 UI  Saída 
-15	14/07/2011  3 HOSPITAL	LIOFILIZADO FATOR VIII - 250 UI  Retorno 

[priajf]

Another detail if I get the items from the SELECT and leave only the sum and another CD_LOTE. He returns a line as I want. But I need to leave these items. There is another way.

[gfkauer]

The problem is as follows, you have a cluster by the service, considering that there are 2 different services, a output and a return, can not group this way.

The solution is to rewrite the entire consultation. I'll see if I set an example for you.

[rogenaro]

Some information is missing.
- How to identify which request refers to the return? Is the 'r' movement always generated with the same output request number?
- Can there be more than one return to the same exit?
- The date of the return movement can be different from the output date? If so, should you consider the date of the return movement?
For example:
Day 01 An outlet of 10 units is generated.
Day 02 The return of 02 units is generated.
Day 03 The return of 08 units is generated.
Generating the report on 01 to day 02, should return 08 units? Or should you consider the movement of the 03 too, returning 0?

Considering that the request number is the way to group the output and return records, and which should be considered only the limited movements at the interval of the dates informed as a parameter:

Select all

 
select * 
from 
( 
  select sum( decode( tp_movto, 'S', 1, 'R', -1, 0 ) * qt_servico ) over ( partition by nr_solicitacao ) qt_movimentada 
       , dt_movto 
       , nr_solicitacao 
       , nm_entidade 
       , tp_movto 
  from 
  ( 
    -- Apenas para simular alguns registros 
    select 20 qt_servico 
         , to_date('14/07/2011', 'dd/mm/yyyy') dt_movto 
         , 3 nr_solicitacao 
         , 'HOSPITAL   LIOFILIZADO FATOR VIII - 250 UI' nm_entidade 
         , 'S' tp_movto 
    from   dual 
    union all 
    select 5 qt_servico 
         , to_date('14/07/2011', 'dd/mm/yyyy') dt_movto 
         , 3 nr_solicitacao 
         , 'HOSPITAL   LIOFILIZADO FATOR VIII - 250 UI' nm_entidade 
         , 'R' tp_movto 
    from   dual 
    union all 
    select 2 qt_servico 
         , to_date('17/07/2011', 'dd/mm/yyyy') dt_movto 
         , 3 nr_solicitacao 
         , 'HOSPITAL   LIOFILIZADO FATOR VIII - 250 UI' nm_entidade 
         , 'R' tp_movto 
    from   dual 
    union all 
    select 10 qt_servico 
         , to_date('15/07/2011', 'dd/mm/yyyy') dt_movto 
         , 4 nr_solicitacao 
         , 'TESTE' nm_entidade 
         , 'S' tp_movto 
    from   dual 
    -- 
  ) a 
  where dt_movto between to_date('14/07/2011', 'dd/mm/yyyy') and to_date('19/07/2011', 'dd/mm/yyyy') 
) a 
where a.tp_movto = 'S' 
and   a.qt_movimentada >= 0 -- Se houver retorno de todos os itens movimentados, exibir a linha? 
                            -- E se a quantidade retornada for maior do que a quantidade registrada na saída? 

[priajf]

I was able to do here
putting subselect that makes the sum
but, I can have left on the same date, and return também
now I do not know how I put it on the group by

[priajf]

It was certain staff
I put a subselect and put the group by. The number of the request, and the batch code.

Now I'm ok
Thanks for the attention