Need to display a column with deleted overlapping dates

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 9, 2018 at 1:32 pm

  #1996855

  Mike01 - Friday, July 6, 2018 10:01 AM

  Here's a recursive CTE that returns the values you want.  Seems you wants the contiguous keys and ignore all the noise in between

  
Drop table if exists #dimP
go

  CREATE TABLE #dimP (
  pcode char(4),
  pkey int,
  RowEffectiveDate datetime,
  rowenddate datetime
);
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
VALUES
     ('PL56',8855,'1900-01-01 00:00:00.000','2018-02-13 23:59:59.997'),
     ('PL56',8856,'2018-02-09 15:09:52.000','2018-02-14 23:59:59.997'),
     ('PL56',8903,'2018-02-09 15:09:52.000','2018-02-15 23:59:59.997'),
     ('PL56',8904,'2018-02-09 15:09:52.000','2018-02-16 23:59:59.997'),
     ('PL56',8935,'2018-02-14 00:00:00.000','2018-02-17 23:59:59.997'),
     ('PL56',8944,'2018-02-15 00:00:00.000','2018-02-18 23:59:59.997'),
     ('PL56',8955,'2018-02-16 00:00:00.000','2018-03-02 23:59:59.997'),
     ('PL56',8965,'2018-02-17 00:00:00.000','2018-03-03 23:59:59.997'),
     ('PL56',8972,'2018-02-18 00:00:00.000','2018-03-04 23:59:59.997'),
     ('PL56',8973,'2018-02-19 00:00:00.000','2018-03-05 23:59:59.997'),
     ('PL56',9114,'2018-03-03 00:00:00.000','2018-04-24 23:59:59.997'),
     ('PL56',9115,'2018-03-04 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',9136,'2018-03-05 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',9140,'2018-03-06 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11597,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',11820,'2018-04-26 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',11837,'2018-04-27 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11839,'2018-04-30 00:00:00.000','2018-05-22 23:59:59.997'),
     ('PL56',12372,'2018-05-23 00:00:00.000','9999-12-31 00:00:00.000');
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  VALUES  ('KO18', 502 , '1900-01-01 00:00:00.000', '2017-12-06 23:59:59.997'),
    ('KO18', 5431 , '2017-12-07 00:00:00.000', '2018-01-29 23:59:59.997'),
    ('KO18', 6852 , '2018-01-30 00:00:00.000', '2018-02-09 23:59:59.997'),
    ('KO18', 8861 , '2018-02-10 00:00:00.000', '2018-04-24 23:59:59.997'),
    ('KO18', 8862 , '2018-02-10 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11764, '2018-04-25 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11797, '2018-04-26 00:00:00.000', '9999-12-31 00:00:00.000');

  /*
SELECT *, ROW_NUMBER() OVER(PARTITION BY RowEffectiveDate ORDER BY rowenddate, PKey) AS rn
FROM #dimP
order by 1, 2
*/

  ;WITH CTE AS (
select pcode,pkey, RowEffectiveDate, rowenddate,RowEffectiveDateCalc,RowEndDateCalc
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 #dimP
--         where PCode = 'PL56'
        ) X
    where rn = 1
union all
    SELECT NextRow.PCode, NextRow.Pkey, NextRow.RowEffectiveDate, NextRow.rowenddate
            ,cast(NextRow.RowEffectiveDate as date) RowEffectiveDateCalc
                    ,cast(NextRow.rowenddate as date) RowEndDateCalc
    FROM CTE CurRow
        join #dimP NextRow
        on CurRow.PCode = NextRow.PCode
        and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
)

  select * from CTE
order by 1, 2


  Wow this query worked like a charm. Thanks Mike.

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 9, 2018 at 3:52 pm

  #1996880

  Mike01 - Friday, July 6, 2018 10:01 AM

  Here's a recursive CTE that returns the values you want.  Seems you wants the contiguous keys and ignore all the noise in between

  
Drop table if exists #dimP
go

  CREATE TABLE #dimP (
  pcode char(4),
  pkey int,
  RowEffectiveDate datetime,
  rowenddate datetime
);
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
VALUES
     ('PL56',8855,'1900-01-01 00:00:00.000','2018-02-13 23:59:59.997'),
     ('PL56',8856,'2018-02-09 15:09:52.000','2018-02-14 23:59:59.997'),
     ('PL56',8903,'2018-02-09 15:09:52.000','2018-02-15 23:59:59.997'),
     ('PL56',8904,'2018-02-09 15:09:52.000','2018-02-16 23:59:59.997'),
     ('PL56',8935,'2018-02-14 00:00:00.000','2018-02-17 23:59:59.997'),
     ('PL56',8944,'2018-02-15 00:00:00.000','2018-02-18 23:59:59.997'),
     ('PL56',8955,'2018-02-16 00:00:00.000','2018-03-02 23:59:59.997'),
     ('PL56',8965,'2018-02-17 00:00:00.000','2018-03-03 23:59:59.997'),
     ('PL56',8972,'2018-02-18 00:00:00.000','2018-03-04 23:59:59.997'),
     ('PL56',8973,'2018-02-19 00:00:00.000','2018-03-05 23:59:59.997'),
     ('PL56',9114,'2018-03-03 00:00:00.000','2018-04-24 23:59:59.997'),
     ('PL56',9115,'2018-03-04 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',9136,'2018-03-05 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',9140,'2018-03-06 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11597,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',11820,'2018-04-26 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',11837,'2018-04-27 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11839,'2018-04-30 00:00:00.000','2018-05-22 23:59:59.997'),
     ('PL56',12372,'2018-05-23 00:00:00.000','9999-12-31 00:00:00.000');
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  VALUES  ('KO18', 502 , '1900-01-01 00:00:00.000', '2017-12-06 23:59:59.997'),
    ('KO18', 5431 , '2017-12-07 00:00:00.000', '2018-01-29 23:59:59.997'),
    ('KO18', 6852 , '2018-01-30 00:00:00.000', '2018-02-09 23:59:59.997'),
    ('KO18', 8861 , '2018-02-10 00:00:00.000', '2018-04-24 23:59:59.997'),
    ('KO18', 8862 , '2018-02-10 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11764, '2018-04-25 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11797, '2018-04-26 00:00:00.000', '9999-12-31 00:00:00.000');

  /*
SELECT *, ROW_NUMBER() OVER(PARTITION BY RowEffectiveDate ORDER BY rowenddate, PKey) AS rn
FROM #dimP
order by 1, 2
*/

  ;WITH CTE AS (
select pcode,pkey, RowEffectiveDate, rowenddate,RowEffectiveDateCalc,RowEndDateCalc
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 #dimP
--         where PCode = 'PL56'
        ) X
    where rn = 1
union all
    SELECT NextRow.PCode, NextRow.Pkey, NextRow.RowEffectiveDate, NextRow.rowenddate
            ,cast(NextRow.RowEffectiveDate as date) RowEffectiveDateCalc
                    ,cast(NextRow.rowenddate as date) RowEndDateCalc
    FROM CTE CurRow
        join #dimP NextRow
        on CurRow.PCode = NextRow.PCode
        and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
)

  select * from CTE
order by 1, 2


  Hi Mike, 

  I just ran it for a large set of records and it looks like the query is taking a lot of time. I did use the below at the end of my query but to no success.Please help.

  option (maxrecursion 0)

• Mike01

  SSChampion

  Points: 11395

  More actions

  July 9, 2018 at 6:09 pm

  #1996886

  Did you look at the plan to see what it was doing?  How much is alot of data?

  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/

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 10, 2018 at 9:31 am

  #1997003

  Mike01 - Monday, July 9, 2018 6:09 PM

  Did you look at the plan to see what it was doing?  How much is alot of data?

  I have attached the execution plan here. There are 11,654 records in the table.

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 10, 2018 at 10:22 am

  #1997018

  sanket.wagh7689 - Tuesday, July 10, 2018 9:31 AM

  Mike01 - Monday, July 9, 2018 6:09 PM

  Did you look at the plan to see what it was doing?  How much is alot of data?

  I have attached the execution plan here. There are 11,654 records in the table.

  Plus if you see below this record has a overlapping date too.
  

  pcode	pkey	RowEffectiveDate	rowenddate	RowEffectiveDateCalc	RowEndDateCalc
  LA0900	502	1900-01-01 00:00:00.000	2017-12-06 23:59:59.997	1/1/1900	12/6/2017
  LA0900	5431	2017-12-07 00:00:00.000	2018-01-29 23:59:59.997	12/7/2017	1/29/2018
  LA0900	6852	2018-01-30 00:00:00.000	2018-02-09 23:59:59.997	1/30/2018	2/9/2018
  LA0900	8861	2018-02-10 00:00:00.000	2018-04-24 23:59:59.997	2/10/2018	4/24/2018
  LA0900	8862	2018-02-10 00:00:00.000	2018-04-25 23:59:59.997	2/10/2018	4/25/2018
  LA0900	11764	2018-04-25 00:00:00.000	2018-04-25 23:59:59.997	4/25/2018	4/25/2018
  LA0900	11797	2018-04-26 00:00:00.000	9999-12-31 00:00:00.000	4/26/2018	12/31/9999
  LA0900	11797	2018-04-26 00:00:00.000	9999-12-31 00:00:00.000	4/26/2018	12/31/9999

