Solved

sql query for selecting conditionally

Posted on 2014-04-07
11
131 Views
Last Modified: 2014-04-17
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.
0
Comment
Question by:bigbigpig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39984534
select 'c1' computer, 'ERROR' stat, '4/7/2014 4:21:00 PM' dt into #temp

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'
0
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 166 total points
ID: 39984671
I'd write it like this:

select computer_name, timestamp
from my_table as blah
where status = 'ERROR'
and timestamp = (select max(timestamp) from my_table where computer_name = blah.computer_name)

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39984692
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:
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'
;

Open in new window

0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 1

Expert Comment

by:JoseBarroso
ID: 39984733
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 39986226
The method below avoids any second reads of the original table:


SELECT [computer name]
FROM dbo.tablename
GROUP BY [computer name]
HAVING
    MAX(CASE WHEN status = 'ERROR' THEN timestamp END) =
    MAX(timestamp)
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 39986301
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'.
I think the following should work -
select computer_name, status, timestamp from
(select computer_name, status, timestamp,
 row_number() over (partition by computer_name order by timestamp desc) dt_rn,
 rank() over (partition by computer_name order by status desc) stat_rnk
 from tbl) as x
where dt_rn > 1 and stat_rnk = 2;
0
 
LVL 10

Author Closing Comment

by:bigbigpig
ID: 40006598
Thanks everyone for the comments.  Apparently lots of ways to work this these all worked.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40006860
>> 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.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40007545
As would mine . . . I was surprised it wasn't excepted as a solution.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40007610
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.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40007965
No issues awking :-)
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question