Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Question Unanswered: Get set of records closest to date with SQL

    I have a history table with a series of equipment item events, including the equipment location (ModuleID) and the date of the change event. A new record is added (from the Delete table) each time a record is changed. For example...

    [hr]
    Code:
    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
    [hr]

    What I need to find is:
    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?

    Any and all advice appreciated!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Something like this?
    Code:
    DECLARE @point_in_time datetime
        SET @point_in_time = '20080917'
    
    SELECT *
    FROM   your_table
     INNER
      JOIN (
            SELECT equipment_id
                 , Max(change_date) As change_date
            FROM   your_table
            WHERE  change_date <= @point_in_time
            GROUP
                BY equipment_id
           ) As applicable_records
        ON applicable_records.equipment_id = your_table.equipment_id
       AND applicable_records.change_date = your_table.change_date
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    thought I'd play around with CROSS APPLY, based on other chats

    Code:
    SELECT outerT.*
    FROM   your_table AS outerT
    CROSS APPLY 
        (
            SELECT equipment_id
                 , MAX(innerT.change_date) AS change_date
            FROM   your_table AS innerT
            WHERE  innerT.change_date   <=  @point_in_time
               AND innerT.equipment_id  =   outerT.equipment_id
            GROUP
                BY equipment_id
            HAVING MAX(innerT.change_date) =   outerT.change_date
           ) As applicable_records
    
    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
    Second is very expensive.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2009
    Posts
    7
    Perfect. Thanks!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Liking the HAVING part ++1 Poots!
    George
    Home | Blog

Posting Permissions

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