• Mike01

  SSChampion

  Points: 11395

  More actions

  July 10, 2018 at 10:51 am

  #1997028

  Try this.

  btw, my table has over 200,000 records and the query returns in 5 seconds without the PCode = parameter

  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  values
  ('LA0900',502,'1900-01-01 00:00:00.000','2017-12-06 23:59:59.997'),
  ('LA0900',5431,'2017-12-07 00:00:00.000','2018-01-29 23:59:59.997'),
  ('LA0900',6852,'2018-01-30 00:00:00.000','2018-02-09 23:59:59.997'),
  ('LA0900',8861,'2018-02-10 00:00:00.000','2018-04-24 23:59:59.997'),
  ('LA0900',8862,'2018-02-10 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11764,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000')

  /*
  -- Add more records
  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  select PCode + cast(n as varchar(5)), pkey, RowEffectiveDate, rowenddate
  from #dimP
      cross apply dbo.Tally(1,10000)
  where PCODE like 'PL%'

  */

  ;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 #dimP
               where PCode = 'LA0900'
              ) 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 #dimP NextRow
                  on CurRow.PCode = NextRow.PCode
                  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/

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 10, 2018 at 1:21 pm

  #1997053

  Mike01 - Tuesday, July 10, 2018 10:51 AM

  Try this.

  btw, my table has over 200,000 records and the query returns in 5 seconds without the PCode = parameter

  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  values
  ('LA0900',502,'1900-01-01 00:00:00.000','2017-12-06 23:59:59.997'),
  ('LA0900',5431,'2017-12-07 00:00:00.000','2018-01-29 23:59:59.997'),
  ('LA0900',6852,'2018-01-30 00:00:00.000','2018-02-09 23:59:59.997'),
  ('LA0900',8861,'2018-02-10 00:00:00.000','2018-04-24 23:59:59.997'),
  ('LA0900',8862,'2018-02-10 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11764,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000')

  /*
  -- Add more records
  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  select PCode + cast(n as varchar(5)), pkey, RowEffectiveDate, rowenddate
  from #dimP
      cross apply dbo.Tally(1,10000)
  where PCODE like 'PL%'

  */

  ;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 #dimP
               where PCode = 'LA0900'
              ) 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 #dimP NextRow
                  on CurRow.PCode = NextRow.PCode
                  and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
              ) c
          where rn = 1
  )

  select * from CTE
  order by 1, 2
  

  It's still the same. It is pretty fast for a few records but it takes too long for a large set of records and doesn't really give any result. I had attached my execution plan in the previous post.
  I have attached the execution plan again.

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  July 10, 2018 at 1:41 pm

  #1997059

  sanket.wagh7689 - Tuesday, July 10, 2018 1:21 PM

  Mike01 - Tuesday, July 10, 2018 10:51 AM

  Try this.

  btw, my table has over 200,000 records and the query returns in 5 seconds without the PCode = parameter

  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  values
  ('LA0900',502,'1900-01-01 00:00:00.000','2017-12-06 23:59:59.997'),
  ('LA0900',5431,'2017-12-07 00:00:00.000','2018-01-29 23:59:59.997'),
  ('LA0900',6852,'2018-01-30 00:00:00.000','2018-02-09 23:59:59.997'),
  ('LA0900',8861,'2018-02-10 00:00:00.000','2018-04-24 23:59:59.997'),
  ('LA0900',8862,'2018-02-10 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11764,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000')

  /*
  -- Add more records
  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  select PCode + cast(n as varchar(5)), pkey, RowEffectiveDate, rowenddate
  from #dimP
      cross apply dbo.Tally(1,10000)
  where PCODE like 'PL%'

  */

  ;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 #dimP
               where PCode = 'LA0900'
              ) 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 #dimP NextRow
                  on CurRow.PCode = NextRow.PCode
                  and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
              ) c
          where rn = 1
  )

  select * from CTE
  order by 1, 2
  

  It's still the same. It is pretty fast for a few records but it takes too long for a large set of records and doesn't really give any result. I had attached my execution plan in the previous post.
  I have attached the execution plan again.

  I only see a picture of the execution plan, which is virtually useless.  Try attaching the actual execution plan as a .sqlplan file.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 10, 2018 at 1:43 pm

  #1997060

  drew.allen - Tuesday, July 10, 2018 1:41 PM

  sanket.wagh7689 - Tuesday, July 10, 2018 1:21 PM

  Mike01 - Tuesday, July 10, 2018 10:51 AM

  Try this.

  btw, my table has over 200,000 records and the query returns in 5 seconds without the PCode = parameter

  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  values
  ('LA0900',502,'1900-01-01 00:00:00.000','2017-12-06 23:59:59.997'),
  ('LA0900',5431,'2017-12-07 00:00:00.000','2018-01-29 23:59:59.997'),
  ('LA0900',6852,'2018-01-30 00:00:00.000','2018-02-09 23:59:59.997'),
  ('LA0900',8861,'2018-02-10 00:00:00.000','2018-04-24 23:59:59.997'),
  ('LA0900',8862,'2018-02-10 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11764,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000')

  /*
  -- Add more records
  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  select PCode + cast(n as varchar(5)), pkey, RowEffectiveDate, rowenddate
  from #dimP
      cross apply dbo.Tally(1,10000)
  where PCODE like 'PL%'

  */

  ;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 #dimP
               where PCode = 'LA0900'
              ) 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 #dimP NextRow
                  on CurRow.PCode = NextRow.PCode
                  and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
              ) c
          where rn = 1
  )

  select * from CTE
  order by 1, 2
  

  It's still the same. It is pretty fast for a few records but it takes too long for a large set of records and doesn't really give any result. I had attached my execution plan in the previous post.
  I have attached the execution plan again.

  I only see a picture of the execution plan, which is virtually useless.  Try attaching the actual execution plan as a .sqlplan file.

  Drew

  Uploaded the plan here as .sqlplan file.

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 10, 2018 at 2:07 pm

  #1997064

  sanket.wagh7689 - Tuesday, July 10, 2018 1:43 PM

  drew.allen - Tuesday, July 10, 2018 1:41 PM

  sanket.wagh7689 - Tuesday, July 10, 2018 1:21 PM

  Mike01 - Tuesday, July 10, 2018 10:51 AM

  Try this.

  btw, my table has over 200,000 records and the query returns in 5 seconds without the PCode = parameter

  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  values
  ('LA0900',502,'1900-01-01 00:00:00.000','2017-12-06 23:59:59.997'),
  ('LA0900',5431,'2017-12-07 00:00:00.000','2018-01-29 23:59:59.997'),
  ('LA0900',6852,'2018-01-30 00:00:00.000','2018-02-09 23:59:59.997'),
  ('LA0900',8861,'2018-02-10 00:00:00.000','2018-04-24 23:59:59.997'),
  ('LA0900',8862,'2018-02-10 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11764,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000'),
  ('LA0900',11797,'2018-04-26 00:00:00.000','9999-12-31 00:00:00.000')

  /*
  -- Add more records
  INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  select PCode + cast(n as varchar(5)), pkey, RowEffectiveDate, rowenddate
  from #dimP
      cross apply dbo.Tally(1,10000)
  where PCODE like 'PL%'

  */

  ;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 #dimP
               where PCode = 'LA0900'
              ) 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 #dimP NextRow
                  on CurRow.PCode = NextRow.PCode
                  and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
              ) c
          where rn = 1
  )

  select * from CTE
  order by 1, 2
  

  It's still the same. It is pretty fast for a few records but it takes too long for a large set of records and doesn't really give any result. I had attached my execution plan in the previous post.
  I have attached the execution plan again.

  I only see a picture of the execution plan, which is virtually useless.  Try attaching the actual execution plan as a .sqlplan file.

  Drew

  Uploaded the plan here as .sqlplan file.

  The query mentioned above does not complete. How will I get an actual execution plan without completion?
  I have attached the plan that i got by using SET SHOWPLAN_ALL on;

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  July 11, 2018 at 10:16 am

  #1997156

  sanket.wagh7689 - Monday, July 9, 2018 3:52 PM

  Mike01 - Friday, July 6, 2018 10:01 AM

  Here's a recursive CTE that returns the values you want.  Seems you wants the contiguous keys and ignore all the noise in between

  
