Delete Duplicate Records by Group BY

1

Good morning,

Does anyone know if there is a possibility to delete duplicate records as follows:

I have a query that brings the result

All fields are repeated except the NDO field, and NDOGroup, are these fields that are duplicating the records.

I'm forced to put these two fields in my group by when I give the select.

Example:

 SELECT

   AD.[vch_od],
   MK.NDMarket,  
   MK.TypeMercado,
   MK.NDOrigin as Praça,
   TR.Analyst,
  -- DTB.Season,
 --  DTB.EvDef,
   AD.[dtt_data_captura],
   AD.semana,
   AD.DataMes,
   AD.NDO,
   AD.NDOGroup,
--   PR.valorpremium,  
   AD.[Flt Type],
   IND.[Flt Type],
   AD.[Flt_base] as AD,
   G3.[flt_base] as G3,
   JJ.[flt_base] as JJ,
   OC.[flt_base] as OC,
   P3.[flt_base] as P3
  -- IND.[flt_base] as IND

from #TARIFASAD AD

GROUP BY    
AD.[vch_od],
   MK.NDMarket,  
   MK.TypeMercado,
   MK.NDOrigin,
   TR.Analyst,
 --  DTB.Season,
 --  DTB.EvDef,
  -- AD.NDOGroup,
   AD.[dtt_data_captura],
   AD.semana,
   AD.DataMes,
   AD.NDO,
   AD.NDOGroup,
   --   PR.valorpremium,  
   AD.[Flt Type],
   IND.[Flt Type],
   AD.[Flt_base],
   G3.[flt_base],
   JJ.[flt_base],
   OC.[flt_base],
   P3.[flt_base] 
  -- IND.[flt_base] as IND

Today my result is this:

AJUREC  RECAJU  Monopoly    REC Diego                           12/1/2016   11/27/2016  DRT CNX 415.843396
AJUREC  RECAJU  Monopoly    REC Diego                           12/2/2016   11/27/2016  DRT CNX 303.539575
BPSSSA  SSABPS  Super-Competitive   SSA Diego                           12/1/2016   11/27/2016  DRT CNX 290.775
BPSSSA  SSABPS  Super-Competitive   SSA Diego                           12/2/2016   11/27/2016  DRT CNX 217.514213

You do not have a duplicate record. It brings me the average rate by catch date (grouped)

I need to insert the two NDO columns, and NDOGroup

that are:

NDOGroup
(15-21)
(15-21)
(22-29)
(22-29)
(15-21)
(15-21)

NDO
15
17
20
22
27
29

datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data]) AS NDO, 

  case
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])< '1' then 'NDO 00'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])< '5' then'(1-4)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])< '8' then '(5-7)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'15' then '(8-14)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'22' then '(15-21)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'30' then '(22-29)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'50' then '(30-49)'
  else '50+'
  END AS NDOGroup

I understand that the dtt_data field, shown below, will differentiate. As long as dtt_capture is on 01/12/2016, in several lines the dtt_data field will be different. So in the above datadiff sometimes it will give me the difference of 1 day, sometimes 3 and so on.

dtt_data_captura    dtt_data
12/1/2016   13/3/2016
12/1/2016   16/3/2016
12/1/2016   21/3/2016
12/1/2016   17/3/2016

But I would like to find a way to bring the NDOGroup from a group just without repeating:     NDOGroup     (15-21)     (22-29)

case
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])< '1' then 'NDO 00'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])< '5' then'(1-4)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])< '8' then '(5-7)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'15' then '(8-14)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'22' then '(15-21)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'30' then '(22-29)'
  when datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data])<'50' then '(30-49)'
  else '50+'

And with a single NDOGroup field bring the corresponding NDO:

datediff(dd,QL2.[dtt_data_captura],QL2.[dtt_data]) 

I thought of something more or less, deleting records from the NDOGroup field and NDO bringing me to smaller or larger

 DELETE a FROM #TARIFASAD AS a, nomes AS b WHERE a.vch_od=b.vch_od AND a.NDOGroup < b.NDOGroup

Thank you

    
asked by anonymous 31.03.2017 / 14:26

1 answer

2

Do something like this:

delete from #tarifasad where ndo in (
select ndo from  (
SELECT

   AD.[vch_od],
   MK.NDMarket,  
   MK.TypeMercado,
   MK.NDOrigin as Praça,
   TR.Analyst,
  -- DTB.Season,
 --  DTB.EvDef,
   AD.[dtt_data_captura],
   AD.semana,
   AD.DataMes,
   AD.NDO,
   AD.NDOGroup,
--   PR.valorpremium,  
   AD.[Flt Type],
   IND.[Flt Type],
   AD.[Flt_base] as AD,
   G3.[flt_base] as G3,
   JJ.[flt_base] as JJ,
   OC.[flt_base] as OC,
   P3.[flt_base] as P3
  -- IND.[flt_base] as IND

from #TARIFASAD AD

GROUP BY    
AD.[vch_od],
   MK.NDMarket,  
   MK.TypeMercado,
   MK.NDOrigin,
   TR.Analyst,
 --  DTB.Season,
 --  DTB.EvDef,
  -- AD.NDOGroup,
   AD.[dtt_data_captura],
   AD.semana,
   AD.DataMes,
   AD.NDO,
   AD.NDOGroup,
   --   PR.valorpremium,  
   AD.[Flt Type],
   IND.[Flt Type],
   AD.[Flt_base],
   G3.[flt_base],
   JJ.[flt_base],
   OC.[flt_base],
   P3.[flt_base] 
  -- IND.[flt_base] as IND 
  ) A  ) AND ....

but make a select before to see if you are returning only the records that you really want to exclude, you may have to refine this query by adding other filters.

Test script for you to check the operation:

create table teste( 
  ndo number(5),
  descricao varchar2(10))

insert into teste values( 1, 'AB' )  
insert into teste values( 1, 'AC' )  
insert into teste values( 1, 'CC' )  
insert into teste values( 2, 'DC' )  

select * from teste  

delete from teste where ndo in (   
select ndo from  (   
SELECT ndo, DESCRICAO FROM TESTE 
GROUP BY ndo, descricao ) a  ) and descricao like 'A%'

select * from teste

Check script for operation and then apply the same concept to your table.

    
31.03.2017 / 14:32