Useful Days x Sales

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
jdfuhr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 27 Jul 2010 2:59 pm
Location: Florianópolis - SC

Good afternoon guys,

I have a problem:

I am riding a select that brings me the total sales of a certain business day.

Well,
I created a view that brings me all the business days of the month by discounting the holidays registered in Tsifer. (I needed to do for the year, but for now the month serves me)


This table I use as helper in case there is no sale in the day And also to generate the table of the integer, so that it can follow according to the daily selling target of the seller as much as it lacks it to reach the daily goal. The totalizers in the software I use are mounted on the report view.

Also to make it easier I created another view that rides presents the Meta's X Meta sales for all sellers.

Select all

 
CREATE OR REPLACE VIEW VENDA_DIARIA AS 
SELECT DISTINCT 
    VEN.CODVEND, VEN.APELIDO, FDU.DATA AS DTNEG 
    /*CAB.DTNEG*/,  
    CASE WHEN (TRUNC(FDU.DATA) = TRUNC(CAB.DTNEG)) THEN SUM(CAB.VLRNOTA) ELSE 0 END AS VENDAS, 
    MET.DTREF, 
    (MET.PREVREC / (SELECT COUNT(DATA) FROM FOCA_DIAS_UTEIS WHERE TO_CHAR(DATA, 'MM') = TO_CHAR(MET.DTREF, 'MM'))) METADIARIA 
 
FROM TGFCAB CAB 
     LEFT JOIN TGFITE ITE ON ITE.SEQUENCIA = 1 AND ITE.NUNOTA = CAB.NUNOTA 
     LEFT JOIN TGFVEN VEN ON VEN.CODVEND = ITE.CODVEND 
     LEFT JOIN TGMMET MET ON MET.CODVEND = VEN.CODVEND 
     LEFT JOIN DIAS_UTEIS FDU ON FDU.DATA <> TO_DATE('01/01/2001') 
 
WHERE CAB.TIPMOV = 'P' AND CAB.SERIENOTA = 'P' 
     AND CAB.DTNEG BETWEEN MET.DTREF AND LAST_DAY(MET.DTREF) 
 
GROUP BY VEN.CODVEND, VEN.APELIDO, FDU.DATA, 
     CAB.DTNEG, MET.DTREF, MET.PREVREC 
> 
> 
> 
SQL> SELECT * FROM VENDA_DIARIA WHERE CODVEND = 3; 
  
CODVEND APELIDO         DTNEG           VENDAS DTREF       METADIARIA 
------- --------------- ----------- ---------- ----------- ---------- 
      3 ALTAIR          01/11/2010           0 01/11/2010        3150 
      3 ALTAIR          01/11/2010     4025,52 01/11/2010        3150 
 
      3 ALTAIR          03/11/2010           0 01/11/2010        3150 
      3 ALTAIR          03/11/2010      3804,1 01/11/2010        3150 
 
      3 ALTAIR          04/11/2010           0 01/11/2010        3150 
      3 ALTAIR          04/11/2010     2909,33 01/11/2010        3150 
 
      3 ALTAIR          05/11/2010           0 01/11/2010        3150 
      3 ALTAIR          05/11/2010     5052,74 01/11/2010        3150 
 
      3 ALTAIR          08/11/2010           0 01/11/2010        3150 
      3 ALTAIR          09/11/2010           0 01/11/2010        3150 
      3 ALTAIR          10/11/2010           0 01/11/2010        3150 
      3 ALTAIR          11/11/2010           0 01/11/2010        3150 
      3 ALTAIR          12/11/2010           0 01/11/2010        3150 
      3 ALTAIR          16/11/2010           0 01/11/2010        3150 
      3 ALTAIR          17/11/2010           0 01/11/2010        3150 
      3 ALTAIR          18/11/2010           0 01/11/2010        3150 
      3 ALTAIR          19/11/2010           0 01/11/2010        3150 
      3 ALTAIR          22/11/2010           0 01/11/2010        3150 
      3 ALTAIR          23/11/2010           0 01/11/2010        3150 
      3 ALTAIR          24/11/2010           0 01/11/2010        3150 
      3 ALTAIR          25/11/2010           0 01/11/2010        3150 
      3 ALTAIR          26/11/2010           0 01/11/2010        3150 
      3 ALTAIR          29/11/2010           0 01/11/2010        3150 
      3 ALTAIR          30/11/2010           0 01/11/2010        3150 
  
24 rows selected
My problem. For the days I'm selling it brings me a line with zero sale too (I separated in blocks of 2 lines at the beginning to facilitate understanding).

Can anyone help me?

: -O

Valeu
Top
diegopedrao
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 164
Joined: Fri, 22 Aug 2008 12:28 pm
Location: SP

Select all

 
CREATE OR REPLACE VIEW VENDA_DIARIA AS  
SELECT DISTINCT  
    VEN.CODVEND, VEN.APELIDO, FDU.DATA AS DTNEG  
    /*CAB.DTNEG*/,  
    SUM(CASE WHEN (TRUNC(FDU.DATA) = TRUNC(CAB.DTNEG)) THEN SUM(CAB.VLRNOTA) ELSE 0 END) AS VENDAS,  
    MET.DTREF,  
    (MET.PREVREC / (SELECT COUNT(DATA) FROM FOCA_DIAS_UTEIS WHERE TO_CHAR(DATA, 'MM') = TO_CHAR(MET.DTREF, 'MM'))) METADIARIA  
 
FROM TGFCAB CAB  
     LEFT JOIN TGFITE ITE ON ITE.SEQUENCIA = 1 AND ITE.NUNOTA = CAB.NUNOTA  
     LEFT JOIN TGFVEN VEN ON VEN.CODVEND = ITE.CODVEND  
     LEFT JOIN TGMMET MET ON MET.CODVEND = VEN.CODVEND  
     LEFT JOIN DIAS_UTEIS FDU ON FDU.DATA <> TO_DATE('01/01/2001')  
 
WHERE CAB.TIPMOV = 'P' AND CAB.SERIENOTA = 'P'  
     AND CAB.DTNEG BETWEEN MET.DTREF AND LAST_DAY(MET.DTREF)  
 
GROUP BY VEN.CODVEND, VEN.APELIDO, FDU.DATA,  
     CAB.DTNEG, MET.DTREF, MET.PREVREC
I put a sum in the case when ... will solve ...

ABS,

Diego Monteiro
Top
jdfuhr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 27 Jul 2010 2:59 pm
Location: Florianópolis - SC

Impressive the speed with which you can get answers in the forum. Very good indeed.

Dear Diegopedrao, when putting the sum in the case when he introduces me to the following error:

Select all

ORA-00937: NOT A SINGLE-GROUP GROUP FUNCTION
First column out of sum.
Top
diegopedrao
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 164
Joined: Fri, 22 Aug 2008 12:28 pm
Location: SP

Now it goes ... rs

Select all

 
 
CREATE OR REPLACE VIEW VENDA_DIARIA AS  
SELECT TABELA.CODVEND, 
           TABELA.APELIDO, 
           TABELA.DTNEG, 
          SUM(TABELA.VENDAS) AS VENDAS, 
          TABELA.DTREF, 
          TABELA.METADIARIA 
FROM ( 
SELECT DISTINCT  
    VEN.CODVEND, VEN.APELIDO, FDU.DATA AS DTNEG  
    /*CAB.DTNEG*/,  
    CASE WHEN (TRUNC(FDU.DATA) = TRUNC(CAB.DTNEG)) THEN SUM(CAB.VLRNOTA) ELSE 0 END AS VENDAS,  
    MET.DTREF,  
    (MET.PREVREC / (SELECT COUNT(DATA) FROM FOCA_DIAS_UTEIS WHERE TO_CHAR(DATA, 'MM') = TO_CHAR(MET.DTREF, 'MM'))) METADIARIA  
 
FROM TGFCAB CAB  
     LEFT JOIN TGFITE ITE ON ITE.SEQUENCIA = 1 AND ITE.NUNOTA = CAB.NUNOTA  
     LEFT JOIN TGFVEN VEN ON VEN.CODVEND = ITE.CODVEND  
     LEFT JOIN TGMMET MET ON MET.CODVEND = VEN.CODVEND  
     LEFT JOIN DIAS_UTEIS FDU ON FDU.DATA <> TO_DATE('01/01/2001')  
 
WHERE CAB.TIPMOV = 'P' AND CAB.SERIENOTA = 'P'  
     AND CAB.DTNEG BETWEEN MET.DTREF AND LAST_DAY(MET.DTREF)  
 
GROUP BY VEN.CODVEND, VEN.APELIDO, FDU.DATA,  
     CAB.DTNEG, MET.DTREF, MET.PREVREC ) TABELA 
 
GROUP BY TABELA.CODVEND, 
               TABELA.APELIDO, 
               TABELA.DTNEG, 
               TABELA.DTREF, 
              TABELA.METADIARIA
Att,

Diego Monteiro
Top
jdfuhr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 27 Jul 2010 2:59 pm
Location: Florianópolis - SC

I decided to create a new view to make the sum.

Select all

 
CREATE OR REPLACE VIEW VENDA_DIARIA1 AS 
SELECT  
     FVD.CODVEND, FVD.APELIDO, FVD.DTNEG,  
     SUM(FVD.VENDAS) as VENDAS, FVD.DTREF, FVD.METADIARIA  
      
FROM VENDA_DIARIA FVD 
 
GROUP BY FVD.CODVEND, FVD.APELIDO, FVD.DTNEG,  
      FVD.DTREF, FVD.METADIARIA 
> 
> 
SQL> SELECT * FROM FOCA_VENDA_DIARIA WHERE CODVEND = 3; 
  
CODVEND APELIDO         DTNEG           VENDAS DTREF       METADIARIA 
------- --------------- ----------- ---------- ----------- ---------- 
      3 ALTAIR          01/11/2010     4025,52 01/11/2010        3150 
      3 ALTAIR          03/11/2010      3804,1 01/11/2010        3150 
      3 ALTAIR          04/11/2010     2909,33 01/11/2010        3150 
      3 ALTAIR          05/11/2010     5616,86 01/11/2010        3150 
      3 ALTAIR          08/11/2010           0 01/11/2010        3150 
      3 ALTAIR          09/11/2010           0 01/11/2010        3150 
      3 ALTAIR          10/11/2010           0 01/11/2010        3150 
      3 ALTAIR          11/11/2010           0 01/11/2010        3150 
      3 ALTAIR          12/11/2010           0 01/11/2010        3150 
      3 ALTAIR          16/11/2010           0 01/11/2010        3150 
      3 ALTAIR          17/11/2010           0 01/11/2010        3150 
      3 ALTAIR          18/11/2010           0 01/11/2010        3150 
      3 ALTAIR          19/11/2010           0 01/11/2010        3150 
      3 ALTAIR          22/11/2010           0 01/11/2010        3150 
      3 ALTAIR          23/11/2010           0 01/11/2010        3150 
      3 ALTAIR          24/11/2010           0 01/11/2010        3150 
      3 ALTAIR          25/11/2010           0 01/11/2010        3150 
      3 ALTAIR          26/11/2010           0 01/11/2010        3150 
      3 ALTAIR          29/11/2010           0 01/11/2010        3150 
      3 ALTAIR          30/11/2010           0 01/11/2010        3150 
  
20 rows selected
This way solved my problem.

Thank you very much.
Top
jdfuhr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 27 Jul 2010 2:59 pm
Location: Florianópolis - SC

Thanks diegopedrao You are: -The

I left your way with a view only that gets more elegant !! Hehehehehe


Hugs
Top
jdfuhr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 27 Jul 2010 2:59 pm
Location: Florianópolis - SC

Resurrecting the topic:

The report is ball show, working perfectly, helping enough the sales manager.

But as the customer is never content with the first version, he asked me to make breaks per week, that is, to display totals per week.

I tried to do this by adding 1 column with the number of the week:

Select all

TO_CHAR(FVD.DTNEG,'W') As "SEMANA"
only he is not breaking for the week of the month, he picks up the first 4 days And puts week 1, the next 4 week 2 and so on.

Select all

 
SQL>  
SQL> SELECT  DISTINCT 
  2  FVD.CODVEND As "CODIGO.VENDEDOR", 
  3  FVD.APELIDO As "VENDEDOR.APELIDO", 
  4  FVD.DTNEG As "DATA", 
  5  FVD.VENDAS As "VENDEDOR.VENDAS", 
  6  FVD.METADIARIA As "COTA.DIARIA", 
  7  FVD.VENDAS - FVD.METADIARIA As "VENDEDOR.DIFERENÇA", 
  8  TO_CHAR(FVD.DTNEG,'W') As "SEMANA" 
  9  FROM VENDA_DIARIA FVD 
 10   WHERE ( TO_CHAR(FVD.DTNEG,'YYYYMM') = 201012)   AND 
 11   ( FVD.CODVEND = 05) 
 12   ORDER BY "CODIGO.VENDEDOR", "SEMANA", "DATA" 
 13   
SQL> / 
  
CODIGO.VENDEDOR VENDEDOR.APELIDO DATA        VENDEDOR.VENDAS COTA.DIARIA VENDEDOR.DIFERENÇA SEMANA 
--------------- ---------------- ----------- --------------- ----------- ------------------ ------ 
              5 ROBSON           01/12/2010          1879,47 3142,857142  -1263,38714285714 1 
              5 ROBSON           02/12/2010          5462,85 3142,857142   2319,99285714286 1 
              5 ROBSON           03/12/2010          3831,26 3142,857142   688,402857142857 1 
              5 ROBSON           06/12/2010          3053,74 3142,857142  -89,1171428571429 1 
              5 ROBSON           07/12/2010          2981,82 3142,857142  -161,037142857143 1 
              5 ROBSON           08/12/2010          3100,41 3142,857142  -42,4471428571429 2 
              5 ROBSON           09/12/2010          6193,08 3142,857142   3050,22285714286 2 
              5 ROBSON           10/12/2010           1207,1 3142,857142  -1935,75714285714 2 
              5 ROBSON           13/12/2010          1402,67 3142,857142  -1740,18714285714 2 
              5 ROBSON           14/12/2010          2498,26 3142,857142  -644,597142857143 2 
              5 ROBSON           15/12/2010          1903,42 3142,857142  -1239,43714285714 3 
              5 ROBSON           16/12/2010          4720,05 3142,857142   1577,19285714286 3 
              5 ROBSON           17/12/2010          1524,94 3142,857142  -1617,91714285714 3 
              5 ROBSON           20/12/2010          1866,02 3142,857142  -1276,83714285714 3 
              5 ROBSON           21/12/2010           1114,8 3142,857142  -2028,05714285714 3 
              5 ROBSON           22/12/2010          4781,02 3142,857142   1638,16285714286 4 
              5 ROBSON           23/12/2010           289,75 3142,857142  -2853,10714285714 4 
              5 ROBSON           27/12/2010          1594,84 3142,857142  -1548,01714285714 4 
              5 ROBSON           28/12/2010          1553,46 3142,857142  -1589,39714285714 4 
              5 ROBSON           29/12/2010          7307,28 3142,857142   4164,42285714286 5 
              5 ROBSON           30/12/2010                0 3142,857142  -3142,85714285714 5
In this way in the report view I command it to group breaks per week and it already displays the totals. But the weeks are incorrect.

The correct one would be:
days - 1,2,3 = week 1
days - 6,7,8,9,10 = week 2
days - 13,14,15,16,17 = week 3
days - 20,21,22,23,24 = week 4
days - 27,28,29,30 , 31 = week 5

Does anyone have any suggestions ??

Thanks
Top
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And then brow, beauty?

see if this is here that you need ....

S !!

Select all

 
SQL> with tbl as ( 
  2  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('01/12/2010','DD/MM/YYYY') DATA, 1879.47 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1263.38714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  3  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('02/12/2010','DD/MM/YYYY') DATA, 5462.85 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  2319.99285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  4  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('03/12/2010','DD/MM/YYYY') DATA, 3831.26 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  688.402857142857 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  5  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('06/12/2010','DD/MM/YYYY') DATA, 3053.74 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -89.1171428571429 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  6  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('07/12/2010','DD/MM/YYYY') DATA, 2981.82 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -161.037142857143 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  7  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('08/12/2010','DD/MM/YYYY') DATA, 3100.41 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -42.4471428571429 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  8  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('09/12/2010','DD/MM/YYYY') DATA, 6193.08 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  3050.22285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  9  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('10/12/2010','DD/MM/YYYY') DATA,  1207.1 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1935.75714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 10  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('13/12/2010','DD/MM/YYYY') DATA, 1402.67 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1740.18714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 11  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('14/12/2010','DD/MM/YYYY') DATA, 2498.26 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -644.597142857143 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 12  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('15/12/2010','DD/MM/YYYY') DATA, 1903.42 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1239.43714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 13  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('16/12/2010','DD/MM/YYYY') DATA, 4720.05 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  1577.19285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 14  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('17/12/2010','DD/MM/YYYY') DATA, 1524.94 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1617.91714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 15  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('20/12/2010','DD/MM/YYYY') DATA, 1866.02 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1276.83714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 16  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('21/12/2010','DD/MM/YYYY') DATA,  1114.8 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -2028.05714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 17  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('22/12/2010','DD/MM/YYYY') DATA, 4781.02 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  1638.16285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 18  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('23/12/2010','DD/MM/YYYY') DATA,  289.75 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -2853.10714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 19  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('27/12/2010','DD/MM/YYYY') DATA, 1594.84 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1548.01714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 20  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('28/12/2010','DD/MM/YYYY') DATA, 1553.46 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1589.39714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 21  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('29/12/2010','DD/MM/YYYY') DATA, 7307.28 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  4164.42285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 22  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('30/12/2010','DD/MM/YYYY') DATA,       0 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -3142.85714285714 VENDEDOR_DIFERENCA FROM DUAL 
 23  ) 
 24  select tbl_A.* 
 25        ,tbl_b.rn 
 26    from (select a.codigo_vendedor 
 27                ,a.data 
 28                ,trunc(a.data,'IW') data2 
 29            from tbl a 
 30           order by a.data 
 31         ) tbl_a 
 32        ,(select data_wk, rownum rn 
 33            from (select distinct trunc(data,'IW') data_wk from tbl order by data_wk) 
 34         ) tbl_b 
 35  where tbl_a.data2 = tbl_b.data_wk 
 36  order by tbl_a.data 
 37  / 
 
CODIGO_VENDEDOR DATA        DATA2               RN 
--------------- ----------- ----------- ---------- 
              5 1/12/2010   29/11/2010           1 
              5 2/12/2010   29/11/2010           1 
              5 3/12/2010   29/11/2010           1 
              5 6/12/2010   6/12/2010            2 
              5 7/12/2010   6/12/2010            2 
              5 8/12/2010   6/12/2010            2 
              5 9/12/2010   6/12/2010            2 
              5 10/12/2010  6/12/2010            2 
              5 13/12/2010  13/12/2010           3 
              5 14/12/2010  13/12/2010           3 
              5 15/12/2010  13/12/2010           3 
              5 16/12/2010  13/12/2010           3 
              5 17/12/2010  13/12/2010           3 
              5 20/12/2010  20/12/2010           4 
              5 21/12/2010  20/12/2010           4 
              5 22/12/2010  20/12/2010           4 
              5 23/12/2010  20/12/2010           4 
              5 27/12/2010  27/12/2010           5 
              5 28/12/2010  27/12/2010           5 
              5 29/12/2010  27/12/2010           5 
              5 30/12/2010  27/12/2010           5 
 
21 rows selected
Top
jdfuhr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 27 Jul 2010 2:59 pm
Location: Florianópolis - SC

Exactly this Cristiano,

I just can not leave generic, that is, to make it so that it counts correctly when I have several releases at the base. Today I have 9 sellers and sales of 4 months, when I do the rownum it goes beyond the amount of the month, getting as if it were week of the year and with the select exactly the way it is it behind me 1 day a week.


But it was worth the tip, I think through it turns easier to find the solution.
Top
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And then brow, beauty?

Changing Subquery to have this information ..

Select all

 
SQL> with tbl as ( 
  2  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('01/12/2010','DD/MM/YYYY') DATA, 1879.47 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1263.38714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  3  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('02/12/2010','DD/MM/YYYY') DATA, 5462.85 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  2319.99285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  4  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('03/12/2010','DD/MM/YYYY') DATA, 3831.26 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  688.402857142857 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  5  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('06/12/2010','DD/MM/YYYY') DATA, 3053.74 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -89.1171428571429 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  6  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('07/12/2010','DD/MM/YYYY') DATA, 2981.82 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -161.037142857143 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  7  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('08/12/2010','DD/MM/YYYY') DATA, 3100.41 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -42.4471428571429 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  8  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('09/12/2010','DD/MM/YYYY') DATA, 6193.08 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  3050.22285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
  9  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('10/12/2010','DD/MM/YYYY') DATA,  1207.1 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1935.75714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 10  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('13/12/2010','DD/MM/YYYY') DATA, 1402.67 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1740.18714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 11  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('14/12/2010','DD/MM/YYYY') DATA, 2498.26 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -644.597142857143 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 12  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('15/12/2010','DD/MM/YYYY') DATA, 1903.42 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1239.43714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 13  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('16/12/2010','DD/MM/YYYY') DATA, 4720.05 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  1577.19285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 14  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('17/12/2010','DD/MM/YYYY') DATA, 1524.94 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1617.91714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 15  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('20/12/2010','DD/MM/YYYY') DATA, 1866.02 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1276.83714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 16  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('21/12/2010','DD/MM/YYYY') DATA,  1114.8 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -2028.05714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 17  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('22/12/2010','DD/MM/YYYY') DATA, 4781.02 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  1638.16285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 18  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('23/12/2010','DD/MM/YYYY') DATA,  289.75 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -2853.10714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 19  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('27/12/2010','DD/MM/YYYY') DATA, 1594.84 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1548.01714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 20  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('28/12/2010','DD/MM/YYYY') DATA, 1553.46 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -1589.39714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 21  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('29/12/2010','DD/MM/YYYY') DATA, 7307.28 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA,  4164.42285714286 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 22  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('30/12/2010','DD/MM/YYYY') DATA,       0 VENDEDOR_VENDAS, 3142.857142 COTA_DIARIA, -3142.85714285714 VENDEDOR_DIFERENCA FROM DUAL UNION ALL 
 23  select 5 CODIGO_VENDEDOR, 'ROBSON' VENDEDOR_APELIDO, TO_DATE('15/01/2011','DD/MM/YYYY') DATA, 1000.00 VENDEDOR_VENDAS, 1242.857142 COTA_DIARIA, -1242.85714285714 VENDEDOR_DIFERENCA FROM DUAL 
 24  ) 
 25  select tbl_A.* 
 26        ,tbl_b.rn 
 27    from (select a.* 
 28                ,trunc(a.data,'IW') data2 
 29                ,to_char(a.data,'W') data3 -- NRO DA SEMANA DENTRO DO mês 
 30            from tbl a 
 31           order by a.data 
 32         ) tbl_a 
 33        ,(select data_wk, rownum rn 
 34            from (select distinct trunc(data,'IW') data_wk from tbl order by data_wk) 
 35         ) tbl_b 
 36  where tbl_a.data2 = tbl_b.data_wk 
 37  order by tbl_a.data 
 38  / 
 
