Home Forums SQL Server 2017 SQL Server 2017 - Development Need to display a column with deleted overlapping dates RE: Need to display a column with deleted overlapping dates
July 12, 2018 at 10:59 am
george_at_sql - Thursday, July 12, 2018 10:28 AMsanket.wagh7689 - Thursday, July 12, 2018 9:37 AMMike01 - Thursday, July 12, 2018 7:09 AMThere was some bad data in there, that e didn't account for. I added the criteria below
;WITH CTE AS (
select pcode,pkey, RowEffectiveDate, rowenddate, RowEffectiveDateCalc, RowEndDateCalc,
RowEffectiveDateCalc CurRowEffectiveDateCalc, RowEndDateCalc CurRowEndDateCalc
from (
SELECT pcode,pkey, RowEffectiveDate, rowenddate
,cast(RowEffectiveDate as date) RowEffectiveDateCalc
,cast(rowenddate as date) RowEndDateCalc
, ROW_NUMBER() OVER(PARTITION BY pcode ORDER BY RowEffectiveDate, rowenddate, PKey) AS rn
FROM DimPro
--where PCode = 'TX5125'
) X
where rn = 1
union all
select c.pcode, c.pkey, c.RowEffectiveDate, c.rowenddate, c.RowEffectiveDateCalc, c.RowEndDateCalc,
c.RowEffectiveDateCalc CurRowEffectiveDateCalc, c.RowEndDateCalc CurRowEndDateCalc
from (
SELECT NextRow.PCode, NextRow.Pkey, NextRow.RowEffectiveDate, NextRow.rowenddate
,cast(NextRow.RowEffectiveDate as date) RowEffectiveDateCalc
,cast(NextRow.rowenddate as date) RowEndDateCalc
,cast(CurRow.RowEffectiveDate as date) CurRowEffectiveDateCalc
,cast(CurRow.rowenddate as date) CurRowEndDateCalc
,Row_Number() over (partition by NextRow.pcode order by NextRow.RowEffectiveDate, NextRow.rowenddate, NextRow.PKey) AS rn
FROM CTE CurRow
join DimPro NextRow
on CurRow.PCode = NextRow.PCode
and NextRow.RowEffectiveDate <= NextRow.rowenddate
and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
) c
where rn = 1
)select * from CTE
order by 1, 2Works like a charm and within seconds. Can you please tell me what was the bad data that was causing a problem?
Also, if there any way we can write all these unwanted/overlapping records to a file so that we can identify them in others tables too and remove them?You have records whose end_dates is less than the start date which is prevented by the following condition
NextRow.RowEffectiveDate <= NextRow.rowenddate
So if you
select *
from dbo.dimPro
where rowenddate<roweffectivedateyou should be able to see the erroneous records.
Thank you! you are all awesome.