July 11, 2018 at 2:53 pm
Mike01 - Wednesday, July 11, 2018 1:47 PMI don't see an attachment
I attached it with this reply.
July 12, 2018 at 7:09 am
There 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, 2
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 12, 2018 at 9:37 am
Mike01 - 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, 2
Works 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?
July 12, 2018 at 10:28 am
sanket.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<roweffectivedate
you should be able to see the erroneous records.
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.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply