Selecting Historical Record Outside of Date Range

Question

I have a situation where I need to select an address that was current for a particular date and time from an address history table. Some sample records might be as follows:

Address/Client JOIN Table (Address_Client_JOIN):

-------------------------
|AddressId  |  ClientId |   
-------------------------
|5          |  8888887  | 
-------------------------
|6          |  8888887  |
-------------------------

History Table (Address_History):

-------------------------------------------------------------------------------------------
|HistoryId | AddressId | AddTypeId | StreetAddress  |  CreatedDate      |   ModifiedDate  |
-------------------------------------------------------------------------------------------
|1         |     5     |     1     | 123 Home Street|  2013-03-11 21:08 | 2013-04-02 13:18|
-------------------------------------------------------------------------------------------
|2         |     5     |     2     | 456 My Avenue  |  2013-03-11 21:08 | 2013-04-08 15:00|
-------------------------------------------------------------------------------------------
|3         |     6     |     1     | 789 Cat Road   |  2013-05-17 12:00 | 2013-05-17 12:00|
-------------------------------------------------------------------------------------------

The requirements for this query are that I have to grab the earliest record where @dateOfService falls between the CreatedDate and the ModifiedDate and where the AddTypeId is "1", if there is one, otherwise any other AddTypeId. The query I've thus far created is:

SELECT TOP 1 ah.HistoryId, ah.AddTypeId, ah.AddressId, ah.StreetAddress,
ah.CreatedDate, ah.ModifiedDate 
FROM Address_Client_JOIN acj WITH (NOLOCK)
INNER JOIN Address_History ah WITH (NOLOCK) ON ah.AddressId = acj.AddressId
WHERE apj.ClientId = @clientId 
    AND (ah.CreatedDate <= @dateOfService 
        AND (@dateOfService <= ah.ModifiedDate ))
ORDER BY 
    ah.HistoryId ASC, CASE WHEN ah.AddTypeId = 1 THEN 0 ELSE 1 END

This works fine as long as the @dateOfService falls between the CreatedDate and ModifiedDate. However, when I've got a @dateOfService that occurs after the ModifiedDate, I get nothing, obviously. I need to be able to account for a situation where (using the above data) @dateOfService is after the ModifiedDate of 5/17/2013. For example, where @dateOfService = '2013-08-01 12:30'.

Thanks in advance.

score 0 · Answer 1 · answered Aug 16 '13 at 22:43

You are only selecting the top row. That means that you can move the where filter into the order by clause. Then, it becomes a priority rather than a filter.

So, if nothing matches the filter, you will still be able to get a row. I think the query you want is something like:

SELECT TOP 1 ah.HistoryId, ah.AddTypeId, ah.AddressId, ah.StreetAddress,
       ah.CreatedDate, ah.ModifiedDate 
FROM Address_Client_JOIN acj WITH (NOLOCK) INNER JOIN
     Address_History ah WITH (NOLOCK)
     ON ah.AddressId = acj.AddressId
WHERE apj.ClientId = @clientId
ORDER BY ah.HistoryId ASC,
         (CASE WHEN ah.AddTypeId = 1 THEN 0 ELSE 1 END),
         (case when ah.CreatedDate <= @dateOfService AND @dateOfService <= ah.ModifiedDate then 1
               when @dateOfService > ah.ModifiedDate then 2
               else 3
          end)

Selecting Historical Record Outside of Date Range

1 Answers1