Drop table if exists #dimP
go

  CREATE TABLE #dimP (
  pcode char(4),
  pkey int,
  RowEffectiveDate datetime,
  rowenddate datetime
);
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
VALUES
     ('PL56',8855,'1900-01-01 00:00:00.000','2018-02-13 23:59:59.997'),
     ('PL56',8856,'2018-02-09 15:09:52.000','2018-02-14 23:59:59.997'),
     ('PL56',8903,'2018-02-09 15:09:52.000','2018-02-15 23:59:59.997'),
     ('PL56',8904,'2018-02-09 15:09:52.000','2018-02-16 23:59:59.997'),
     ('PL56',8935,'2018-02-14 00:00:00.000','2018-02-17 23:59:59.997'),
     ('PL56',8944,'2018-02-15 00:00:00.000','2018-02-18 23:59:59.997'),
     ('PL56',8955,'2018-02-16 00:00:00.000','2018-03-02 23:59:59.997'),
     ('PL56',8965,'2018-02-17 00:00:00.000','2018-03-03 23:59:59.997'),
     ('PL56',8972,'2018-02-18 00:00:00.000','2018-03-04 23:59:59.997'),
     ('PL56',8973,'2018-02-19 00:00:00.000','2018-03-05 23:59:59.997'),
     ('PL56',9114,'2018-03-03 00:00:00.000','2018-04-24 23:59:59.997'),
     ('PL56',9115,'2018-03-04 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',9136,'2018-03-05 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',9140,'2018-03-06 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11597,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',11820,'2018-04-26 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',11837,'2018-04-27 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11839,'2018-04-30 00:00:00.000','2018-05-22 23:59:59.997'),
     ('PL56',12372,'2018-05-23 00:00:00.000','9999-12-31 00:00:00.000');
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  VALUES  ('KO18', 502 , '1900-01-01 00:00:00.000', '2017-12-06 23:59:59.997'),
    ('KO18', 5431 , '2017-12-07 00:00:00.000', '2018-01-29 23:59:59.997'),
    ('KO18', 6852 , '2018-01-30 00:00:00.000', '2018-02-09 23:59:59.997'),
    ('KO18', 8861 , '2018-02-10 00:00:00.000', '2018-04-24 23:59:59.997'),
    ('KO18', 8862 , '2018-02-10 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11764, '2018-04-25 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11797, '2018-04-26 00:00:00.000', '9999-12-31 00:00:00.000');

  /*
SELECT *, ROW_NUMBER() OVER(PARTITION BY RowEffectiveDate ORDER BY rowenddate, PKey) AS rn
FROM #dimP
order by 1, 2
*/

  ;WITH CTE AS (
select pcode,pkey, RowEffectiveDate, rowenddate,RowEffectiveDateCalc,RowEndDateCalc
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 #dimP
--         where PCode = 'PL56'
        ) X
    where rn = 1
union all
    SELECT NextRow.PCode, NextRow.Pkey, NextRow.RowEffectiveDate, NextRow.rowenddate
            ,cast(NextRow.RowEffectiveDate as date) RowEffectiveDateCalc
                    ,cast(NextRow.rowenddate as date) RowEndDateCalc
    FROM CTE CurRow
        join #dimP NextRow
        on CurRow.PCode = NextRow.PCode
        and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
)

  select * from CTE
