locked
Date question - SQL QUERY RRS feed

  • Question

  • Hello,

    USE [Practice]
    GO
    
    IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
    	DROP TABLE #TEMP
    
    CREATE TABLE #TEMP
    (
    	ID			INT,
    	START_DT	DATETIME,
    	ENT_DT		DATETIME
    )
    
    INSERT INTO #TEMP VALUES ( 580101012, '1996-10-01 00:00:00.000', '2003-03-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2003-04-01 00:00:00.000', '2007-07-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2007-08-01 00:00:00.000', '2010-10-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2014-03-01 00:00:00.000', '2016-12-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2017-01-01 00:00:00.000', '9999-12-31 00:00:00.000' )
    
    SELECT	ID, START_DT, ENT_DT, ROW_NUMBER() OVER(ORDER BY START_DT) AS Rw
    FROM	#TEMP
    
    IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
    	DROP TABLE #TEMP


    Expected result:-

    SELECT	'580101012' AS ID, '1996-10-01 00:00:00.000' AS START_DT, '2010-10-31 00:00:00.000' AS END_DT
    UNION ALL
    SELECT	'580101012', '2014-03-01 00:00:00.000', '9999-12-31 00:00:00.000'

    Thanks in advance

    Regards,

    gk03


    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, October 20, 2016 7:32 PM

All replies

  • Not clear to what you want.

    A Fan of SSIS, SSRS and SSAS

    Thursday, October 20, 2016 8:29 PM
  • END_DT if equal to next record START_DT + 1 then print 2nd record END_DT

    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, October 20, 2016 9:06 PM
  • IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
    	DROP TABLE #TEMP
    
    CREATE TABLE #TEMP
    (
    	ID			INT,
    	START_DT	DATETIME,
    	END_DT		DATETIME
    )
    
    INSERT INTO #TEMP VALUES ( 580101012, '1996-10-01 00:00:00.000', '2003-03-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2003-04-01 00:00:00.000', '2007-07-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2007-08-01 00:00:00.000', '2010-10-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2014-03-01 00:00:00.000', '2016-12-31 00:00:00.000' )
    INSERT INTO #TEMP VALUES ( 580101012, '2017-01-01 00:00:00.000', '9999-12-31 00:00:00.000' )
    
    SELECT	ID, START_DT, END_DT, ROW_NUMBER() OVER(ORDER BY START_DT) AS Rw
    FROM	#TEMP
    
    
    DECLARE @minstart_dt DATETIME  
    SELECT @minstart_dt = MIN(start_dt)   FROM #temp;
     
     
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),  
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num3)
     
    ,myDateCTE as
    (
    select dateadd(day,n-1,(select min(start_dt) from #temp)) dt 
    from Nums
    WHERE  n<=36500)
     
     
    , mycte as (
    select dt, ID, row_number() Over(Partition by ID order by dt) rn1 from
    (select distinct ID from #temp) t1, myDateCTE  
    )
     
    ,mycte1 as (
    SELECT ID, dt, rn1, row_number() Over(Partition by ID order by dt) rn2
       FROM mycte t1
       WHERE  EXISTS 
         (SELECT * FROM #temp t2 
           WHERE t1.dt BETWEEN t2.start_dt AND t2.end_dt and t1.ID=t2.ID)
     
           )
     
     
    Select ID, Min(m.dt) As start_dt
    , Case when dateadd(day,36500-1,@minstart_dt)=Max(m.dt) then  '9999-12-31 00:00:00.000' Else  Max(m.dt) End As end_dt
    From mycte1 m
    Group By ID, m.rn1 - m.rn2
     
      
    
    SELECT	'580101012' AS ID, '1996-10-01 00:00:00.000' AS START_DT, '2010-10-31 00:00:00.000' AS END_DT
    UNION ALL
    SELECT	'580101012', '2014-03-01 00:00:00.000', '9999-12-31 00:00:00.000'
    
    IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
    	DROP TABLE #TEMP

    • Proposed as answer by Kalman Toth Thursday, October 20, 2016 9:12 PM
    Thursday, October 20, 2016 9:07 PM
  • END_DT if equal to next record START_DT + 1 then print 2nd record END_DT

    Please do let us know your feedback. Thank You - KG, MCTS

    START_DT = '1996-10-01 00:00:00.000' and ENT_DT = '2003-03-31 00:00:00.000'

    Print ENT_DT = '2010-10-31 00:00:00.000', not '2007-07-31 00:00:00.000'?

    But why

    START_DT = '2014-03-01 00:00:00.000'and ENT_DT = '2016-12-31 00:00:00.000'

    PRINT ENT_DT = '9999-12-31 00:00:00.000'?


    A Fan of SSIS, SSRS and SSAS

    Thursday, October 20, 2016 9:34 PM
  • An alternate way

    with tord as (
      select id, start_dt, ent_dt, row_number() over (partition by id order by start_dt) as rn -- *, iif(datediff(day, lag(ent_dt) over (partition by id order by start_dt), start_dt)=1, 0, 1), lag(ent_dt) over (partition by id order by start_dt) as num
      from #TEMP
    ),
    loops as
    (
      select t.id, start_dt, ent_dt, t.rn, 1 as grp
      from tord t where rn = 1
      union all
      select t1.id, t1.start_dt, t1.ent_dt, t1.rn, iif(datediff(day, t.ent_dt, t1.start_dt)=1, 0, 1)+grp
      from tord t1 inner join loops t on t.id = t1.id and t.rn+1 = t1.rn 
    )
    select id, min(start_dt), max(ent_dt) from loops
    group by id, grp

    Tuesday, October 25, 2016 1:15 AM