CODEWITHSUNDEEP
sqlsql-serversql-server-2012
sky_limit
sky_limit

Reputation: 133

Cross apply to get a specific record based on DateTime

Here is the sql fiddle for the following scenario: http://sqlfiddle.com/#!6/434fc/1

Base table:

╔═══════════════════════╗
║ Id   DateTime         ║
╠═══════════════════════╣
║ 201  2015-05-03 08:01 ║
║ 301  2015-05-03 08:20 ║
║ 401  2015-05-03 08:40 ║
╚═══════════════════════╝

Extract Table as E:

╔═══════════════════════════════════════╗
║     Id   DateTime            Location ║
╠═══════════════════════════════════════╣
║     201  2015-05-03 07:50    City A   ║
║     201  2015-05-03 08:01    City B   ║
║     201  2015-05-03 08:50    City C   ║
║     301  2015-05-03 07:15    City E   ║
║     301  2015-05-03 08:01    City F   ║
║     301  2015-05-03 08:20    City G   ║
║     401  2015-05-03 07:15    City W   ║
║     401  2015-05-03 08:30    City X   ║
║     401  2015-05-03 08:55    City Y   ║
║     401  2015-05-03 09:00    City Z   ║
╚═══════════════════════════════════════╝

Desired Result:

╔══════════════════════════════════════╗
║     Location  Id    DateTime         ║
╠══════════════════════════════════════╣
║     City B    201   2015-05-03 08:01 ║
║     City G    301   2015-05-03 08:20 ║
║     City X    401   2015-05-03 08:30 ║
╚══════════════════════════════════════╝

Using the following code, I get the right result for ID 201 and 301, but for 401 I need the first largest date in Extract table which is less than the date in Base table since there is no matching date in Extract table as in Base table.

select ca.Location, B.id, ca.Datetime
from Base B
cross apply(select top 1 Location, DateTime
       from Extract E 
       where B.id = E.id 
       order by case when B.DateTime = E.DateTime then 1 else 0 end desc,
                     E.Datetime)ca

Please help in editing this code to get the desired result. Thank you so much.

Upvotes: 2

Views: 63

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

You are almost there. I adjusted your SQL Fiddle

select ca.Location, B.id, ca.Datetime
from 
    Base B
    cross apply
    (
        select top (1) E.Location, E.DateTime
        from Extract E 
        where 
            B.id = E.id
            AND B.DateTime >= E.DateTime
        order by E.Datetime DESC
    ) AS CA

Please note, that if for a certain ID Extract table doesn't have any dates less than or equal to Base date, then this ID would not be returned at all. If you do want to see every row from Base table no matter what is in Extract table, use OUTER APPLY instead of CROSS APPLY. You'll get NULLs for Location and DateTime, but all IDs would be there.

Upvotes: 1

Related Questions