none
Check gaps between dates RRS feed

  • Question

  • Hi,

    I would like to write a custom query to see if the date is continuous across rows or not. My data looks like below - 

    ID JobDate
    1   20190621
    2   20190620
    3   20190221
    4   20181113
    5   20181108

    As you see above the 1st and 2nd date is continuous and 3rd is not.

    Wednesday, June 26, 2019 7:50 AM

All replies

  • create table #t (id int, dt int)

    insert into #t values (1,20190620)
    insert into #t values (2,20190621)
    insert into #t values (3,20190221)
    insert into #t values (4,20181113)
    insert into #t values (5,20181108)

    select n as startgap, nextn as endgap
    from (select dt n,
            (select min(dt)
             from #t as b
             where b.dt > a.dt) as nextn
          from #t as a) as d
    where nextn - n > 1

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, June 26, 2019 7:58 AM
    Answerer
  • Can you show sample expected output?
    Wednesday, June 26, 2019 11:58 AM
  • Hi Srisaravanan.a,

    Here's the code you can refer to:

    IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
    GO
    create table table1 (id int, JobDate date)
    
    insert into table1 values 
    (1,'2019-06-20'),
    (2,'2019-06-21'),
    (3,'2019-02-21'),
    (4,'2018-11-13'),
    (5,'2018-11-08')
    
    
    SELECT *,
    case when lead(JobDate)over(order by id)=DATEADD(day, 1, JobDate) 
    then 'true' else 'false' end as [outcome]
    from table1
    
    /*
    id          JobDate    outcome
    ----------- ---------- -------
    1           2019-06-20 true
    2           2019-06-21 false
    3           2019-02-21 false
    4           2018-11-13 false
    5           2018-11-08 false
    */

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 27, 2019 3:14 AM
  • Hi,

    drop table #job
    create table #job (id integer, jobDate date)
    insert #job values (1,   '20190621'),
    (2,   '20190620'),
    (3,   '20190619'),
    (4,   '20181113'),
    (5,   '20181108')


    select id, jobDate
    from
    (select *,abs(datediff(day,lag(jobDate) over (order by id),jobDate)) d
    from #job) j
    where j.d = 1
    or (j.id = 1 and exists (select 1 from (select *,abs(datediff(day,lag(jobDate) over (order by id),jobDate)) d  /* special case for first record */
                             from #job) e
    where e.id = 2 and e.d = 1)) 

    Mark as answer if it helps. Thanks.


    Thursday, June 27, 2019 5:21 AM
  • Hi,

    I would like to write a custom query to see if the date is continuous across rows or not. My data looks like below - 

    ID JobDate
    1   20190621
    2   20190620
    3   20190221
    4   20181113
    5   20181108

    As you see above the 1st and 2nd date is continuous and 3rd is not.

    so what should be your output?

    Just give missing dates?

    Or indicates dates where there're gaps?

    Please illustrate with sample output for the given data above


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, June 27, 2019 5:27 AM
  • Hi Srisaravanan.a,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 15, 2019 2:09 AM