CODIGO_VENDEDOR VENDEDOR_APELIDO DATA        VENDEDOR_VENDAS COTA_DIARIA VENDEDOR_DIFERENCA DATA2       DATA3         RN 
--------------- ---------------- ----------- --------------- ----------- ------------------ ----------- ----- ---------- 
              5 ROBSON           1/12/2010           1879,47 3142,857142  -1263,38714285714 29/11/2010  1              1 
              5 ROBSON           2/12/2010           5462,85 3142,857142   2319,99285714286 29/11/2010  1              1 
              5 ROBSON           3/12/2010           3831,26 3142,857142   688,402857142857 29/11/2010  1              1 
              5 ROBSON           6/12/2010           3053,74 3142,857142  -89,1171428571429 6/12/2010   1              2 
              5 ROBSON           7/12/2010           2981,82 3142,857142  -161,037142857143 6/12/2010   1              2 
              5 ROBSON           8/12/2010           3100,41 3142,857142  -42,4471428571429 6/12/2010   2              2 
              5 ROBSON           9/12/2010           6193,08 3142,857142   3050,22285714286 6/12/2010   2              2 
              5 ROBSON           10/12/2010           1207,1 3142,857142  -1935,75714285714 6/12/2010   2              2 
              5 ROBSON           13/12/2010          1402,67 3142,857142  -1740,18714285714 13/12/2010  2              3 
              5 ROBSON           14/12/2010          2498,26 3142,857142  -644,597142857143 13/12/2010  2              3 
              5 ROBSON           15/12/2010          1903,42 3142,857142  -1239,43714285714 13/12/2010  3              3 
              5 ROBSON           16/12/2010          4720,05 3142,857142   1577,19285714286 13/12/2010  3              3 
              5 ROBSON           17/12/2010          1524,94 3142,857142  -1617,91714285714 13/12/2010  3              3 
              5 ROBSON           20/12/2010          1866,02 3142,857142  -1276,83714285714 20/12/2010  3              4 
              5 ROBSON           21/12/2010           1114,8 3142,857142  -2028,05714285714 20/12/2010  3              4 
              5 ROBSON           22/12/2010          4781,02 3142,857142   1638,16285714286 20/12/2010  4              4 
              5 ROBSON           23/12/2010           289,75 3142,857142  -2853,10714285714 20/12/2010  4              4 
              5 ROBSON           27/12/2010          1594,84 3142,857142  -1548,01714285714 27/12/2010  4              5 
              5 ROBSON           28/12/2010          1553,46 3142,857142  -1589,39714285714 27/12/2010  4              5 
              5 ROBSON           29/12/2010          7307,28 3142,857142   4164,42285714286 27/12/2010  5              5 
              5 ROBSON           30/12/2010                0 3142,857142  -3142,85714285714 27/12/2010  5              5 
              5 ROBSON           15/1/2011              1000 1242,857142  -1242,85714285714 10/1/2011   3              6
Top
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Brother, sorry for improving the name of the fields of query .. the day hj this half run here .... project homologation, you know how it is rsrsrs
Top
jdfuhr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 27 Jul 2010 2:59 pm
Location: Florianópolis - SC

Thanks Tineks,

The problem is that this way it does not obey the weeks properly, it divides the month in 4 weeks independent of the real order of them. If you observe the Data3 field is with the same sequence as I have previously placed.
Top
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And then brow, beauty? In the rn column you have the order of the week analyzing the result as a whole (1 .. 10, 20.) .. in the data3 column you have the order of the week within the month ..

How did you want this ordination?
Top
Post Reply

Return to “PL/SQL”

  • Information

  • Who is online

    Users browsing this forum: No registered users and 3 guests