Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    6

    Unanswered: Comparing Date Records in one Column to know missing date interval

    Hi! I am new here. Just want to ask for help about one of my queries. I need to compare the contents of a column.

    I have a table with 1 column. Below are the sample contents..

    DateInterval
    2012-06-01 00:30:00.000
    2012-06-01 01:00:00.000
    2012-06-01 01:30:00.000
    2012-06-01 02:00:00.000
    2012-06-01 02:30:00.000
    2012-06-01 03:00:00.000
    2012-06-01 03:30:00.000
    2012-06-01 04:00:00.000
    2012-06-01 04:30:00.000
    2012-06-01 05:00:00.000
    2012-06-01 05:30:00.000
    2012-06-01 06:00:00.000
    2012-06-01 06:30:00.000
    2012-06-01 07:00:00.000
    2012-06-01 07:30:00.000
    2012-06-01 08:00:00.000
    2012-06-01 08:30:00.000
    2012-06-01 09:00:00.000
    2012-06-01 09:30:00.000
    2012-06-01 10:00:00.000
    2012-06-01 10:30:00.000
    2012-06-01 11:00:00.000
    2012-06-01 11:30:00.000
    2012-06-01 12:00:00.000
    2012-06-01 12:30:00.000
    2012-06-01 13:00:00.000
    2012-06-01 13:30:00.000
    2012-06-01 14:00:00.000
    2012-06-01 14:30:00.000
    2012-06-01 15:00:00.000
    2012-06-01 15:30:00.000
    2012-06-01 16:00:00.000
    2012-06-01 16:30:00.000
    2012-06-01 17:00:00.000
    2012-06-01 17:30:00.000
    2012-06-01 18:30:00.000
    2012-06-01 19:00:00.000
    2012-06-01 19:30:00.000
    2012-06-01 20:00:00.000
    2012-06-01 21:00:00.000
    2012-06-01 21:30:00.000
    2012-06-01 22:00:00.000
    2012-06-01 22:30:00.000
    2012-06-01 23:00:00.000
    2012-06-01 23:30:00.000

    as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:

    DateInterval
    2012-06-01 18:00:00.000
    2012-06-01 20:30:00.000

    if it is not doable, we can get the nearest record of all missing records like this:

    DateInterval
    2012-06-01 17:30:00.000
    2012-06-01 20:00:00.000

    or this:

    DateInterval
    2012-06-01 18:30:00.000
    2012-06-01 21:00:00.000

    or this:

    DateInterval
    2012-06-01 17:30:00.000
    2012-06-01 18:30:00.000
    2012-06-01 20:00:00.000
    2012-06-01 21:00:00.000

    Thank you very much!

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try something like that:

    Code:
    with 
        CTE_L as
        (
            select top 1 DateInterval from MyTable order by DateInterval desc
        ),
        
        CTE_I as
        (
            select top 1 DateInterval from MyTable
        
            union all
        
            select DATEADD(minute, 30, DateInterval) as DateInterval
            from CTE_I
            where DateInterval < (select DateInterval from CTE_L)
        )
    
    select i.DateInterval
    from CTE_I as i
    left join MyTable as t on t.DateInterval = i.DateInterval
    where t.DateInterval is null
    Hope this helps.

  3. #3
    Join Date
    Jun 2012
    Posts
    6
    Thanks for the reply! but it has an error:

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

    EDIT: i think that if there are more than 100 records it will give you the error above.
    Last edited by jetaw03; 06-06-12 at 10:22.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with 
        CTE_F as
        (
            select top 1 DateInterval from MyTable
        ),
        
        CTE_L as 
        (
            select top 1 DateInterval from MyTable order by DateInterval desc
        ),
        
        CTE_I as
        (
            select DATEADD(MINUTE, 30 * a.number, f.DateInterval) as DateInterval
            FROM master.dbo.spt_values AS a 
            cross join CTE_F as f
            where (a.number > 0) and 
                  (a.type = 'P') and
                  (DATEADD(MINUTE, 30 * a.number, f.DateInterval) < 
                                                      (select DateInterval from CTE_L))
        )
        
    select i.DateInterval 
    from CTE_I as i
    left join MyTable as t on t.DateInterval = i.DateInterval
    where t.DateInterval is null
    Hope this helps.

  5. #5
    Join Date
    Jun 2012
    Posts
    6
    it worked! thanks a lot! i really want to learn how did you do that. can you give me some comments on the following parts in that code:

    the "with" part,
    the cross join,
    the use of master.dbo.spt_values,
    and the "DATEADD(MINUTE, 30 * a.number, f.DateInterval" part

    thank you very much! sorry for the trouble..

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    See:

    1) Using Common Table Expressions
    2) Using Cross Joins
    3) try: select a.number FROM master.dbo.spt_values AS a where (a.number > 0) and (a.type = 'P')
    4) DATEADD (Transact-SQL)

    Hope this helps.

  7. #7
    Join Date
    Jun 2012
    Posts
    6
    thanks for the reply!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •