Oracle SQL - Return of the first record between two dates

Asked

Viewed 120 times

2

I have this query:

SELECT codigo_usuario, dataproducao 
FROM tbproducao
WHERE
dataproducao >= to_date('01/02/2013', 'DD/MM/YYYY')
and dataproducao <= to_date('30/03/2013', 'DD/MM/YYYY') and

Is the result comes as expected, returned all records that are between the dates reported, example:

codigo_usuario | dataproducao 
 -------------------
   5      01/02/2013
 -------------------
   8      03/02/2013

The code "5" returned in the query above has another record with the production date = 05/10/2012, which was its first record in the table.

And now comes the problem, I can not make the above query does not return code 5, after I want the query return between the dates informed the records that have their first record in that range. Any idea?

sql oracle

  • Sorack, I edited the question by placing the real name of the table. Example data is already in the example. Make it easy?

  • Sorack, exactly.

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

2 answers

1


Use the clause EXISTS together with NOT:

SELECT p.codigo_usuario,
      p.data_producao
  FROM tbproducao p
WHERE p.data_producao BETWEEN TO_DATE('01/02/2013', 'DD/MM/YYYY') AND TO_DATE('30/03/2013', 'DD/MM/YYYY')
  AND NOT EXISTS(SELECT 1
                    FROM tbproducao p2
                  WHERE p2.codigo_usuario = p.codigo_usuario
                    AND p2.data_producao < TO_DATE('01/02/2013', 'DD/MM/YYYY'))

EXISTS Condition

An EXISTS condition tests for Existence of Rows in a subquery.

In free translation:

A condition EXISTS tests the existence of lines in a subquery.

  • It worked, thank you very much

  • @Paulogonçalves Glad you liked the answers! But the best way to thank those who helped you is to mark "accept" the best answer and vote for all who helped you. So you make sure that whoever wrote the answer gets something in return, in addition to making the site cleaner and more useful for everyone.

0

Try it this way:

SELECT      T.*
FROM        tabela  T
LEFT JOIN   (
                SELECT  codigo
                    ,   dataproducao
                    ,   ROW_NUMBER() OVER (PARTITION BY codigo ORDER BY dataproducao) AS rn
                FROM    tabela
            )       T2 ON T2.codigo = T.codigo AND T2.rn = 1
WHERE       T.dataproducao  BETWEEN TO_DATE('2013-02-01', 'yyyy-mm-dd') 
        AND TO_DATE('2013-03-30', 'yyyy-mm-dd')
        AND T2.dataproducao BETWEEN TO_DATE('2013-02-01', 'yyyy-mm-dd') 
        AND TO_DATE('2013-03-30', 'yyyy-mm-dd')

The idea was to place a query that obtains the line number in each of the code groups and then, in the LEFT JOIN, catch only those of número = 1.

Then compare the dates in the records of the main query + that of the sub-consumption with only the dates of the first time record.

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.