Returning Records within Days Range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning Records within Days Range

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-31 : 05:45:59

Hi

This is a follow on from one of my earlier threads where I was trying to return one specific record. In this case I am trying to return multiple records for the same person ID and within a number of days range.

Snapshot of my date with same PersonID:


PersonID  Arrival_Date  Leaving_Date ArrivalID
========  ============  ============ =========
123456    01/12/2012    01/12/2013   arr_56464
123456    10/12/2012    10/12/2013   arr_56474
123456    13/12/2012    13/12/2013   arr_56494

And from this I want to check if one record's leaving date of the record is within 7 days of another record's arrival date. I also want to return the record that had a leaving date within 7 days of the next arrival date.

I understand that if I self join on personID with the data above I will get 9 rows, for each row I will get 3 matches, I am using INNER JOIN to join to the same table but with a different alias so I assume this is the self join I should be using.

Simple code
============

But then how do I process this? I would want to say for record 1 check the leaving date is within 7 days of arrival date of any other record matching that PersonId but not ArrivalID, and return both records.

From my snapahot of code I would eventually want to return:


PersonID  Arrival_Date  Leaving_Date ArrivalID
========  ============  ============ =========
123456    10/12/2012    10/12/2013   arr_56474
123456    13/12/2012    13/12/2013   arr_56494

But can't seem to get this using a self join query like this:


select a.PersonID, a.Leaving_date,a.Arrival_Date,a.arrivalID
from arrivals a INNER JOIN arrivals  b
ON b.personID = a.personID
WHERE 
a.arrivalid != b.arrivalid
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) <=7
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) >=0

If any clarification as to what I want to see is needed please ask.

Thanks

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 06:15:02

[code]
SELECT t.*
FROM Table t
WHERE EXISTS (SELECT 1
FROM table
WHERE PersonID = t.PersonID
AND (ABS(DATEDIFF(dd,Arrival_date,t.Leaving_date))<= 7
OR ABS(DATEDIFF(dd,t.Arrival_date,Leaving_date)))<= 7
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-31 : 07:37:18

quote:
Originally posted by visakh16

SELECT t.*
FROM Table t
WHERE EXISTS (SELECT 1
FROM table
WHERE PersonID = t.PersonID
AND (ABS(DATEDIFF(dd,Arrival_date,t.Leaving_date))<= 7
OR ABS(DATEDIFF(dd,t.Arrival_date,Leaving_date)))<= 7
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Get this error on bottom line:

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 08:25:50

that was a typo


SELECT t.*
FROM Table t
WHERE EXISTS (SELECT 1
             FROM table
             WHERE PersonID  = t.PersonID  
             AND (ABS(DATEDIFF(dd,Arrival_date,t.Leaving_date))<= 7
             OR ABS(DATEDIFF(dd,t.Arrival_date,Leaving_date)))<= 7)
            )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-31 : 09:12:01

This still shows all the rows associated with a certain ID not just the arrival within 7 days and the associated record that it was compared to. For instance:

Arrival Date Leaving Date PatientID
============ ============ ==========
2012-10-12 2012-10-12 0103556675
2012-10-11 2012-10-11 0103556675
2012-09-28 2012-09-28 0103556675

Here I would only want to return:

Arrival Date Leaving Date PatientID
============ ============ ==========
2012-10-12 2012-10-12 0103556675
2012-10-11 2012-10-11 0103556675

Because row2 leaving date is within 7 days of the next arrival date on row 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources