Query product mix oracle

Question

Query product mix oracle

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I made an indicator here in the company where you ready product information, quantity sold, average value, etc. This in a period of time.

Now I need the following information:

What is the product that most often appeared in the same sales note for each product in the list.

For example, whenever I sell an IP camera, I sell a source and 2 connectors. So in my search for the camera, the product that sells the most together is the connector.

I do not even know how to start this assessment. Is there an Oracle function that can help me?

sql oracle pl-sql

asked by anonymous 19.05.2016 / 18:31

2 answers

Cordova Geolocation How to value without pennies down?

score 2 · Answer 1

SIMPLIFYING

NOTA_FISCAL_ITEM
NUMERONOTA
CODCLIENTE
CODPRODUTO

CAMERAS

SELECT NUMERONOTA
FROM   NOTA_FISCAL_ITEM
WHERE  CODPRODUTO = 'CAMERA IP'

SOLD TOGETHER

SELECT *
FROM   NOTA_FISCAL_ITEM
WHERE  CODPRODUTO <> 'CAMERA IP'
AND    NUMERONOTA IN (SELECT NUMERONOTA
                      FROM   NOTA_FISCAL_ITEM
                      WHERE  CODPRODUTO = 'CAMERA IP')

MORE SOLD TOGETHER

SELECT CODPRODUTO , COUNT(*) QTD
FROM   NOTA_FISCAL_ITEM
WHERE  CODPRODUTO <> 'CAMERA IP'
AND    NUMERONOTA IN (SELECT NUMERONOTA
                      FROM   NOTA_FISCAL_ITEM
                      WHERE  CODPRODUTO = 'CAMERA IP')
GROUP BY CODPRODUTO
ORDER BY 2 DESC

A simplification however, no optimization and the real problem is a bit more complex.

score 0 · Answer 2

Your question is very generic, but I believe that with the query below you can adapt it for your use.

Basically, in my case, it lists the charges (cameras) and their invoices (source, connectors). Note the second select within the from.

Adapt it to your use:

--- lista as cargas criadas no periodo e suas notas fiscais vinculadas
select NAME        CARGA,
       STATUS_CODE STATUS,
       TRUNC(CREATION_DATE) DATA_CRIACAO,
       notas.NotaFiscal NOTASFISCAIS
 from wsh_trips ,
      (select distinct wt.name      viagem,
                       a.trx_number NotaFiscal
            from 
               ra_customer_trx_all           a,
               wsh_trips                     wt,
               WSH_NEW_DELIVERIES            WND,
               WSH_DELIVERY_LEGS             WDL,
               WSH_TRIP_STOPS                WTS
            where trunc(a.trx_date)     between '01-JUL-12' AND '31-AGO-12'
             AND WND.DELIVERY_ID             = TO_NUMBER(A.INTERFACE_HEADER_ATTRIBUTE3(+))
             AND WND.DELIVERY_ID             = WDL.DELIVERY_ID
             AND WDL.PICK_UP_STOP_ID         = WTS.STOP_ID
             AND WTS.TRIP_ID                 = WT.TRIP_ID
             and a.status_trx <> 'VD'
            order by 1,2) Notas

 where trunc(CREATION_DATE) between '01-JUL-12' AND '31-JUL-12'       
       and name = Notas.viagem(+)