+ Reply to Thread
Results 1 to 2 of 2

Thread: Select previous date

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    45

    Select previous date

    I have a table that tracks machine maintenance which happen at arbitrary points in time. Here's a simplified table structure:
    Code:
    Maintenance Table
    ----------------------------------------
    ID            - integer
    DateCompleted - date
    MachineName   - varchar
    And here's some sample table data:
    Code:
    ID   DateCompleted MachineName
    ----------------------------------------
    1     5/16/2011     'Machine 1'
    2     2/8/2011    'Machine 2'
    3     3/5/2011    'Machine 1'
    4     10/15/2011     'Machine 3'
    5     8/30/2011    'Machine 1'
    6     2/9/2011     'Machine 2'
    7     12/2/2011    'Machine 3'
    What I'm trying to do is create a query that will return the date from the previous maintenance task for each task. So the result set would be like this:
    Code:
    ID   MachineName  CurDate     PrevDate
    ----------------------------------------
    1    'Machine 1'    5/16/2011     NULL
    2    'Machine 2'    2/8/2011   NULL
    3    'Machine 1'    3/5/2011   1/6/2011
    4    'Machine 3'    2/2/2011    NULL
    5    'Machine 1'    2/26/2011   1/14/2011
    6    'Machine 2'    3/9/2011    1/13/2011
    7    'Machine 3'    4/2/2011   2/2/2011
    What would be the best way to write such a query? My only idea so far would be something like this:
    Code:
    SELECT ID, MachineName, DateCompleted AS CurDate,
      (
        SELECT TOP 1 DateCompleted FROM Maintenance m2
        WHERE m1.MachineName = m2.MachineName
          AND m1.DateCompleted > m2.DateCompleted
        ORDER BY DateCompleted DESC
      ) AS PrevDate
    
    FROM Maintenance m1
    
    ORDER BY ID

  2. #2
    It depends on your dbms. MAX() will work across platforms. Index DateCompleted and MachineName; they're both used in the WHERE clause.
    Code:
    select m1.id, m1.machinename, m1.datecompleted as curdate,
      ( select max(datecompleted)
        from maintenance
        where machinename = m1.machinename
          and datecompleted < m1.datecompleted ) as prevdate
    from maintenance m1
    order by machinename, curdate
    If your data base management system supports windowing functions, you can use
    Code:
    select m1.id, m1.machinename, m1.datecompleted as curdate, 
           max(datecompleted) over (partition by machinename 
                                    order by m1.datecompleted 
                                    rows between unbounded preceding 
                                             and 1 preceding) as prevdate
    from maintenance m1

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

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