CODEWITHSUNDEEP
sql-serversortingif-statementcase
nambla
nambla

Reputation: 71

SQL Server : sorting and checking

I have a table named Logs:

 ->OCCUR_TIME --date and time
 ->NAME --name of a person
 ->KIND --the kind of log (eg. 40 means `something`)
 ->VALUE --the value of the kind of log (eg. 99)

I have to create a query:

SELECT 
*
FROM        LOGS
WHERE       NAME='dude'
ORDER BY    KIND, OCCUR_TIME, VALUE;

Now this displays the logs and sorted by kind, then occur time (if occur_time is exactly the same it will then sort by value).

Notes:

What if for an example there was a problem with the log and after the VALUE 400 the next VALUE is 398?

Example:

   Occur_Time                | Name    | Kind | Value
   2012-06-26 15:14:25.407     dude      40     398
   2012-06-27 16:55:28.730     dude      40     399
   2012-06-30 02:43:26.763     dude      40     400
   2012-06-30 05:26:32.673     dude      40     398 <-- data prob. (possible rollback)
   2012-06-30 16:35:28.330     dude      40     399 <-- problem continuing
   2012-06-20 20:29:51.207     dude      41     100 <-- no prob. bcoz its another kind
   2012-06-23 05:50:59.130     guy       40     500 <-- no prob. bcoz its another name

I want a query that will find the problem, and where it started. Like this?

Please help. Thank you.

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

In SQL Server 2012, you would do this using the lag() function.

I'll assume you are using an earlier version, but post 2005. You can do this with nested windows functions.

select name, KIND, OCCUR_TIME, VALUE,
       (case when max(testval) over (partition by kind, name) <> min(testval) over (partition by kind)
              then 'FAIL'
              else 'OKAY'
        end) as status
from (SELECT *,
             row_number() over (partition by kind, name order by occur_time, value) as seqnum,
             (value - row_number() over (partition by kind, name order by occur_time, value)) as testval
      FROM LOGS
      WHERE NAME='dude'
     ) t
  ORDER BY name, KIND, OCCUR_TIME, VALUE

The idea is to use row_number() to generate a sequence of numbers. The difference between the value and the sequence should be a constant. The outer query looks at the min and max values of these. If they are different, you have a problem.

Upvotes: 0

SWeko
SWeko

Reputation: 30912

Not an ideal query, but this will list the problems:

select l1.*, l2.* 
from logs l1 -- self join on the columns that are the same
  inner join logs l2 on l1.Name = l2.Name and l1.Kind = l2.kind
where l1.occur_time > l2.occur_time
  and l1.value < l2.value

Basically this will find all mismatches, where the time grows, but the value diminishes. It will return a lot of duplicates for every occurence, though, so you might have to restrict with something like this:

select distinct l1.Name, l1.Kind
from logs l1 -- self join on the columns that are the same
  inner join logs l2 on l1.Name = l2.Name and l1.Kind = l2.kind
where l1.occur_time > l2.occur_time
  and l1.value < l2.value

to isolate the problematic Name/Kind pairs

Upvotes: 1

Related Questions