order by 1, 2


  Hi Mike, 

  I just ran it for a large set of records and it looks like the query is taking a lot of time. I did use the below at the end of my query but to no success.Please help.

  option (maxrecursion 0)

  Just to understand better have you used this option (maxrecursion 0) in your cte code?. And after adding that you experienced that queries take a lot of time to complete/never complete?

  If yes then,it could be a condition which does an infinite loop in your CTE code.

  what happens when you set maxrecursion to 5 or a lower number

  Thanks
  George

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 11, 2018 at 10:25 am

  #1997157

  george_at_sql - Wednesday, July 11, 2018 10:16 AM

  sanket.wagh7689 - Monday, July 9, 2018 3:52 PM

  Mike01 - Friday, July 6, 2018 10:01 AM

  Here's a recursive CTE that returns the values you want.  Seems you wants the contiguous keys and ignore all the noise in between

  
Drop table if exists #dimP
go

  CREATE TABLE #dimP (
  pcode char(4),
  pkey int,
  RowEffectiveDate datetime,
  rowenddate datetime
);
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
VALUES
     ('PL56',8855,'1900-01-01 00:00:00.000','2018-02-13 23:59:59.997'),
     ('PL56',8856,'2018-02-09 15:09:52.000','2018-02-14 23:59:59.997'),
     ('PL56',8903,'2018-02-09 15:09:52.000','2018-02-15 23:59:59.997'),
     ('PL56',8904,'2018-02-09 15:09:52.000','2018-02-16 23:59:59.997'),
     ('PL56',8935,'2018-02-14 00:00:00.000','2018-02-17 23:59:59.997'),
     ('PL56',8944,'2018-02-15 00:00:00.000','2018-02-18 23:59:59.997'),
     ('PL56',8955,'2018-02-16 00:00:00.000','2018-03-02 23:59:59.997'),
     ('PL56',8965,'2018-02-17 00:00:00.000','2018-03-03 23:59:59.997'),
     ('PL56',8972,'2018-02-18 00:00:00.000','2018-03-04 23:59:59.997'),
     ('PL56',8973,'2018-02-19 00:00:00.000','2018-03-05 23:59:59.997'),
     ('PL56',9114,'2018-03-03 00:00:00.000','2018-04-24 23:59:59.997'),
     ('PL56',9115,'2018-03-04 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',9136,'2018-03-05 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',9140,'2018-03-06 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11597,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',11820,'2018-04-26 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',11837,'2018-04-27 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11839,'2018-04-30 00:00:00.000','2018-05-22 23:59:59.997'),
     ('PL56',12372,'2018-05-23 00:00:00.000','9999-12-31 00:00:00.000');
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  VALUES  ('KO18', 502 , '1900-01-01 00:00:00.000', '2017-12-06 23:59:59.997'),
    ('KO18', 5431 , '2017-12-07 00:00:00.000', '2018-01-29 23:59:59.997'),
    ('KO18', 6852 , '2018-01-30 00:00:00.000', '2018-02-09 23:59:59.997'),
    ('KO18', 8861 , '2018-02-10 00:00:00.000', '2018-04-24 23:59:59.997'),
    ('KO18', 8862 , '2018-02-10 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11764, '2018-04-25 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11797, '2018-04-26 00:00:00.000', '9999-12-31 00:00:00.000');

  /*
SELECT *, ROW_NUMBER() OVER(PARTITION BY RowEffectiveDate ORDER BY rowenddate, PKey) AS rn
FROM #dimP
order by 1, 2
*/

  ;WITH CTE AS (
select pcode,pkey, RowEffectiveDate, rowenddate,RowEffectiveDateCalc,RowEndDateCalc
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 #dimP
--         where PCode = 'PL56'
        ) X
    where rn = 1
union all
    SELECT NextRow.PCode, NextRow.Pkey, NextRow.RowEffectiveDate, NextRow.rowenddate
            ,cast(NextRow.RowEffectiveDate as date) RowEffectiveDateCalc
                    ,cast(NextRow.rowenddate as date) RowEndDateCalc
    FROM CTE CurRow
        join #dimP NextRow
        on CurRow.PCode = NextRow.PCode
        and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
)

  select * from CTE
