0 votes

Query based on two parameters VISUAL FOXPRO

Good morning! I would like to know how I can make a query to get the results of my records depending on the date.

I have PEDPOS which is the order and I have CMOV which is the movement that was made (101 delivered 102 returned) I have the date on which each movement was made. CMOV

What I want to do is for example if an item that has:

PEDPOS    CMOV    FECHA
123       102     20-02-17
123       101     22-03-17

Here as you can see this order is already discarded because it was delivered, as, what I want to know is how I can do to get the ones that are not yet delivered as for example this one:

PEDPOS    CMOV    FECHA
124       102     15-03-17
124       101     23-03-17
124       102     04-04-17

The product has not yet been returned here as it was previously delivered but was returned again.

0voto

Patrick Arguello Points 707

Ale, you can try these consecutive select. The last cursor (cur4) gets the listing you need.

** obtener cursor el ultimo movimiento de cada pedido
select  pedpos as c1pedpos, max(fecha) as ultfch, count(*) as cantreg from pedido group by 1 into cursor cur1
brow

** obtener los pedido a excluir porque tuviero moviento 101 y 102 en la misma fecha
select * from pedido p1 inner join cur1 c1 ;
           on p1.pedpos = c1.c1pedpos and p1.fecha = c1.ultfch ;
        where cantreg > 2  and cmov = 101 into cursor curexcluir
brow 

**  obtener los posible pedidos que cumplen la condicion >2 movimientos                                                   
select * from pedido p1 inner join cur1 c1 ;
           on p1.pedpos = c1.c1pedpos and p1.fecha = c1.ultfch ;
        where cantreg > 2   into cursor cur3 
brow

** detalle de los que cumplen la condicion
select * from pedido ; 
        where pedpos in (select pedpos from cur3 where cantreg > 2 ) ;
          and pedpos not in ( select pedpos from curexcluir)   ;
        into cursor cur4
brow

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X