Tracking/Querying Status Changes using SQL

Question

I was given a database design which stores information about an organization and any changes that have happened or will happen to the organizations. Since pretty much anything can change about an organization, there is one table that only contains the unique OrganizationID's in a table called "Organizations". The changes to that organization all have an effective date and follow a similar design pattern such as the following, the location change:

Table: organization_locations

organization_id (int, not null) - Relates back to the Organizations.ID column.
location_id (int, not null) - Relates to Locations.ID
eff_date (datetime, not null) - The date this change becomes effective

Table: Locations

ID (int, pk, identity, not null) - ID of the location
Name (varchar(255), not null) - Name of the location
... Other miscellaneous columns that aren't important for this discussion ...

E.G. Organizations may just contain two rows which would simply hold the id's 1 and 2 respectively. Locations may have 3 locations (id, name):

1, Location1
2, Location2
3, Location3

organization_locations (organization_id, location_id, eff_date):

1, 1, 1/1/2000  <--- Organization 1 is starting at location 1
1, 2, 1/1/2010  <--- On 1/1/2010, organization 1 moves to location 2 (from location 1)
1, 3, 1/1/2011 <--- On 1/1/2011, organization 1 moves to location 3 (in this case from location 2)

I already have a large and possibly overly complex query for specifying a date and getting back an organizations status at that given time but I feel like there's probably an easier way. However, the problem at hand is the following:

From this schema, how can I answer a question such as "What organizations will move from Location 1 to another location and what organizations will move to Location 1 from another location in the given timeframe: date1 to date2?"

A similar question that could also answer the first is: How can I query each organization's location changes (easy) while showing the PREVIOUS location they are moving from (hard?) ?

Note: Including the LINQ tag in case there's an easy way to do it in LINQ I can go that route.

Answer 1

To Location 1:

SELECT  DISTINCT organization_id
FROM    organization_locations ol
WHERE   ol.eff_date BETWEEN @date1 AND @date2
        AND ol.location = 1

From Location 1:

SELECT  DISTINCT organization_id
FROM    (
        SELECT organization_id,
               (
               SELECT  TOP 1 location_id
               FROM    organization_locations oln
               WHERE   oln.organization_id = ol.organization_id
                       AND oln.eff_date < ol.eff_date
               ORDER BY
                       organization_id DESC, eff_date DESC
               ) AS previous_location
        FROM   organization_locations ol
        WHERE  eff_date BETWEEN @date1 AND @date2
        ) olo
WHERE   previous_location = 1

Show previous location:

SELECT ol.*,
       (
       SELECT  TOP 1 location_id
       FROM    organization_locations oln
       WHERE   oln.organization_id = ol.organization_id
               AND oln.eff_date < ol.eff_date
               AND location_id = 1
       ORDER BY
               organization_id DESC, eff_date DESC
       ) AS previous_location
FROM   organization_locations ol

Having a UNIQUE INDEX on (organization_id, eff_date) will help you a lot.