Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Unanswered: SQL Query for missing data

    Hi experts,
    I have a table of following structure
    Time Node
    2012-06-15 12:00 Node1
    2012-06-15 12:00 Node2
    2012-06-15 12:00 Node3

    2012-06-15 12:15 Node1
    2012-06-15 12:15 Node2
    2012-06-15 12:15 Node4

    2012-06-15 12:30 Node2
    2012-06-15 12:30 Node3
    I need a query that will give me a list for which time period which node's data is missing
    The table should look like this
    2012-06-15 12:00 Node4
    2012-06-15 12:15 Node3
    2012-06-15 12:30 Node1
    2012-06-15 12:30 Node4

    please help

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

    Try something like that:

    Code:
    with
        CTE_T as
        (
            select distinct Time from MyTable
        ),
    
        CTE_N as
        (
            select distinct Node from MyTable
        )
    
    select t.Time, n.Node 
    from CTE_N as n
    cross join CTE_T as t
    
    except
    
    select Time, Node
    from MyTable
    Hope this helps.

Tags for this Thread

Posting Permissions

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