order by 1, 2


  Hi Mike, 

  I just ran it for a large set of records and it looks like the query is taking a lot of time. I did use the below at the end of my query but to no success.Please help.

  option (maxrecursion 0)

  Just to understand better have you used this option (maxrecursion 0) in your cte code?. And after adding that you experienced that queries take a lot of time to complete/never complete?

  If yes then,it could be a condition which does an infinite loop in your CTE code.

  what happens when you set maxrecursion to 5 or a lower number

  Thanks
  George

  The query runs in an infinite loop when i run using option (maxrecursion 0) and when i use option (maxrecursion 5) or option (maxrecursion 32767) it gives the below error:

  Msg 530, Level 16, State 1, Line 2
  The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  July 11, 2018 at 11:25 am

  #1997168

  sanket.wagh7689 - Wednesday, July 11, 2018 10:25 AM

  george_at_sql - Wednesday, July 11, 2018 10:16 AM

  sanket.wagh7689 - Monday, July 9, 2018 3:52 PM

  Mike01 - Friday, July 6, 2018 10:01 AM

  Here's a recursive CTE that returns the values you want.  Seems you wants the contiguous keys and ignore all the noise in between

  
Drop table if exists #dimP
go

  CREATE TABLE #dimP (
  pcode char(4),
  pkey int,
  RowEffectiveDate datetime,
  rowenddate datetime
);
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
VALUES
     ('PL56',8855,'1900-01-01 00:00:00.000','2018-02-13 23:59:59.997'),
     ('PL56',8856,'2018-02-09 15:09:52.000','2018-02-14 23:59:59.997'),
     ('PL56',8903,'2018-02-09 15:09:52.000','2018-02-15 23:59:59.997'),
     ('PL56',8904,'2018-02-09 15:09:52.000','2018-02-16 23:59:59.997'),
     ('PL56',8935,'2018-02-14 00:00:00.000','2018-02-17 23:59:59.997'),
     ('PL56',8944,'2018-02-15 00:00:00.000','2018-02-18 23:59:59.997'),
     ('PL56',8955,'2018-02-16 00:00:00.000','2018-03-02 23:59:59.997'),
     ('PL56',8965,'2018-02-17 00:00:00.000','2018-03-03 23:59:59.997'),
     ('PL56',8972,'2018-02-18 00:00:00.000','2018-03-04 23:59:59.997'),
     ('PL56',8973,'2018-02-19 00:00:00.000','2018-03-05 23:59:59.997'),
     ('PL56',9114,'2018-03-03 00:00:00.000','2018-04-24 23:59:59.997'),
     ('PL56',9115,'2018-03-04 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',9136,'2018-03-05 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',9140,'2018-03-06 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11597,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',11820,'2018-04-26 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',11837,'2018-04-27 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11839,'2018-04-30 00:00:00.000','2018-05-22 23:59:59.997'),
     ('PL56',12372,'2018-05-23 00:00:00.000','9999-12-31 00:00:00.000');
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  VALUES  ('KO18', 502 , '1900-01-01 00:00:00.000', '2017-12-06 23:59:59.997'),
    ('KO18', 5431 , '2017-12-07 00:00:00.000', '2018-01-29 23:59:59.997'),
    ('KO18', 6852 , '2018-01-30 00:00:00.000', '2018-02-09 23:59:59.997'),
    ('KO18', 8861 , '2018-02-10 00:00:00.000', '2018-04-24 23:59:59.997'),
    ('KO18', 8862 , '2018-02-10 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11764, '2018-04-25 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11797, '2018-04-26 00:00:00.000', '9999-12-31 00:00:00.000');

  /*
SELECT *, ROW_NUMBER() OVER(PARTITION BY RowEffectiveDate ORDER BY rowenddate, PKey) AS rn
FROM #dimP
order by 1, 2
*/

  ;WITH CTE AS (
select pcode,pkey, RowEffectiveDate, rowenddate,RowEffectiveDateCalc,RowEndDateCalc
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 #dimP
--         where PCode = 'PL56'
        ) X
    where rn = 1
union all
    SELECT NextRow.PCode, NextRow.Pkey, NextRow.RowEffectiveDate, NextRow.rowenddate
            ,cast(NextRow.RowEffectiveDate as date) RowEffectiveDateCalc
                    ,cast(NextRow.rowenddate as date) RowEndDateCalc
    FROM CTE CurRow
        join #dimP NextRow
        on CurRow.PCode = NextRow.PCode
        and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
)

  select * from CTE
order by 1, 2


  Hi Mike, 

  I just ran it for a large set of records and it looks like the query is taking a lot of time. I did use the below at the end of my query but to no success.Please help.

  option (maxrecursion 0)

  Just to understand better have you used this option (maxrecursion 0) in your cte code?. And after adding that you experienced that queries take a lot of time to complete/never complete?

  If yes then,it could be a condition which does an infinite loop in your CTE code.

  what happens when you set maxrecursion to 5 or a lower number

  Thanks
  George

  The query runs in an infinite loop when i run using option (maxrecursion 0) and when i use option (maxrecursion 5) or option (maxrecursion 32767) it gives the below error:

  Msg 530, Level 16, State 1, Line 2
  The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

  So there is atleast one pCode whose condition falls into a infinite loop. Can you provide the records of the pCode and the corresponding insert statements on which it is stuck.

• sanket.wagh7689

  SSC-Addicted

  Points: 461

  More actions

  July 11, 2018 at 1:00 pm

  #1997191

  george_at_sql - Wednesday, July 11, 2018 11:25 AM

  sanket.wagh7689 - Wednesday, July 11, 2018 10:25 AM

  george_at_sql - Wednesday, July 11, 2018 10:16 AM

  sanket.wagh7689 - Monday, July 9, 2018 3:52 PM

  Mike01 - Friday, July 6, 2018 10:01 AM

  Here's a recursive CTE that returns the values you want.  Seems you wants the contiguous keys and ignore all the noise in between

  
