To find from a table if there is any nearer date values for the same key

Question

I have an sql table (t_accountdetails) with an account column called AccountId and effective date column for that account. An account can have multiple effective date. I have a requirement to get all the entries for the accounts which has very close effective date entries.(an offset of +/-14 days)

Say for eg:

AccountId: 12345 has got 2 entries with effective date 12/11/2017 and 12/18/2017

So my query should return above case where we have an entry of effective dates within offset of +/-14days

Please note I am actually not looking for date +-14 from today. I am looking for effective date which +/- 14 days of another effective date for the same account

Duplicated question. Please check – tung yu Jun 11 '18 at 08:46 — tung yu, Jun 11 '18 at 08:46

score 1 · Answer 1 · answered Jun 11 '18 at 08:41

1

You can use the DATEADD function to make it work

select * from t_accountdetails where AccountId = 12345 and effectiveDate >= DATEADD(day, -14, getdate()) and effectiveDate <= DATEADD(day, 14, getdate())

This will return all records with AccountID = 12345 and an effective date between today - 14 days and today + 14 days. Note: if more than one record match the criteria then all matching records will be returned.

answered Jun 11 '18 at 08:41

mboldt

1,780
11
15

I am actually not looking for date +-14 from today. I am looking for effective date which +/- 14 days of another effective date for the same account – user2081126 Jun 11 '18 at 09:02
My bad, I understood the question incorrectly then. The answer from Thorsten Kettner should be doing what you are looking for. – mboldt Jun 11 '18 at 09:13

score 1 · Accepted Answer · answered Jun 11 '18 at 08:47

1

You want all records where exists another effective date within 14 days, so use WHERE EXISTS:

select *
from t_accountdetails t
where exists
(
  select *
  from t_accountdetails other
  where other.accountid = t.accountid
  and other.id <> t.id
  and abs(datediff(day, other.effective_date, t.effective_date)) <= 14
)
order by accountid, effective_date;

answered Jun 11 '18 at 08:47

Thorsten Kettner

89,309
7
49
73

I am actually not looking for date +-14 from today. I am looking for effective date which +/- 14 days of another effective date for the same account – user2081126 Jun 11 '18 at 09:02
1

Yes, and this is what my query does. Why do you think it deals with today? It compares one record's date (`t.effective_date`) with another record's date (`other.effective_date`). – Thorsten Kettner Jun 11 '18 at 09:07
I don't have a field called id in my table. ur query has other.id <> t.id – user2081126 Jun 11 '18 at 09:12
the query above returns raw if effective date is 01/01/2012 and 01/01/2013 which is not within +/-14 days – user2081126 Jun 11 '18 at 09:16
1

I am using `id` as a record's identifier in order to only look at *other* records and not compare a record with itself. Replace this by the table's ID. If there is none, you could use `effective_date`, but would no longer detect two records with the same `effective_date` (if that is even possible). – Thorsten Kettner Jun 11 '18 at 09:21
As to 01/01/2012 and 01/01/2013: I guess you have mutilated the query. Removed the ID condition mybe? – Thorsten Kettner Jun 11 '18 at 09:22

score 0 · Answer 3 · answered Jun 11 '18 at 10:55

0

I would be inclined to use lag() and lead():

select ad.*
from (select ad.*,
             lag(effective_date) over (partition by accountid order by effective_date) as prev_ed,
             lead(effective_date) over (partition by accountid order by effective_date) as next_ed
      from t_accountdetails ad
     ) ad
where effective_date <= dateadd(day, 14, prev_ed) or
      effective_date >= dateadd(day, -14, next_ed);

It would be interesting to compare the performance of this version to the exists version with an index on t_accountdetails(accountid, effective_date).

answered Jun 11 '18 at 10:55

Gordon Linoff

1,242,037
58
646
786

the query above returns raw if effective date is 01/01/2012 and 01/01/2013 which is not within +/-14 days – user2081126 Jun 11 '18 at 12:11
@user2081126 . . . Without sample data, I'm at something of a disadvantage. But this should only be returning rows where another row is within 14 days in either direction. – Gordon Linoff Jun 11 '18 at 12:19

To find from a table if there is any nearer date values for the same key

3 Answers3