SQL Server - Find previous record based upon ID and Computer Name

Question

I have a two tables with alarming information from multiple different computers. I would like to return a list of specific alarms for a specific computer, and also include the previous alarm for that same computer.

Table 1: Computer Info

  ComputerInfoId| ComputerName| [Desc]
        1       |  Desktop1   | Front Desk
        2       |  Laptop1    | Work Station 1

Table 2: AlarmData

AlarmDataId|ComputerInfoId|AlarmId|AlarmDate
     1          2             1     03Mar2014 01:12:29
     2          2             3     03Mar2014 01:12:30
     3          1             7     03Mar2014 01:12:33
     4          1             2     03Mar2014 01:12:36
     5          1             6     03Mar2014 01:14:29
     6          2            12     03Mar2014 01:15:30
     7          1             2     03Mar2014 01:16:12
     8          2             1     03Mar2014 01:19:40

I would like a query for ComputerInfoID 1 and AlarmId 2. A query would return those records as well as the 2 records for that station that happened previous.

Expected Data:

ComputerInfoId|ComputerName|AlarmDataId|AlarmId|     AlarmDate    
      1       |  Desktop1  |    3      |  7    |03Mar2014 01:12:33 --Prev Record
      1       |  Desktop1  |    4      |  2    |03Mar2014 01:12:36 --Record fits Criteria
      1       |  Desktop1  |    5      |  6    |03Mar2014 01:14:29 --Prev Record
      1       |  Desktop1  |    7      |  2    |03Mar2014 01:16:12 --Record fist Criteria

The comments after each line are not required in the output data, they are included here for clarity.

The query that I have been working with is:

 SELECT * 
 FROM AlarmData AD LEFT OUTER JOIN 
        (SELECT *
         FROM   AlarmData AD2 INNER JOIN 
                 (SELECT  MAX(AlarmDatId)[MaxADID]
                  FROM    AlarmData AD3
                  WHERE   AD3.AlarmDataId < AD.AlarmDataId 
                         AND AD3.ComputerInfoID = AD.ComputerInfoID) AMax
           ON AD2.AlarmDataId = AMax.MaxADID) PrevAl
             ON PrevAl.ComputerInfoID = AD.ComputerInfoID 
 WHERE AD.ComputerInfoID = 1 AND AD.AlarmId = 2

But I am getting an error that the AD.AlarmDataId and AD.ComputerInfoID could not be bound on the "AD3.AlarmDataId < AD.AlarmDataId AND AD3.ComputerInfoID = AD.ComputerInfoID" line.

Answer 1

Your sample output data doesn't match the input data,and I can't tell if you want two previous errors for each matching error, or one previous error for each of 2 errors in the sample data. I think that this query will work on a larger dataset where the previous alarm for a machine might not be the previous AlarmDataID.

I have indicated with a comment where to change the number of surrounding rows you select.

with Errors  as (
select *, ROW_NUMBER() over( order by alarmdate) as OrdCnt
from alarmdata AD
  WHERE AD.ComputerInfoID = 1
)
select c.ComputerName, f.*
from ComputerInfo c 
    left outer join errors e
        on c.computerInfoId = e.ComputerInfoID 
    left outer join errors f
        on f.OrdCnt between (e.OrdCnt - 1 )and (e.OrdCnt + 0) -- Number of rows before and after
where (e.AlarmID = 2)

Answer 2

I think this work for you :

Select ComputerInfo.ComputerInfoId ,ComputerInfo.ComputerName
      ,AlarmData.AlarmDataId ,AlarmData.AlarmId ,AlarmData.AlarmDate
      ,TB.AlarmId As PreAlarmId ,TB.AlarmDate As PreAlarmDate

From ComputerInfo
inner join AlarmData On AlarmData.ComputerInfoId =ComputerInfo.ComputerInfoId
Left Outer join AlarmData TB 
                          On TB.ComputerInfoId = ComputerInfo.ComputerInfoId
                         And TB.AlarmDataId < AlarmData.AlarmDataId
                         And TB.AlarmDataId in ( Select Max(a_1.AlarmDataId)  From AlarmData As a_1
                                                        Where a_1.ComputerInfoId = ComputerInfo.ComputerInfoId
                                                        And a_1.AlarmDataId < AlarmData.AlarmDataId)
where ComputerInfo.ComputerInfoId = 1
And AlarmData.AlarmId = 2

this is sql fiddle link : Sql Fiddle Link