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