Drop table if exists #dimP
go

  CREATE TABLE #dimP (
  pcode char(4),
  pkey int,
  RowEffectiveDate datetime,
  rowenddate datetime
);
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
VALUES
     ('PL56',8855,'1900-01-01 00:00:00.000','2018-02-13 23:59:59.997'),
     ('PL56',8856,'2018-02-09 15:09:52.000','2018-02-14 23:59:59.997'),
     ('PL56',8903,'2018-02-09 15:09:52.000','2018-02-15 23:59:59.997'),
     ('PL56',8904,'2018-02-09 15:09:52.000','2018-02-16 23:59:59.997'),
     ('PL56',8935,'2018-02-14 00:00:00.000','2018-02-17 23:59:59.997'),
     ('PL56',8944,'2018-02-15 00:00:00.000','2018-02-18 23:59:59.997'),
     ('PL56',8955,'2018-02-16 00:00:00.000','2018-03-02 23:59:59.997'),
     ('PL56',8965,'2018-02-17 00:00:00.000','2018-03-03 23:59:59.997'),
     ('PL56',8972,'2018-02-18 00:00:00.000','2018-03-04 23:59:59.997'),
     ('PL56',8973,'2018-02-19 00:00:00.000','2018-03-05 23:59:59.997'),
     ('PL56',9114,'2018-03-03 00:00:00.000','2018-04-24 23:59:59.997'),
     ('PL56',9115,'2018-03-04 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',9136,'2018-03-05 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',9140,'2018-03-06 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11597,'2018-04-25 00:00:00.000','2018-04-25 23:59:59.997'),
     ('PL56',11820,'2018-04-26 00:00:00.000','2018-04-26 23:59:59.997'),
     ('PL56',11837,'2018-04-27 00:00:00.000','2018-04-29 23:59:59.997'),
     ('PL56',11839,'2018-04-30 00:00:00.000','2018-05-22 23:59:59.997'),
     ('PL56',12372,'2018-05-23 00:00:00.000','9999-12-31 00:00:00.000');
INSERT INTO #dimP (pcode, pkey, RowEffectiveDate, rowenddate)
  VALUES  ('KO18', 502 , '1900-01-01 00:00:00.000', '2017-12-06 23:59:59.997'),
    ('KO18', 5431 , '2017-12-07 00:00:00.000', '2018-01-29 23:59:59.997'),
    ('KO18', 6852 , '2018-01-30 00:00:00.000', '2018-02-09 23:59:59.997'),
    ('KO18', 8861 , '2018-02-10 00:00:00.000', '2018-04-24 23:59:59.997'),
    ('KO18', 8862 , '2018-02-10 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11764, '2018-04-25 00:00:00.000', '2018-04-25 23:59:59.997'),
    ('KO18', 11797, '2018-04-26 00:00:00.000', '9999-12-31 00:00:00.000');

  /*
SELECT *, ROW_NUMBER() OVER(PARTITION BY RowEffectiveDate ORDER BY rowenddate, PKey) AS rn
FROM #dimP
order by 1, 2
*/

  ;WITH CTE AS (
select pcode,pkey, RowEffectiveDate, rowenddate,RowEffectiveDateCalc,RowEndDateCalc
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 #dimP
--         where PCode = 'PL56'
        ) X
    where rn = 1
union all
    SELECT NextRow.PCode, NextRow.Pkey, NextRow.RowEffectiveDate, NextRow.rowenddate
            ,cast(NextRow.RowEffectiveDate as date) RowEffectiveDateCalc
                    ,cast(NextRow.rowenddate as date) RowEndDateCalc
    FROM CTE CurRow
        join #dimP NextRow
        on CurRow.PCode = NextRow.PCode
        and DateDiff(day, CurRow.RowEndDateCalc, cast(NextRow.RowEffectiveDate as date)) = 1
)

  select * from CTE
order by 1, 2


  Hi Mike, 

  I just ran it for a large set of records and it looks like the query is taking a lot of time. I did use the below at the end of my query but to no success.Please help.

  option (maxrecursion 0)

  Just to understand better have you used this option (maxrecursion 0) in your cte code?. And after adding that you experienced that queries take a lot of time to complete/never complete?

  If yes then,it could be a condition which does an infinite loop in your CTE code.

  what happens when you set maxrecursion to 5 or a lower number

  Thanks
  George

  The query runs in an infinite loop when i run using option (maxrecursion 0) and when i use option (maxrecursion 5) or option (maxrecursion 32767) it gives the below error:

  Msg 530, Level 16, State 1, Line 2
  The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

  So there is atleast one pCode whose condition falls into a infinite loop. Can you provide the records of the pCode and the corresponding insert statements on which it is stuck.

  I have attached a file with insert statements for all records.    Pcode,Pkey,RowEffectivedate and RowEnddate.
  Unable to identify at which record it is failing.

• Mike01

  SSChampion

  Points: 11395

  More actions

  July 11, 2018 at 1:47 pm

  #1997210

  I don't see an attachment

  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/