Inline view to find Arrivals within days period

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

Inline view to find Arrivals within days period

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-04 : 09:27:19

I have this query:


SELECT a.* 
FROM Unscheduled_Arrival a INNER JOIN (SELECT Person_ID
				      ,[arrival date]
				      ,[leaving date]
				      ,[unique stay id]
				      ,[area code]
                                       FROM Unscheduled_Arrival
				       WHERE [area code] != 'AREA_GH'
				       and [leaving date] is not null) b 
ON a.Person_ID = b.Person_ID
WHERE a.[area code] = 'AREA_GH'
and a.[arrival date] >= b.[leaving date]   
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) <=7
and DATEDIFF(DD, b.[leaving date], a.[arrival date]) >=0
order by a.Person_ID

And what I am trying to do is find instances where a person has arrived back at a different area within 7 days of leaving from a planned visit to any other area different from the last. so lets say we had person ID = 1 and they had had 3 planned visits to one area (not equal to area_gh) in the last 4 months but after the 2nd planned trip to the other area, and before the commencement of the 3rd planned trip to any area not equal to area_gh, they had arrived unscheduled to area_gh, and this unplanned visit was within 7 days of leaving the 2nd last planned visit to any area not equal to area_gh.

The query I have shows data from unscheduled arrivals to the area_gh for a person who has also had visits to any area other than area_gh, but each record for unscheduled visits to area_gh duplicates for the amount of times the person has had scheduled visits to any area not equal to area_gh. This is the first problem but is as a result of not having the correct SQL for the refinement of the data.

This refinement requires that I return only the data that relates to the unscheduled arrival that is within 7 days of the last scheduled arrival, not duplicate rows of area_gh visits for all the scheduled arrivals.

Because my data is showing the data I want but the count is related to the scheduled arrivals I think I need to rewrite the query or alter it.

The data is already restricted to a year's worth of data.

I just need to find any instances of unscheduled arrivals (for arrival at area_gh) within 7 days of the end of any scheduled arrival (determined using leaving date) to area equal to area_gh.

There can be any number of scheduled arrivals and any number of unscheduled arrivals for each person. The [unique stay id] is distinct for each visit so person 1 can have 3 stays, all with a different [unique stay id].

Hope someone can suggest a way to do this?

Thanks

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 10:18:55

Please post some data and explain what you want. Its quite difficult to make out from explanation what you're really after!

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

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-04 : 10:31:09

quote:
Originally posted by visakh16

Please post some data and explain what you want. Its quite difficult to make out from explanation what you're really after!

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


[Person ID]    [Arrival Date]    [Leaving Date]    Area     [unique stay id]
===========    ==============    ==============    =====    ================
1              01/03/2013        03/03/2013        YU       100/1
1              05/03/2013        06/03/2013        AREA_GH  100/2
1              10/03/2013        15/03/2013        VG       100/3

Row 2 data is an unscheduled visit (due to arrival at area_gh). The arrival date of row 2 is within 7 days of the leaving date of row 1 therefore I want to return row 2 as an unscheduled visit that is within 7 days of the end of any other scheduled visit - scheduled visit being to any area other than area_gh. As I said there could be more than one unscheduled visit to area_gh and more than one scheduled visit that ended within 7 days of the start of an unscheduled visit to area_gh.

All the scheduled and unscheduled visits are in one table.

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-10-04 : 10:33:31

Actually if I select distinct in my query it seems to give me what I want, from waht I can see so far. I've not had time to go right through data set yet but there may be caveats along the way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources