Query runtime

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
douglasmattos
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 75
Joined: Mon, 19 Mar 2012 2:28 pm

Good morning!

I need to take a question with you.
I have the query below that takes around 30 seconds to generate the result.

I would like to know if there is something I can do to improve performance, because when I use a Union with the same query the time increases a lot ..

Select all

 
SELECT TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
            ,COUNT(*)mês_ATUAL 
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
      AND ATENDIME.tp_atendimento in ('U','A') 
      AND TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY') = TO_CHAR(SYSDATE,'MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')
Top
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Douglasmattos,

Is everything okay?

I'm not a performance expert, but could give you some suggestions.

In your WHERE, it would be interesting to avoid converting DT_NING WITH TO_CHAR. Choose to use the format DD/MM/YYYY and verify that the date is in a given month with the help of the date functions ADD_MONTH, LAST_DAY and the Between comparator. For example:

Select all

ANTES:  AND TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY') = TO_CHAR(SYSDATE,'MM/YYYY') 
DEPOIS: AND ATENDIME.DT_ATENDIMENTO BEETWEEN LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1,LAST_DAY(SYSDATE)
may be possible to get better results in the WHERE above if this DT_NING column is indexed.

Now, you could also check the cardinality (qtde of different reports) of the CD_Multi_empresa and TP_-sensation columns. If for example the table has millions, but thousands of records with CD_MULTI_EMPRESA = 3 may be that there is (no guarantee) an improvement of performance. The same vouches for TP_-sensation column.

You could evaluate if it would be worth creating compounds formed by DT_NING, CD_MULTI_ COMPANY, TP_NING. Perhaps some combinations between these columns offers a more satisfying result for you. If you choose to create a compound index, it may be better to start with the DT_NING column.
Finishing, I do not think there are answers ready or unique to your case. You will need to take into account a series of factors, such as quantity of records in the table, index existence, if the table is partitioned or not. It is a matter of making attempts until you find (or not) a better result.

I believe that forists can give you other interesting suggestions.

Hugs and good luck!

Sergio Coutinho
Top
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

As was said, the ideal is that the "Attention.DT_NING" field has index. If you already have when you use a function on top of the field, as in the to_char case, the index benefit is lost.
There is a possibility to create an index with the specific function, in the case an index with the to_char.
But recommend that instead of using your filter:

Select all

 
SELECT TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY') mês_ANO 
... 
AND TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY') = TO_CHAR(SYSDATE,'MM/YYYY')
Use the following:

Select all

SELECT TO_CHAR(SYSDATE,'MM/YYYY') mês_ANO 
.... 
AND ATENDIME.DT_ATENDIMENTO BETWEEN TRUNC(SYSDATE,'MM') AND LAST_DAY(SYSDATE)
or even use the sysdate truncation to group.

It would be ideal to see Union as it may only use a SELECT.
Top
douglasmattos
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 75
Joined: Mon, 19 Mar 2012 2:28 pm

noctifero and stcoutinho thank you for the tips.

I will analyze my table and verify that the DT_NING field has index. From what I saw do not have.
. Tests with your tips and check if I have an improvement in the result of my query.

Thanks for the help, thank you very much. : D: D: D
Top
douglasmattos
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 75
Joined: Mon, 19 Mar 2012 2:28 pm

Personal,

I performed the query using the tips of (stcoutinho) and the result was very good. Before it takes around 16 seconds to generate, now it took only 1 second ....

When I used the Union my query takes an average of 3 minutes to generate, now it takes 5 seconds. .

Nothing like speaking with the masters ... rsrs: D: D: D: D

Select all

 
SELECT MAX(mês_ANO)mês_ANO 
       ,SUM(mês_ATUAL)mês_ATUAL 
       ,MAX(mês_ANT_1)mês_ANT_1 
       ,SUM(TOTAL_ANT_1)TOTAL_ANT_1 
       ,MAX(mês_ANT_2)mês_ANT_2 
       ,SUM(TOTAL_ANT_2)TOTAL_ANT_2 
       ,MAX(mês_ANT_3)mês_ANT_3 
       ,SUM(TOTAL_ANT_3)TOTAL_ANT_3 
       ,MAX(mês_ANT_4)mês_ANT_4 
       ,SUM(TOTAL_ANT_4)TOTAL_ANT_4 
       ,MAX(mês_ANT_5)mês_ANT_5 
       ,SUM(TOTAL_ANT_5)TOTAL_ANT_5 
       ,MAX(mês_ANT_6)mês_ANT_6 
       ,SUM(TOTAL_ANT_6)TOTAL_ANT_6 
       ,MAX(mês_ANT_7)mês_ANT_7 
       ,SUM(TOTAL_ANT_7)TOTAL_ANT_7 
       ,MAX(mês_ANT_8)mês_ANT_8 
       ,SUM(TOTAL_ANT_8)TOTAL_ANT_8 
       ,MAX(mês_ANT_9)mês_ANT_9 
       ,SUM(TOTAL_ANT_9)TOTAL_ANT_9 
       ,MAX(mês_ANT_10)mês_ANT_10 
       ,SUM(TOTAL_ANT_10)TOTAL_ANT_10 
       ,MAX(mês_ANT_11)mês_ANT_11 
       ,SUM(TOTAL_ANT_11)TOTAL_ANT_11 
       ,MAX(mês_ANT_12)mês_ANT_12 
       ,SUM(TOTAL_ANT_12)TOTAL_ANT_12 
 FROM( 
SELECT TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2 
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3               
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12                                                         
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13   
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(SYSDATE),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY') 
 
UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL        
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2 
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3               
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')   
 
UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL          
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3               
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-3))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-2)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')     
 
UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL           
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-4))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-3)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')     
 
UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL          
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-5))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-4)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')     
 
UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL            
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-6))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-5)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')     
 
UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL           
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-7))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-6)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')   
   
  UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL         
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-8))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-7)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')   
   
    UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL          
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-9))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-8)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')   
   
   UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL            
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-10))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-9)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')   
 
   UNION ALL 
   
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL           
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11 
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-11))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-10)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY') 
 
   UNION ALL 
 
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL           
       ,'' mês_ANT_12 
       ,0 TOTAL_ANT_12   
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-12))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-11)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')   
 
   UNION ALL 
 
SELECT '' mês_ANT_0 
       ,0 TOTAL_ANT_0 
       ,'' mês_ANT_1 
       ,0 TOTAL_ANT_1 
       ,'' mês_ANT_2 
       ,0 TOTAL_ANT_2               
       ,'' mês_ANT_3 
       ,0 TOTAL_ANT_3 
       ,'' mês_ANT_4 
       ,0 TOTAL_ANT_4 
       ,'' mês_ANT_5 
       ,0 TOTAL_ANT_5 
       ,'' mês_ANT_6 
       ,0 TOTAL_ANT_6 
       ,'' mês_ANT_7 
       ,0 TOTAL_ANT_7 
       ,'' mês_ANT_8 
       ,0 TOTAL_ANT_8 
       ,'' mês_ANT_9 
       ,0 TOTAL_ANT_9 
       ,'' mês_ANT_10 
       ,0 TOTAL_ANT_10 
       ,'' mês_ANT_11 
       ,0 TOTAL_ANT_11   
       ,TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')mês_ANO 
       ,COUNT(*)mês_ATUAL          
       ,'' mês_ANT_13 
       ,0 TOTAL_ANT_13         
  FROM DBAMV.ATENDIME  
 WHERE ATENDIME.cd_multi_empresa = 3 
   AND ATENDIME.tp_atendimento in ('U','A') 
   AND ATENDIME.DT_ATENDIMENTO BETWEEN TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-13))+1,'DD/MM/YYYY') AND TO_DATE(LAST_DAY(ADD_MONTHS(SYSDATE,-12)),'DD/MM/YYYY') 
  GROUP BY TO_CHAR(ATENDIME.DT_ATENDIMENTO,'MM/YYYY')    
        
  )
Top
Post Reply

Return to “SQL”

  • Information

  • Who is online

    Users browsing this forum: No registered users and 17 guests