How do I SELECT this

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 21 Sep 2006 10:48 am

I have two tables: A and b.

The table has the movement of the financial statement of each employee every month. Table B has the section history of each employee.

Table: PLATE, ANOCOMP, MESCOMP, DTPAGTO, VALUE.

Table B: CHAPA, DTMUDANCA, REASON, CODSECAO.

Let's say a fucnionário in January 2006 was in section 2 and in may he went to section 5. When crossing this data with your financial statement of 2006: a. CHAPA, a. ANOCOMP, a. MESCOMP, b. CODSECAO, a. VALUE, I need him to show me de Janeiro to April 2 and section from May to September the section 5.
fbarros300472
Location: sp

Poston Fri, 22 Sep 2006 12:51 pm

well in this case you would have to do a select internal to bring section and wouldn't put the table B in the from, see
Code: Select all
select A.CHAPA,
       A.ANOCOMP,
       A.MESCOMP,
       (SELECT MAX(NR_SECAO) FROM B
         WHERE A.CHAPA = B.CHAPA
           AND TO_DATE('01'||A.MESCOMP||A.ANOCOMP,'DDMMRRRR') < TRUNC(B.DTPAGTO)) CODSECAO,
       A.VALOR
  FROM A

detail in this case will bring the biggest section before the date code adulteration.
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Poston Fri, 22 Sep 2006 12:54 pm

PAHO ... sorry ... actually put the inverted signal ... the right is greater
Code: Select all
TO_DATE('01'||A.MESCOMP||A.ANOCOMP,'DDMMRRRR') > TRUNC(B.DTPAGTO)
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Poston Thu, 28 Sep 2006 3:13 pm

table
Code: Select all
CHAPA      ANOC ME DTPAGTO              VALOR     
---------- ---- -- -------------------- ----------
        12 2006 01 28/09/2006                  100
        12 2006 02 28/09/2006                  100
        12 2006 03 28/09/2006                  100
        12 2006 04 28/09/2006                  100
        12 2006 05 28/09/2006                  100
        12 2006 06 28/09/2006                  100
        12 2006 07 28/09/2006                  100
        12 2006 08 28/09/2006                  100
        12 2006 09 28/09/2006                  100
        12 2006 10 28/09/2006                  100
        12 2006 11 28/09/2006                  100
        12 2006 12 28/09/2006                  100
table B
Code: Select all
CHAPA      DTMUDANCA            MOTIVO        CODSECAO 
---------- -------------------- ------------- ----------
        12 01/05/2006           TESTE            5
        12 01/08/2006           TESTE            8
        12 01/12/2005           TESTE            2
doing the select
Code: Select all
SQLWKS> select A.CHAPA,
     2>        A.ANOCOMP,
     3>        A.MESCOMP,
     4>        (SELECT MAX(CODSECAO) FROM B
     5>          WHERE A.CHAPA = B.CHAPA
     6>            AND TO_DATE('01'||A.MESCOMP||A.ANOCOMP,'DDMMRRRR') >= TRUNC(B.DTMUDANCA)) CODSECAO,
     7>        A.VALOR
     8>   FROM A
     9>
CHAPA      ANOC ME CODSECAO   VALOR     
---------- ---- -- ---------- ----------
        12 2006 01          2        100
        12 2006 02          2        100
        12 2006 03          2        100
        12 2006 04          2        100
        12 2006 05          5        100
        12 2006 06          5        100
        12 2006 07          5        100
        12 2006 08          8        100
        12 2006 09          8        100
        12 2006 10          8        100
        12 2006 11          8        100
        12 2006 12          8        100
12 linhas selecionadas.
I think this is the way you need right?
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Poston Fri, 29 Sep 2006 1:15 pm

Hi Thiago, beleza?

Just one little detail so that the sql function. When the change happens section in numerical order, it works fine. If you toggle the numerical order of the section: 12/1/2005, 2; 5/1/2006, 8; 9/1/2006, 3; in time to display the data, the section 3 will not be displayed. Section 2 until 07/2006 and section 8 from 05/2006 onwards. Thanks for the attention.
fbarros300472
Location: sp



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests