bigbigpig
asked on
sql query for selecting conditionally
I've been staring at this for too long and I can't get my head wrapped around this.
To simplify this, let's say I have a table that holds computer name, status, timestamp. For example:
computer1 | ERROR | 4/7/2014 4:21 PM
computer1 | OK | 4/7/2014 4:22 PM
computer2 | OK | 4/7/2014 4:22 PM
computer2 | ERROR | 4/7/2014 4:23 PM
From this I want a list of computer that have a current status of ERROR. If that computer has a more recent record with a status of OK then I don't want to see it.
To simplify this, let's say I have a table that holds computer name, status, timestamp. For example:
computer1 | ERROR | 4/7/2014 4:21 PM
computer1 | OK | 4/7/2014 4:22 PM
computer2 | OK | 4/7/2014 4:22 PM
computer2 | ERROR | 4/7/2014 4:23 PM
From this I want a list of computer that have a current status of ERROR. If that computer has a more recent record with a status of OK then I don't want to see it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
no points pl.
I'd go with using row_number() as proposed by Kyle Abrahams, however it isn't necessary to use a CTE, this would do the same:
I'd go with using row_number() as proposed by Kyle Abrahams, however it isn't necessary to use a CTE, this would do the same:
SELECT
computer_name
, timestamp
FROM (
SELECT
computer_name
, timestamp
, ROW_NUMBER() OVER (PARTITION BY computer ORDER BY dt DESC) rowNm
FROM #temp
) AS x
WHERE RowNm = 1
AND stat = 'ERROR'
;
Hi,
You can do like this but it's a little bit tricky, anyway for the tests i made it works:
SELECT Computer as XComputer, Max(DateZ+Status) AS MaxDateZ
FROM TABLE_
group by Computer
HAVING substring(Max(DateZ+Status ),14,50)=' ERROR';
The idea here is to join the date and the satus to get something like this:
20140408231010OK - or -
20140408231010ERROR
this way you can use the MAX to get the last Status for that PC and then check if in that string exists the word "ERROR" with the function HAVING.
You will need to work the "Datez" and format it in someting like "yyyyMMddhhmmss", check your regional options.
Fields: Computer, Status (ERROR/OK), DateZ (date)
Hoper this help
You can do like this but it's a little bit tricky, anyway for the tests i made it works:
SELECT Computer as XComputer, Max(DateZ+Status) AS MaxDateZ
FROM TABLE_
group by Computer
HAVING substring(Max(DateZ+Status
The idea here is to join the date and the satus to get something like this:
20140408231010OK - or -
20140408231010ERROR
this way you can use the MAX to get the last Status for that PC and then check if in that string exists the word "ERROR" with the function HAVING.
You will need to work the "Datez" and format it in someting like "yyyyMMddhhmmss", check your regional options.
Fields: Computer, Status (ERROR/OK), DateZ (date)
Hoper this help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone for the comments. Apparently lots of ways to work this these all worked.
>> I don't think the proposed solutions necessarily cover other potential scenarios, such as a single computer having a status of 'ERROR' or three rows in which the chronological statuses are 'ERROR','OK','ERROR'. <<
My code would handle either/both of those conditions.
My code would handle either/both of those conditions.
As would mine . . . I was surprised it wasn't excepted as a solution.
My apologies. I misread the question to mean show only those records with a status of 'ERROR' where a previous status was 'OK' and not just where the curent status was 'ERROR'. Forgive me.
No issues awking :-)
insert into #temp
select 'c1' computer, 'OK' stat, '4/7/2014 4:22:00 PM'
insert into #temp
select 'c2' computer, 'OK' stat, '4/7/2014 4:22:00 PM'
insert into #temp
select 'c2' computer, 'ERROR' stat, '4/7/2014 4:23:00 PM'
;with cte as
(select row_number() over(partition by computer order by dt desc) rowNm, *
from #temp
)
select * from cte
where RowNm = 1 and stat = 'ERROR'