CODEWITHSUNDEEP
sqlsql-servert-sql
user2841861
user2841861

Reputation: 443

Select last 3 consecutive rows

I keep staring at this query that runs in a stored procedure getting more confused.

I have a table that looks like this...

AsofDate       Database_Name      Mirroring_Sate
DateTime       Database1          Synchronized 
DateTime       Database2          Suspended
DateTime       Database3          Disconnected
DateTime       Database1          Synchronized 
DateTime       Database2          Synchronized
DateTime       Database3          Disconnected
DateTime       Database1          Synchronized 
DateTime       Database2          Synchronized
DateTime       Database3          Disconnected

So I have a job that runs and records the mirroring status in this table for 3 databases every 15 minutes. I need a query that will for any one database name for 3 consecutive checks if the mirroring status is not equal to Synchronized.

So if for example Database1 at 3pm, then 3.15pm, then at 3.30pm was in a disconnected state, this is what I need to know. What I am doing at the moment is looking at the whole table and if the count for any one database in a state other than synchronized is 3 or more then I'm taking an acion which isn't quite what I want. I only want to do this for the last 3 checks and that's it.

Here's my query as it stands.

IF EXISTS (SELECT 1
            FROM dbo.U_MirroringStatus WITH (NOLOCK)
            where mirroring_state_desc <> 'SYNCHRONIZED'
            GROUP BY database_name
            HAVING COUNT(*) >= @MirroringStatusViolationCountForAlert
            )

Any help / advice would be great. Thanks in advance.

Upvotes: 0

Views: 96

Answers (2)

paparazzo
paparazzo

Reputation: 45096

select Database_Name 
  from ( select AsofDate, Database_Name, Mirroring_Sate 
                ROW_NUMBER ( ) Over (partition by Database_Name order by AsofDate desc) as row 
       ) as xx 
 where Mirroring_Sate <> 'SYNCHRONIZED'
   and row <= 3 
 group by Database_Name 
having count(*) = 3

ROW_NUMBER (Transact-SQL)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Perhaps something like this will do what you need:

IF EXISTS (SELECT 1
           FROM (SELECT TOP 3 ms.*
                 FROM dbo.U_MirroringStatus WITH (NOLOCK)
                 WHERE database_name = @DatabaseName
                 ORDER BY AsOfDate DESC
                ) ms
            WHERE mirroring_state_desc <> 'SYNCHRONIZED'
            HAVING COUNT(*) >= @MirroringStatusViolationCountForAlert
           )

In the text, you specify that you want this for a single database, which is why I added the where.

Upvotes: 1

Related Questions