sql query for selecting conditionally

Posted on 2014-04-07
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.
Question by:bigbigpig
  • 3
  • 2
  • 2
  • +4
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'
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

LVL 48

Expert Comment

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:
    , timestamp
                , 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


Expert Comment

ID: 39984733

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
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 -

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
LVL 69

Accepted Solution

ScottPletcher 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]
    MAX(CASE WHEN status = 'ERROR' THEN timestamp END) =
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 32

Assisted Solution

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;
LVL 10

Author Closing Comment

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

Expert Comment

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.
LVL 40

Expert Comment

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

Expert Comment

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.
LVL 40

Expert Comment

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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now