Find date gaps

  • Hello everyone,

    I need to find a way to fill gaps in case a week is missing, example each record has Begin_Date and End_Date, and some records can have multiple Begin_Date and End_Date but they should be continuation of the previous week.

    Example :

    Row_Nbd Account_Nbr Account_Name Account_Desc Begin_Date End_Date

    1 1 test1 chk 1/1/2012 3/1/2012

    2 1 test1 chk 3/2/2012 6/30/2012

    3 1 test chk 8/2/2012 12/31/2012

    as you can see there us a missing date range right above row_nbr 3 which (7/1/2012 to 8/1/2012) how can find the missing week?

    Thanks

  • Would something like this get you started?

    create table #temp (

    Row_Ndb int,

    Account_Nbr int,

    Account_Name char(5),

    Account_Desc char(3),

    Begin_Date date,

    End_Date date

    )

    insert into #temp values

    (1, 1, 'test1', 'chk', '1/1/2012', '3/1/2012'),

    (2, 1, 'test1', 'chk', '3/2/2012', '6/30/2012'),

    (3, 1, 'test' , 'chk', '8/2/2012', '12/31/2012')

    with cte

    as

    (

    select row_ndb

    , account_nbr

    , account_name

    , Account_desc

    , Begin_date

    , End_date

    , row_number() over (partition by Account_Nbr order by begin_date) rownum

    from #temp

    )

    select *

    from

    (

    select cte1.*

    , case when datediff(d,cte2.End_date,cte1.begin_date) > 2 then datediff(d,cte2.End_date,cte1.begin_date) else 0 end as DaysMissing

    , case when datediff(d,cte2.End_date,cte1.begin_date) > 2 then dateadd(d,1,cte2.end_date) else null end as MissingBegin_Date

    , case when datediff(d,cte2.End_date,cte1.begin_date) > 2 then dateadd(d,-1,cte1.Begin_date) else null end as MissingEnd_Date

    from cte cte1

    left join cte cte2

    on cte1.account_nbr = cte2.account_nbr and cte1.rownum = cte2.rownum + 1

    ) source

    where source.DaysMissing > 0

    drop table #temp

  • Thanks Erin, the code worked beautifully. I appreciate your help.

  • Glad I could help. 🙂

  • I am facing another challenge. I tried to make the code work with no success. the issue i am facing is, i need to fill gaps also,

    if the year is not completed example :

    ( 1, 1, 'test1', 'chk', '2/1/2012', '3/1/2012' ),

    ( 2, 1, 'test1', 'chk', '3/2/2012', '6/30/2012' ),

    ( 3, 1, 'test', 'chk', '8/2/2012', '12/01/2012' );

    the year should always start at 1.1.2012 and end 12.31.2012 and if there is a keep needs to be filled up, so in this scenario i am missing 1.1.2012 to 1.31.2012

    7.1.2012 to 8.1.2012 (Which already fixed thanks to Erin)

    12.2.2012 to 12.31.2012

  • I believe i have the code working now :

    but it just seems too long

    CREATE TABLE #temp

    (

    Row_Ndb INT ,

    Account_Nbr INT ,

    Account_Name CHAR(5) ,

    Account_Desc CHAR(3) ,

    Begin_Date DATE ,

    End_Date DATE

    )

    INSERT INTO #temp

    VALUES

    ( 1, 1, 'test1', 'chk', '1/1/2012', '3/1/2012' ),

    ( 2, 1, 'test1', 'chk', '3/2/2012', '6/30/2012' ),

    ( 3, 1, 'test1', 'chk', '7/11/2012', '7/22/2012' ),

    ( 3, 1, 'test1', 'chk', '8/1/2012', '10/11/2012' ),

    ( 4, 1, 'test1', 'chk', '10/19/2012', '1/1/2013' );

    WITH cte

    AS ( SELECT

    row_ndb ,

    account_nbr ,

    account_name ,

    Account_desc ,

    Begin_date ,

    End_date ,

    ROW_NUMBER() OVER ( PARTITION BY Account_Nbr ORDER BY begin_date ) rownum

    FROM

    #temp

    ),

    cte_Middle

    AS ( SELECT DISTINCT

    *

    FROM

    ( SELECT

    cte1.* ,

    CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1

    THEN DATEDIFF(d, cte2.End_date, cte1.begin_date)

    ELSE 0

    END AS DaysMissing ,

    CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1

    THEN DATEADD(d, 1, cte2.end_date)

    ELSE NULL

    END AS MissingBegin_Date ,

    CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1

    THEN DATEADD(d, -1, cte1.Begin_date)

    ELSE NULL

    END AS MissingEnd_Date

    FROM

    cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date

    LEFT JOIN cte cte2

    ON cte1.account_nbr = cte2.account_nbr

    AND cte1.rownum = cte2.rownum + 1

    ) source

    WHERE

    source.DaysMissing > 0

    ),

    Cte_Top

    AS ( SELECT DISTINCT

    *

    FROM

    ( SELECT

    cte1.* ,

    CASE WHEN DATEDIFF(d, '1/1/2012', Min_Begin_Date) > 1

    THEN DATEDIFF(d, '1/1/2012', Min_Begin_Date)

    ELSE 0

    END AS DaysMissing ,

    CASE WHEN Min_Begin_Date > '1/1/2012'

    AND Min_Begin_Date < Min_End_Date

    THEN '1/1/2012'

    ELSE NULL

    END AS MissingBegin_Date ,

    CASE WHEN DATEDIFF(d, '1/1/2012', Min_Begin_Date) > 1

    THEN DATEADD(d, -1, Min_Begin_Date)

    ELSE NULL

    END AS MissingEnd_Date

    -- ,

    --Min_Begin_Date ,

    --Min_End_Date

    FROM

    cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date

    CROSS APPLY ( SELECT TOP 1

    ( Begin_Date ) Min_Begin_Date ,

    ( End_Date ) Min_End_Date ,

    account_Name

    FROM

    cte c3

    WHERE

    cte1.account_nbr = c3.account_nbr

    ORDER BY

    begin_date ASC

    ) cte3

    LEFT JOIN cte cte2

    ON cte1.account_nbr = cte2.account_nbr

    AND cte1.rownum = cte2.rownum + 1

    WHERE

    Min_End_Date = cte1.End_Date

    ) source

    ),

    Cte_Button

    AS ( SELECT

    *

    FROM

    ( SELECT

    cte1.* ,

    CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1

    THEN DATEDIFF(d, Max_End_Date, '12/31/2012')

    ELSE 0

    END AS DaysMissing ,

    CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1

    --AND Min_Begin_Date < Min_End_Date

    THEN DATEADD(d, 1, Max_End_Date)

    ELSE NULL

    END AS MissingBegin_Date ,

    CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1

    THEN '12/31/2012'

    ELSE NULL

    END AS MissingEnd_Date

    -- ,

    --Min_Begin_Date ,

    --Min_End_Date

    FROM

    cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date

    CROSS APPLY ( SELECT TOP 1

    ( Begin_Date ) Max_Begin_Date ,

    ( End_Date ) Max_End_Date ,

    account_Name

    FROM

    cte c3

    WHERE

    cte1.account_nbr = c3.account_nbr

    ORDER BY

    begin_date DESC

    ) cte3

    LEFT JOIN cte cte2

    ON cte1.account_nbr = cte2.account_nbr

    AND cte1.rownum = cte2.rownum + 1

    WHERE

    Max_End_Date = cte1.End_Date

    ) source

    )

    SELECT

    *

    FROM

    cte_Middle

    WHERE DaysMissing > 0

    UNION

    SELECT

    *

    FROM

    Cte_Top

    where DaysMissing > 0

    UNION

    SELECT

    *

    FROM

    Cte_Button

    WHERE

    DaysMissing > 0

    DROP TABLE #temp

  • Sorry! Post withdrawn due to Homer Simpson moment.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply