Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: Find Nearest Date Record without Cross Apply !!

    Table :

    ChangeID ChangeDate EquipmentID ModuleID EquipStatus
    1 12/9/08 230 1789 Normal
    2 13/9/08 450 1245 Normal
    3 17/9/08 230 1789 Open
    4 21/9/08 230 1899 Open
    5 21/9/08 450 1674 Normal
    6 22/9/08 450 2364 Normal

    Given a date, what module was each equipment item in on that date?

    How do I get the date of the nearest previous event from a list like this?

    For the above scenario I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.

    SELECT outerT.*
    FROM your_table AS outerT
    CROSS APPLY
    (
    SELECT TOP 1
    equipment_id
    , change_date
    FROM your_table AS innerT
    WHERE innerT.change_date <= @point_in_time
    AND innerT.equipment_id = outerT.equipment_id
    ORDER BY change_date DESC
    ) AS applicable_records
    WHERE applicable_records.change_date = outerT.change_date


    The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.

    Kindly help, it's needed urgently !!

    Thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.ChangeID 
         , t.ChangeDate 
         , t.EquipmentID 
         , t.ModuleID 
         , t.EquipStatus 
      FROM ( SELECT EquipmentID
                  , MAX(ChangeDate) AS latest
               FROM daTable
              WHERE ChangeDate <= @point_in_time
             GROUP
                 BY EquipmentID ) AS m
    INNER
      JOIN daTable AS t
        ON t.EquipmentID = m.EquipmentID
       AND t.ChangeDate = m.latest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •