SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Returning Records within Days Range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Grifter
Constraint Violating Yak Guru

264 Posts

Posted - 10/31/2013 :  05:45:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/31/2013 :  06:15:02  Show Profile  Reply with Quote

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

Edited by - visakh16 on 10/31/2013 06:17:31
Go to Top of Page

Grifter
Constraint Violating Yak Guru

264 Posts

Posted - 10/31/2013 :  07:37:18  Show Profile  Reply with Quote
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 ')'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/31/2013 :  08:25:50  Show Profile  Reply with Quote
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
Go to Top of Page

Grifter
Constraint Violating Yak Guru

264 Posts

Posted - 10/31/2013 :  09:12:01  Show Profile  Reply with Quote
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












Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000