Tricky query for excluding records related to existing records

[pdbowling]

Hello, Everyone.

I have a query that I do not have an idea of how to complete.

My simple join gives a recordset like this:

Recordset 1

p_key,   f_key,  my_type, start_date,  end_date,   name
000004,  45678,  ddd,     01/15/2013,  01/31/2013, John Doe
000005,  45678,  ddd,     02/01/2013,  03/01/2013, John Doe
000006,  56789,  eee,     02/01/2013,  03/01/2013, JQ Public
000007,  67890,  aaa,     02/01/2013,  03/01/2013, Jane Doe
000008,  34567,  ccc,     01/15/2013,  01/31/2013, Jim Test 
000009,  67890,  ccc,     03/02/2013,  04/01/2013, Jane Doe

About the data:
Looking at primary keys 000004 and 000005, we see that the foreign key is the same.

We also see that the end_date of 000004 is only one day removed from the start_date of 000005.

Goal:
At these points (similar in p_keys 000007 and 000009), I am being asked to make the next record not count (or to not show in the query).

Stating it another way:
If the f_key is the same and the end_date of the first record is 1 day or less removed from the start_date of te next record, do not show the second record.

Lastly, here is the desired result as another example.

Recordset 2 (desired)

p_key,   f_key,  my_type, start_date, name
000004,  45678,  ddd,     01/15/2013, John Doe   
000006,  56789,  eee,     02/01/2013, JQ Public
000007,  67890,  aaa,     02/01/2013, Jane Doe
000008,  34567,  ccc,     01/15/2013, Jim Test

About the data:
p_key 000005 dropped out since its start_date was 02/01/2013 and the end_date of 000004 was 01/31/2013.
p_key 000009 dropped out since its start_date was 03/02/2013 and the end_date of 000007 was 03/01/2013.

This is the simple join I am using to get the original dataset.

SELECT t1.p_key, t1.f_key, t1.my_type, t1.Start_date, t2.name
  FROM table1 AS t1
  JOIN table2 AS t2
    ON t1.f_key=t2.f_key

How would I work the SQL to get a result like Recordset 2?

Any pointers would be quite welcome.

Thank you.
Patrick

 

[imex]

Hi,

Try:

with CTE_R as
(
    SELECT 
        t1.p_key, 
        t1.f_key, 
        t1.my_type, 
        t1.Start_date, 
        t2.name.
        ROW_NUMBER() OVER(PARTITION BY t1.f_key ORDER BY t1.Start_date) as RowNum
    FROM table1 AS t1 
    JOIN table2 AS t2 
        ON t1.f_key = t2.f_key
)

select 
    t.* 
from CTE_R as t
left join CTE_R as p
    on p.f_key = t.f_key and
       p.RowNum = t.RowNum - 1
where 
    t.RowNum = 1 or
    DATEDIFF(DAY, p.Start_date, t.Start_date) > 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[pdbowling]

Well, spelling it all out for everyone helped my tear it apart in my head. I have it.

Thanks,
Patrick

SELECT t1.p_key, t1.my_type, t1.start_date,t1.end_date, t2.Name, t2.f_key
  FROM table1 AS t1
  JOIN table2 AS t2
    ON t2.f_key = t1.f_key
 WHERE t1.p_key NOT IN
(
SELECT t3.p_key
  FROM table1 AS t1
  JOIN table2 AS t2
    ON t2.f_key = t1.f_key
  JOIN table1 AS t3
    ON t3.f_key = t1.f_key
 WHERE t3.p_key <> t1.p_key
   AND ABS(DATEDIFF(HOUR,t3.start_date,t1.end_date)) < 25
)
 ORDER BY t2.name

 

[pdbowling]

Thank you, imex.

That is quite elegant.