SQL Server - Find Previous Record Based on Computer ID and Name
I have two tables with alarming information from several different computers. I would like to return a list of specific alarms for a specific computer and also enable a previous alarm for the same computer.
Table 1: Computer Information
ComputerInfoId| ComputerName| [Desc]
1 | Desktop1 | Front Desk
2 | Laptop1 | Work Station 1
Table 2: AlarmData p>
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 need a query for ComputerInfoID 1 and AlarmId 2. The query will return these records, as well as 2 records for this station that happened earlier.
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
Comments after each line are not required in the output, they are included here for clarity.
The query I worked 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 AD.AlarmDataId and AD.ComputerInfoID cannot be related by the line "AD3.AlarmDataId <AD.AlarmDataId AND AD3.ComputerInfoID = AD.ComputerInfoID".
source to share
The selected output does not match the input, and I cannot determine if you want two previous errors for each matching error, or one prior error for each of the two errors in the sampled data. I think this query will work on a large dataset where the previous alarm for the machine might not be the previous AlarmDataID.
I have indicated with a comment where you can change the number of surrounding lines you have selected.
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)
source to share
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 the sql fiddle link : Sql Fiddle link
source to share
Your result is unclear (AlarmDataId = 5 for ComputerInfoId = 2 NOT 1)
But I wrote something that you need.
SELECT a.ComputerInfoID,
(SELECT ComputerName
FROM ComputerInfo c
WHERE c.ComputeInfoID = a.ComputerInfoID),
a.AlarmDataId, a.AlarmId, a.AlarmDate
FROM AlarmData a
WHERE a.ComputerInfoID = 1
AND ( a.AlarmId = 2
OR EXISTS ( SELECT 'a'
FROM AlarmData a2
WHERE a.ComputerInfoID = a2.ComputerInfoID
AND a2.AlarmId = 2
AND a2.AlarmDate > a.AlarmDate
AND NOT EXISTS ( SELECT 'b'
FROM AlarmData a3
WHERE a.ComputerInfoID = a3.ComputerInfoID
AND a3.AlarmDate < a2.AlarmDate
AND a3.AlarmDate > a.AlarmDate
)
)
)
source to share
I also tried it. I created a SQLFiddle for it .
Notes. You can set the depth it returns to to show errors, so instead of only showing the last error, you can also show the last two errors by simply increasing the depth to 2 here:
cte.Depth + 1 <= 1 -- Depth you want to go to
and here
alarms.Depth <= 1 -- Should equal the depth in the CTE query
When you run this query, it produces the following set of results:
ComputerInfoId ComputerName AlarmDataId AlarmId AlarmDate
1 Desktop1 3 7 2014-03-03 01:12:33.000
1 Desktop1 4 2 2014-03-03 01:12:36.000
1 Desktop1 6 12 2014-03-03 01:15:30.000
1 Desktop1 7 2 2014-03-03 01:16:12.000
source to share
you can use this query for expected output
WITH cte
AS (
SELECT AD.ComputerInfoId
,ComputerName
,AlarmDataId
,AlarmId
,AlarmDate
FROM AlarmData AD
INNER JOIN ComputerInfo C ON AD.ComputerInfoId = C.ComputerInfoId
WHERE AD.ComputerInfoId = 1
AND AlarmId <> 2
)
SELECT ComputerInfoId
,ComputerName
,AlarmDataId
,AlarmId
,AlarmDate
FROM cte
WHERE AlarmDataId < (
SELECT Max(AlarmDataId)
FROM AlarmData
WHERE ComputerInfoId = 1
AND AlarmId = 2
)
UNION ALL
SELECT AD.ComputerInfoId
,ComputerName
,AlarmDataId
,AlarmId
,AlarmDate
FROM AlarmData AD
INNER JOIN ComputerInfo C ON AD.ComputerInfoId = C.ComputerInfoId
WHERE AD.ComputerInfoId = 1
AND AlarmId = 2
ORDER BY AlarmDataId
source to share