Function that returns an initial date and a final.

[Tony Joni Duran]

Good afternoon friends developers. This is my first post and I would like a little friends.
I need to elaborate a function that returns from several dates records the initial date and the end of those records that we are going to say concatenated with each other.
Ex:

Select all

1  - 01/01/2016 00:00:00	17/03/2016 00:00:00	40 
2 - 18/03/2016 00:00:00	15/06/2016 00:00:00	40 
3  16/06/2016 00:00:00	15/07/2016 00:00:00	40 
4 - 16/07/2016 00:00:00	31/12/2016 00:00:00	30

notice that the registration of the first initial date 01/01/2016 and ends at the date 15/07/2016 because they are sequential (concatenda) ends on date 15/07/2016 Logically that I should create a function for each search one for the initial date and another for the final date.
I hope I have been clear.
Hugs expensive colleagues.

[spernega]

Good afternoon Tony,

How do you post the structure of your table?

There are the MIN and MAX functions, we need to see if you can use to answer.

[Tony Joni Duran]

Hello .. Good afternoon spernega .. I already tried this computer, but it returns more than one line by having different codes in the call of the SELECT.
Follow you to check.

Select all

SELECT 
       T.NUMFUNC, T.NUMVINC,t.dtini, t.dtfim, t.codigo 
         FROM 
         ( 
         SELECT L.NUMFUNC, L.NUMVINC, 
         ( 
                  CASE WHEN L.DTINI <=to_date('01/01/2016','dd/mm/yyyy') 
                       THEN to_date('01/01/2016','dd/mm/yyyy') 
                 ELSE L.DTINI 
                  END 
         ) DTINI, 
 
         ( 
                  CASE WHEN (L.DTFIM >=LAST_DAY(to_date('01/12/2016','dd/mm/yyyy')) OR L.DTFIM IS NULL) 
                       THEN to_date('31/12/2016','dd/mm/yyyy') 
                 ELSE L.DTFIM 
                  END 
         ) DTFIM, I.ITEM CODIGO 
          FROM ITEMTABELA I, LIC_AFAST L, CODIGOS_FREQ_ F, VINCULOS V 
           WHERE L.NUMFUNC= 381454--WNUMFUNC 
           AND L.NUMVINC = 1--WNUMVINC 
           AND L.CODFREQ = F.CODIGO 
           AND L.NUMFUNC = V.NUMFUNC 
           AND L.NUMVINC = V.NUMERO 
           AND ( 
        (TO_CHAR(L.DTINI,'YYYY')='2016' OR TO_CHAR(L.DTFIM,'YYYY')='2016') 
   or    (L.DTFIM IS NULL  AND TO_CHAR(L.DTINI,'YYYY')<='2016') 
   OR  (TO_CHAR(L.DTINI,'YYYY') <='2016' AND TO_CHAR(L.DTFIM,'YYYY') >='2016') 
 
      ) 
 
           AND V.EMP_CODIGO = 1--WEMPRESA 
           AND I.ITEM = F.FLEX_CAMPO_03 
           --AND I.ITEM = PCODIGO 
           AND NVL(V.FORMAVAC,' ') <> 'EXCLUSAO' 
           AND I.TAB = 'RAIS_AFASTAMENTO_MTE' 
           AND V.DTEXERC = '02/05/2007'--PDTEXERC 
           AND I.ITEM > 0 
      ) T 
      GROUP BY T.NUMFUNC, T.NUMVINC,t.dtini, t.dtfim, t.codigo 
 
      order by 3

This SQL brings that relation of dates that I posted earlier.
Hugs.

[spernega]

Good morning Tony,

tries to do this, making a group by the code

Select all

SELECT x.codigo, 
       min(x.dtini) dtini,  
       max(x.dtfim) dtfim,  
from   (        
SELECT T.NUMFUNC,  
       T.NUMVINC, 
       t.dtini,  
       t.dtfim,  
       t.codigo 
FROM   (SELECT L.NUMFUNC, L.NUMVINC, 
                -- 
               (CASE WHEN L.DTINI <=to_date('01/01/2016','dd/mm/yyyy') THEN  
                     to_date('01/01/2016','dd/mm/yyyy') 
                ELSE  
                     L.DTINI 
                END) DTINI, 
                -- 
                ( CASE WHEN (L.DTFIM >=LAST_DAY(to_date('01/12/2016','dd/mm/yyyy')) OR L.DTFIM IS NULL) THEN  
                       to_date('31/12/2016','dd/mm/yyyy') 
                  ELSE  
                       L.DTFIM 
                  END) DTFIM, I.ITEM CODIGO 
                -- 
        FROM   ITEMTABELA I, LIC_AFAST L, CODIGOS_FREQ_ F, VINCULOS V 
        WHERE  L.NUMFUNC= 381454 --WNUMFUNC 
        AND    L.NUMVINC = 1 --WNUMVINC 
        AND    L.CODFREQ = F.CODIGO 
        AND    L.NUMFUNC = V.NUMFUNC 
        AND    L.NUMVINC = V.NUMERO 
        AND    ((TO_CHAR(L.DTINI,'YYYY')='2016' OR TO_CHAR(L.DTFIM,'YYYY')='2016') or  
                (L.DTFIM IS NULL AND TO_CHAR(L.DTINI,'YYYY')<='2016') OR  
                (TO_CHAR(L.DTINI,'YYYY') <='2016' AND TO_CHAR(L.DTFIM,'YYYY') >='2016')) 
        AND    V.EMP_CODIGO = 1  --WEMPRESA 
        AND    I.ITEM = F.FLEX_CAMPO_03 
        AND    NVL(V.FORMAVAC,' ') <> 'EXCLUSAO' 
        AND    I.TAB = 'RAIS_AFASTAMENTO_MTE' 
        AND    V.DTEXERC = '02/05/2007'--PDTEXERC 
        AND    I.ITEM > 0) T 
GROUP BY T.NUMFUNC, T.NUMVINC,t.dtini, t.dtfim, t.codigo) x 
group by x.codigo 
order by 1

In this test I'm neglecting numfunc and numvinc, just to see if there is a more or less correct result ...

[Tony Joni Duran]

And aí friend..sua sql fell like a glove, but ended up infringing in other rules..rsrsrs
in fact when I have a period concatenated blza, your code worked, but it has the cases in which the periods are separated by gaps (periods) and your SQL ends up bringing Dataini and end date of all this period.
But quqlquer form already helped me for damn.
If you have more ideas aí..acidate. Big hug brother!

[spernega]

Good morning Tony,

Good, halfway walked !!

explains me better as this concatenation is, this part I did not quite understand ...