Help on select (crosstab)

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

Poston Thu, 14 Jun 2007 9:46 am

Dear friends I have a difficulty in this select, this select takes what she and day 01 and account and subsequent to the end of the month for family
Code: Select all
   Select
    mp.Attribute1 Familia
     , count(decode(to_char(Spld.Creation_Date, 'DD'), '01' ,Spld.Lot_Number ) )  data01
     , count(decode(to_char(Spld.Creation_Date, 'DD'), '02' ,Spld.Lot_Number ) )  data02
     , count(decode(to_char(Spld.Creation_Date, 'DD'), '03' ,Spld.Lot_Number ) )  data03
     , count(decode(to_char(Spld.Creation_Date, 'DD'), '04' ,Spld.Lot_Number ) )  data04
     , count(decode(to_char(Spld.Creation_Date, 'DD'), '05' ,Spld.Lot_Number ) )  data05
     , count(decode(to_char(Spld.Creation_Date, 'DD'), '31' ,Spld.Lot_Number ) )  data31

    From
      Ra_Addresses_All               RA
     ,Ra_Site_Uses_All               RSU
     ,Ra_Customers                   RC
     ,So_Headers_All                 SH           
     ,So_Lines_All                   SLA
     ,Mtl_System_Items               MSI
     ,So_Line_Details                SLD
     ,So_Picking_Lines_All           SPLA
     ,So_Picking_Line_Details        SPLD
     ,apps.Mtl_Planners         mp    --- planejadores

   Where
      Spld.Creation_Date         Between to_date('01-OCT-2006','DD-MON-YYYY') --:CG$CTRL.Dt_Ini
      And to_date('30-OCT-2006','DD-MON-YYYY') --:CG$CTRL.Dt_Ini And :CG$CTRL.Dt_Fim + .99999
      And Spld.Warehouse_Id            = 45 --:parameter.Organization_Id   
      And Spla.Picking_Line_Id         = Spld.Picking_Line_Id   
      And Spla.Warehouse_Id            = Spld.Warehouse_Id   
      And Sld.Line_Id                  = Spla.Order_Line_Id
      And Sld.Lot_Number               = Spld.Lot_Number
      And Sld.Warehouse_Id             = Spld.Warehouse_Id
      And Msi.Inventory_Item_Id        = Sld.Inventory_Item_Id
      And Msi.Organization_Id          = Sld.Warehouse_Id
      And Sla.Line_Id                  = Spla.Order_Line_Id
      And Sla.Warehouse_Id             = Spla.Warehouse_Id
      And Sh.Header_Id                 = Sla.Header_Id
      And Sh.Warehouse_Id              = Sla.Warehouse_Id
      And Rc.Customer_Id               = Sh.Customer_Id
      And Rsu.Site_Use_Id              = Sh.Ship_To_Site_Use_Id
      And Ra.Address_Id                = Rsu.Address_Id
      and mp.Planner_Code       = msi.Planner_Code
      and mp.Attribute1  is not null

     Group by
   mp.Attribute1
the result and This:
Code: Select all
FAMILIA   DATA01   DATA02   DATA03   DATA04   DATA05   DATA31
CAN       15       104      175      81       87       0
EXPIPG    0        0        0        28       102      0
FSTOCK    11       24       23       33       47       0
INDUST    0        35       74       1        53       0
IPG       21       215      162      135      112      0
the problem and I have to do the following day different now from the 07:00 of the day 01 to day 02 07:00 adding for DATA01 or day 01 and subseguente until the last day of the month left until 00:00 how to do this via select someone has an idea ... I'm accepting anything lol ... a hug:-
madmax
Location: São Paulo

Poston Fri, 15 Jun 2007 4:27 am

It's not the best solutions, but as far as I tested seems to work (I used a simple table, but I think you can understand what has been done by comparing the two versions): Considering the day of 00:00 to 23:59:59 the same day:
Code: Select all
select codigo
, count(decode(to_char(data, 'DD'), '01' ,tipo ) ) data01
, count(decode(to_char(data, 'DD'), '02' ,tipo ) ) data02
, count(decode(to_char(data, 'DD'), '03' ,tipo ) ) data03
, count(decode(to_char(data, 'DD'), '04' ,tipo ) ) data04
from t03 a
where a.data between to_date(20070101, 'yyyymmdd') and to_date(20070131, 'yyyymmdd')
group  by a.codigo
order by 1;
Whereas the day of 07:00 to 06:59:59 the next day:
Code: Select all
select codigo
, count(decode(to_char(dat_ini, 'DD'), '01' ,tipo ) ) data01
, count(decode(to_char(dat_ini, 'DD'), '02' ,tipo ) ) data02
, count(decode(to_char(dat_ini, 'DD'), '03' ,tipo ) ) data03
, count(decode(to_char(dat_ini, 'DD'), '04' ,tipo ) ) data04
from t03 a
,
(
select to_date(20070101, 'yyyymmdd') +
         rownum - ((24-7)/24)                  dat_ini
       -- Todos os dias do mês, a partir das 07:00:00
       -- (utilizar a data inicial como parâmetro)

     , to_date(20070101, 'yyyymmdd') +
       rownum - ((24-7)/24)-(1/(24*60*60))+1 dat_fim
       -- 06:59:59 do próximo dia (utilizar a data inicial como parâmetro)

--  Usar a all_objects, ou qualquer outra tabela
--  de seu sistema que certamente trará um número
--  suficiente de linhas (cada dia de período de leitura
--  irá retornar uma linha)
--  Se você estiver na versão 10g (ou talvez na 9i
--  também funcione), é mais garantido utilizar
--  select ... from dual connect by level <= ...
from all_objects
--  Busca quantos dias deverão ser trazidos
--  (parâmetro data final + 1 -  data inical)
where rownum <= to_date(20070131, 'yyyymmdd')+1 - to_date(20070101, 'yyyymmdd')
) b
where a.data between b.dat_ini and b.dat_fim
group  by a.codigo
order by 1;
rogenaro
Location: Londrina - PR

Poston Sat, 16 Jun 2007 4:54 pm

Thanks for your help Rogenaro, I will test on Monday at work and something of a splash here in the forum thanks man. !!! :D
madmax
Location: São Paulo


  • See also
    Replies
    Views
    Last Post


              Return to SQL

              Who is online

              Users browsing this forum: No registered users and 2 guests